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




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 |