March 4, 2013 at 2:14 am
I have been asked to come up with a case statement that gives me the date of birth, no I know there are easier ways to do it but its about writing the actual statement which I have done but I have got a bit confused.
Can you Help?
Declare @DOB Datetime
Declare @Today Datetime
Set @DOB = '01 November 1971'
Set @Today = GETDATE()
SELECT Datediff(Year,@DOB,@Today) AS Years,
Datepart(Month,@DOB) As DOB_Month,
Datepart(Day, @DOB) as DOB_Day,
DatePart(Month, @Today) As Current_Month,
Datepart(Day,@Today) AS Current_Day
CASE WHEN DatePart(Month, @Today) < Datepart(Month,@DOB) THEN Datediff(Year,@DOB,@Today) THEN -1 ELSE
DatePart(Month, @Today) = Datepart(Month,@DOB) THEN
Datepart(Day,@Today) < Datepart(Day, @DOB) THEN Datediff(Year,@DOB,@Today) THEN -1 ELSE END
March 4, 2013 at 2:49 am
Don't worry all sorted:
Declare @DOB Datetime
Declare @Today Datetime
Set @DOB = '01 November 1971'
Set @Today = '01 November 2011'
SELECT
CASE WHEN DatePart(Month, @Today) < Datepart(Month,@DOB) THEN (Datediff(Year,@DOB,@Today) - 1)
WHEN DatePart(Month, @Today) > Datepart(Month,@DOB) THEN Datediff(Year,@DOB,@Today)
WHEN DatePart(Month, @Today) = Datepart(Month,@DOB) THEN
CASE
WHEN DatePart(Day, @Today) < Datepart(Day,@DOB) THEN (Datediff(Year,@DOB,@Today) - 1 )
WHEN DatePart(Day, @Today) >= Datepart(Day,@DOB) THEN (Datediff(Year,@DOB,@Today) )
END
END AS How_Old_Am_I
March 4, 2013 at 2:52 am
At the moment you have it written like an IF Statement
CASE
WHEN conditional_a = conditional_b THEN something
ELSE conditional_a = conditional_c THEN something_else
ELSE Default_Something
END
With CASE statements you write them like this
CASE
WHEN conditional_a = conditional_b THEN something
WHEN conditional_a = conditional_c THEN something_else
ELSE Default_Something
END
Hope this helps.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply