March 13, 2009 at 11:34 am
Hi,
Please can you help with the following statement.I am trying to create a column called Harvest year that uses the created date to determine if it is Harvest 2008 or Harvest 2009. If I change the “ ” I get different results but all either “harvest year 2008” or “ harvest year 2008”. I think that by specified date is returning as 26-06-1905 but don't know why.
SELECT TOP (100) CREATEDDATE, 'harvestyear'=
CASE
WHEN createddate > 2009-06-01 THEN 'Harvest Year 2009'
ELSE 'Harvest Year 2008'
END
FROM SALESLINE
Any help appriciated
Jason
March 13, 2009 at 11:38 am
It's possible it's just a typo in what you posted, but the first thing I noticed is that you don't have single-quotes around the cut-off date in the When clause. Do you have them in the copy you're running?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 13, 2009 at 11:54 am
Besides what GSquared mentioned, if you just want year why not just compare year?
CASE WHEN Year(CreatedDate) = Year(Date) Then 'blah' ELSE 'blah blah' END
March 13, 2009 at 11:58 am
You must enclose the date in single quotes to cast it correctly to a date time.
when createddate > 2009-06-01
is the same as
when createddate > '1905-06-26'
because 2009-06-01 becomes the integer 2002, and when you cast that to a datetime, it becomes 2002 days after 1900-01-01 or 1905-06-26.
March 13, 2009 at 12:06 pm
Wow, what a quick response. This is my first post here and I really appriciate you help. It wasn't a Typo just an amateur thrown in at the deep-end.
Thanks again,
March 13, 2009 at 12:10 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply