Demystifying the SQL Server DATETIME Datatype

I have a Birthday Column in My Table. How Can I Figure Out When There If Someone’s Birthday is This Year?

Easy one! But every now and then such a question pops up. So, here’s one approach to solve this:

SET NOCOUNT ON

CREATE TABLE birthdays ( myName VARCHAR(20) , myBirthday DATETIME )

GO

INSERT INTO birthdays VALUES(‘Frank’, ’19680723′)

INSERT INTO birthdays VALUES(‘Tanja’, ’19700706′)

INSERT INTO birthdays VALUES(‘Julian’, ’19990829′)

INSERT INTO birthdays VALUES(‘Florian’, ’20030701′)

INSERT INTO birthdays VALUES(‘Nina’, ’20050123′)

SET NOCOUNT OFF

DECLARE @year INT SET @year = YEAR(GETDATE()) SELECT myName , DATEADD(yyyy,@year-YEAR(myBirthday),myBirthday) AS myBirthday FROM birthdays ORDER BY myBirthday

DROP TABLE birthdays

myName myBirthday

——————– ——————————————————

Nina 2005-01-23 00:00:00.00

Florian 2005-07-01 00:00:00.000

Tanja 2005-07-06 00:00:00.000

Frank 2005-07-23 00:00:00.000

Julian 2005-08-29 00:00:00.000

(5 row(s) affected)

How does this work? The important stuff happens within the @year-YEAR(myBirthday) part. There we calculate the offset in years that needs to be added to the birthdate in order to get this years’ birthday for each person. As you also can see through the use of a variable, you have a pretty generic approach here.

I Have Three Separate Values for Day, Month and Year. How Can I turn This Into a Date?

Surely you *can* do something like this:


DECLARE @day INT, @month INT, @year INT SELECT @day = 28, @month = 2, @year = 2005 SELECT CAST( REPLACE(STR(@year,4),’ ‘,’0′) + REPLACE(STR(@month,2),’ ‘,’0′) + REPLACE(STR(@day,2),’ ‘,’0′) AS DATETIME)

——————————————————

2005-02-28 00:00:00.000

(1 row(s) affected)

Or possibly even worse, a tour de brute force with CASTings over CASTings while trying to determine whether the given day or month has one digit or two. I’m not going to show you such an example. Look at some of the questions in online communities on this topic and you’ll see what I mean.

Well, while the above solution is already a quite nice one, I know most developers are also lazy coders who tend to avoid as many keystrokes as possible. So, this one’s for you:

SELECT CAST( CAST( (@year * 10000 + @month * 100 + @day) AS CHAR(8)) AS DATETIME)

——————————————————

2005-02-28 00:00:00.000

(1 row(s) affected)

I should note one thing to keep in mind here. Both solutions do not check if you have a valid date before trying to CAST to a DATETIME. If you want to catch this, you would need to write something like:


DECLARE @day INT, @month INT, @year INT SELECT @day = 29, @month = 2, @year = 2005

SELECT CASE WHEN ISDATE(CAST((@year * 10000 + @month * 100 +@day) AS CHAR(8)))=0

THEN ‘No valid date’ ELSE ‘Valid date’

END ,

CASE WHEN ISDATE(CAST((@year * 10000 + @month * 100 +@day) AS CHAR(8)))=1

THEN CAST(CAST((@year * 10000 + @month * 100 +@day) AS CHAR(8)) AS DATETIME)

ELSE 0

END

————- ——————————————————

No valid date 1900-01-01 00:00:00.000

(1 row(s) affected)

How Many Days Does the Month of a Given Date Have?

This simple question is actually trickier than it seems:

DECLARE @dt DATETIME SET @dt = ’20050325′

SELECT DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt)))) AS DaysInGivenMonth

DaysInGivenMonth

—————-

31

(1 row(s) affected)

Or in a slightly shorter version:

SELECT DAY(DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@dt),@dt))-1) AS DaysInGivenMonth

DaysInGivenMonth

—————-

31

(1 row(s) affected)

