Demystifying the SQL Server DATETIME Datatype

How Can I Find the Last Day of the Month of a Given Date?

Well, following from the previous discussion, you could possibly do this:

DECLARE @dt DATETIME

SET @dt = ’20050326′

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

THEN CAST(CAST(CONVERT(VARCHAR,@dt,112)/100*100+31 AS CHAR(8)) AS DATETIME) WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+30) = 1

THEN CAST(CAST(CONVERT(VARCHAR,@dt,112)/100*100+30 AS CHAR(8)) AS DATETIME) WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+29) = 1

THEN CAST(CAST(CONVERT(VARCHAR,@dt,112)/100*100+29 AS CHAR(8)) AS DATETIME) WHEN ISDATE(CONVERT(VARCHAR,@dt,112)/100*100+28) = 1

THEN CAST(CAST(CONVERT(VARCHAR,@dt,112)/100*100+28 AS CHAR(8)) AS DATETIME)

END

AS LastDayOfMonth LastDayOfMonth

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

2005-03-31 00:00:00.000

(1 row(s) affected)

But honestly, this is certainly far from being elegant. This one’s better:

DECLARE @dt DATETIME

SET @dt = ’20050325′

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

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

2005-03-31 00:00:00.000

(1 row(s) affected)

Or, in it’s shorter version:

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

LastDayOfMonth

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

2005-03-31 00:00:00.000

(1 row(s) affected)

The miracle again, is the adjustment to the first day in the month of the date given via the second DATEADD() function. Then we add 1 month and subtract 1 day. Done!

Last one here:

declare @31st datetime

set @31st = ’19341031′ — any 31st

declare @now datetime set @now = ’20050325′

select dateadd(month,datediff(month,@31st,@now),@31st)

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

2005-03-31 00:00:00.000

(1 row(s) affected)

Again, a pretty cool script for lazycoders by SQL Server MVP Steve Kass. Most interesting about his solution is, that it will work with any 31st (this is mandatory!!!), no matter if past, present or future. And honestly, it’s much faster to type.

How to Determine a Person’s Age?

Definitely more than one way to skin that cat! Here are only some variations in no particular order:

DECLARE @d DATETIME

SET @d = ’19680723′

SELECT DATEDIFF(yyyy,@d,GETDATE()) – (CASE WHEN DATEADD(yyyy,DATEDIFF(yyyy,@d,GETDATE()),@d) > GETDATE() THEN 1 ELSE 0 END)

SELECT DATEDIFF(yy, @d, GETDATE()) – CASE WHEN (MONTH(GETDATE()) * 100 + DAY(GETDATE())) < (MONTH(@d)* 100 + DAY(@d))

THEN 1 ELSE 0 END SELECT (CAST(CONVERT(CHAR(8),GETDATE(), 112) AS INT) – CAST(CONVERT(CHAR(8), @d, 112) AS INT))/10000

SELECT YEAR(GETDATE()) – YEAR(@d) – (CASE WHEN (MONTH(@d) > MONTH(GETDATE())) OR (MONTH(@d) = MONTH(GETDATE()) AND DAY(@d) > DAY(GETDATE()))

THEN 1 ELSE 0

END)

SELECT (0+CONVERT(CHAR(8),GETDATE(), 112) – CONVERT(CHAR(8), @d, 112))/10000

Result is always the same. I won’t go into explanation how each and every single method works as this would blow up this article too much. Try this for yourself, and if there are still questions remaining, feel free to contact me via the forum.

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 |