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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy