Date tricks and tips

At this time I'd like to share some techniques to deal with dates. Many of us have been dealing with dates , and for example this question I have seen in the newsgroup and was asked by many developers "How do I get rid of time?"

Well, first thing is I am going to use CONVERT system function to do that

1)SELECT CAST(CONVERT(VARCHAR(20),GETDATE(),112)AS DATETIME)
2)SELECT CAST(FLOOR(CAST(GETDATE() AS REAL)) AS DATETIME)

It's OK, we got it. In the above statement we need to convert the date twice in the first one and more than twice in the second one to get the result.


The following technique I learned from Steve Kass

SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0)

Pretty simple and work faster, believe me.

Also,by using this technique , you can a last /first day of current/next... month

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)-1

Also, as I have seen we have been rarely using these functions, but they do exist.It also works on SQL Server 2005 (sp2)

SELECT {fn extract(minute FROM getdate())}
SELECT {fn dayname( GetDate()) }
SELECT {fn CURRENT_DATE()}
SELECT {fn CURRENT_time()}