Case Statement - DateDiff and DatePart

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Case Statement - DateDiff and DatePart

Case Statement - DateDiff and DatePart

Rate Topic

Display Mode

Topic Options

Author

Message

wafw1971

Posted Monday, March 04, 2013 2:14 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 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

wafw1971

Posted Monday, March 04, 2013 2:49 AM

Valued Member

Group: General Forum Members
Last Login: Tuesday, May 07, 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

Jason-299789

Posted Monday, March 04, 2013 2:52 AM

SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:30 AM
Points: 803, Visits: 2,124

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 »

Permissions