Well, how does this work? The “magic” is done here:

SELECT DATEADD(DAY,1-DAY(@dt),@dt)

——————————————————

2005-03-01 00:00:00.000

(1 row(s) affected)

This will always adjust the first day of the given month. The rest is straight forward. Add one month, then subtract 1 day and return that value via DAY().

On a side note. You can also use the last statement to adjust to all days less or equal to 28. That is all days that happen to be for sure in all months. If you, for example, want to adjust to the 15th of a month, you would do:

SELECT DATEADD(DAY,15-DAY(@dt),@dt)

——————————————————

2005-03-15 00:00:00.000

(1 row(s) affected)

Another method to retrieve the days of a month is:

DECLARE @dt DATETIME SET @dt = ’20050326′

SELECT @dt ,

CASE WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+31) = 1

THEN 31 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+30) = 1

THEN 30 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+29) = 1

THEN 29 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+28) = 1

THEN 28 END AS DaysInMonth

DaysInMonth

—————————————————— ———–

2005-03-26 00:00:00.000

31

(1 row(s) affected)

For better readability, you might want to consider rewriting this to:

SELECT @dt , CASE WHEN ISDATE(the_date/100*100+31) = 1 THEN 31 WHEN ISDATE(the_date/100*100+30) = 1 THEN 30 WHEN ISDATE(the_date/100*100+29) = 1 THEN 29 WHEN ISDATE(the_date/100*100+28) = 1 THEN 28 END AS DaysInMonth FROM (SELECT CONVERT(VARCHAR,@dt,112) AS the_date ) t1

but I won’t do it here, because I think the first version is a better aid in visualizing what is going on here.

Now, why does the above work? Firstly, the CONVERT to style 112 is to ensure that the result can be interpreted as a number. You then might think a value divided by 100 and again multiplied by 100 is the original value again. So why not go ahead and just do the addition? Not quite. Most of the time you would be right, but we’re doing some integer arithmetic here and so dividing by 100 and multiplying back by 100 does not result in the original value. Want to prove this?

DECLARE @dt DATETIME SET @dt = ’20050326′

SELECT CONVERT(VARCHAR,@dt,112) AS Input ,

CONVERT(VARCHAR,@dt,112)/100*100 AS Output

Input Output

—————————— ———–

20050326 20050300 (1 row(s) affected)

Now, we can add the possible days for months and test if it yields a valid date. If so, we do have our days of the given month. Btw, don’t think you will get a “more” efficient algorithm when you reorder the WHEN expressions from most frequently occurring to least frequently. I mean, don’t put the test for days = 28 before the test for days = 29! This will yield wrong results.

DECLARE @dt DATETIME

SET @dt = ’20040203′

SELECT @dt ,

CASE WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+31) = 1

THEN 31 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+30) = 1

THEN 30 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+29) = 1

THEN 29 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+28) = 1

THEN 28 END AS DaysInMonthCorrect ,

CASE WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+31) = 1

THEN 31 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+30) = 1

THEN 30 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+28) = 1

THEN 28 WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+29) = 1

THEN 29 END AS DaysInMonthWrong

DaysInMonthCorrect DaysInMonthWrong

—————————————————— —————— —————-

2004-02-03 00:00:00.000 29 28

(1 row(s) affected)

Finally here, a pretty cool script for lazycoders comes from SQL Server MVP Steve Kass

DECLARE @dt DATETIME SET @dt = ’20050326′ — or the date in question SELECT 32-DAY(@dt-DAY(@dt)+32) ———– 31 (1 row(s) affected)

Did I mention it already, that I really, really love these cool (and fast to type) scripts?

Continues…

Pages: 1 2 3 4 5 6 7




Related Articles :

  • No Related Articles Found

One Response to “Demystifying the SQL Server DATETIME Datatype”

  1. With the exception of dated material. This is one of the best articles on Dates.
    If anything I learned the differences with the older versions of MS SQL and T-SQL. Kudo’s to all who left this article on the website. This is in my “tricks” folder.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |