Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Case Statement - DateDiff and DatePart Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 2:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:30 AM
Points: 62, Visits: 155
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
Post #1426132
Posted Monday, March 4, 2013 2:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 7, 2013 3:30 AM
Points: 62, Visits: 155
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
Post #1426142
Posted Monday, March 4, 2013 2:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:24 AM
Points: 884, Visits: 2,414
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
Post #1426144
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse