Demystifying the SQL Server DATETIME Datatype

How to Determine the First and the Last Day in a Year?

DECLARE @dt DATETIME

SET @dt = GETDATE()

SELECT CAST( CAST( YEAR(@dt) AS CHAR(4))+’0101′ AS DATETIME) AS StartOfYear , CAST( CAST( YEAR(@dt) AS CHAR(4))+’1231′ AS DATETIME) AS EndOfYear

StartOfYear EndOfYear

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

2005-01-01 00:00:00.000 2005-12-31 00:00:00.000

(1 row(s) affected)

This is possibly one of the few occasions where I would prefer such string concatenation over date arithmetic. Why? Because a solution using SQL Server’s build-in datetime functions might look something like this:

DECLARE @dt DATETIME

SET @dt = ‘20050325’

SELECT DATEADD(m,1-MONTH(DATEADD(DAY,1-DAY(@dt),@dt)),DATEADD(DAY,1-DAY(@dt),@dt)) , DATEADD(yyyy,1,DATEADD(m,1-MONTH(DATEADD(DAY,1-DAY(@dt),@dt)),DATEADD(DAY,1-DAY(@dt),@dt)))-1

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

2005-01-01 00:00:00.000 2005-12-31 00:00:00.000

(1 row(s) affected)

This is not really easy to read. However, to explain it a bit, this solution is based on the trick shown above. You can use the same trick to adjust a date to the first of a month, also to adjust the date to the first day of a year. And once you have this date, just add 1 year and subtract 1 day. You now have the last day in a year.

How to Get the Month Name from an Integer Value?

DECLARE @input INT

SET @input = 3

SELECT DATENAME(MONTH,@input*28) AS CurrentMonth

CurrentMonth

——————————

March

(1 row(s) affected)

Why does this work? First, we’re taking advantage of the fact, that, with respect to our question, both 19000326 and 20050326 are equal. Both will return “March” as month name and that’s all we care about here. Next, the multiplication * 28 is used to ensure that we will always hit a day in the correct month of @input. Try it out!

That’s it. I hope, you’ve seen that the DATETIME datatype is far from being mysterious. SQL Server provides a rich arsenal of build-in functions to deal efficiently with DATETIMEs. Use it!

Pages: 1 2 3 4 5 6 7




Array

No comments yet... Be the first to leave a reply!