year get

Question

Post reply

year get

sqlcentral2@mailinator.com

SSCommitted

Points: 1514
More actions
February 21, 2012 at 9:05 am

#253337

select Name,Block_Name,BlockType, HOURS , Month,cast((DP.Hours*PH.pp)as decimal(4,0))as tt1,PH.PP,
case Month when 'january' then 1
when 'february' then 2
when 'march' then 3
when 'april' then 4
when 'may' then 5
when 'june' then 6
when 'july' then 7
when 'august' then 8
when 'september' then 9
when 'october' then 10
when 'november' then 11
when 'december' then 12 end as mnth
from ##temp1 DP, ##cmd_holiday PH where
DP.MOnth = PH.MOnths
this query is giving me months compare i wanna compare year also over here ..can u temme how i can do this ...am not so good wid date functions
4x4 photos

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 1

It's not clear what you are trying to do. Have you looked at the datepart functions?

Lowell SSC Guru Points: 323518 More actions · Answer 2

from your query so far, i couldn't infer any datetime columns exist that you could pull the year out of.

there is a YEAR function that will get the year of a datetime column that can help.

assuming the column "CreatedDate" exists(your column name is ??), you would simply add to your WHERE clause:

AND YEAR(DP.CreatedDate) = YEAR(PH.CreatedDate)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sqlcentral2@mailinator.com SSCommitted Points: 1514 More actions · Answer 3

with this query am getting months only for a single year - 2012 if user selects from

march to dec

i would get

march - 3

april-4

........

till december - 12

what happens if user selects from

nov -2011 to march -2012

am getting bad data here

nov - 2011

nov - 2012

dec -2011

dec - 2012

the correct representation should be

nov - 2011

dec -- 2011

nov - 2012

dec -2012

now ?? canu help me how ?

4x4 photos

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

try this:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

should help to get the most relevant answer in minutes.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 5

Eugene Elutin (2/21/2012)
try this:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
should help to get the most relevant answer in minutes.

link from above updated: the link had an extra

character in it after the forum rendered it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 6

Corrected. Thanks!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Martin Schoombee SSCoach Points: 19128 More actions · Answer 7

sqlcentral2 (2/21/2012)
with this query am getting months only for a single year - 2012 if user selects from
march to dec
i would get
march - 3
april-4
........
till december - 12
what happens if user selects from
nov -2011 to march -2012
am getting bad data here
nov - 2011
nov - 2012
dec -2011
dec - 2012
the correct representation should be
nov - 2011
dec -- 2011
nov - 2012
dec -2012
now ?? canu help me how ?

I am only guessing based on the lack on information, but you may be ordering by the month only and not the year first (then month). Or you have month first in your "order by".

+100 on what the others said though...you have to give us more info to ensure that we aren't guessing when trying yo help you.