SQL Server Helper - Tip of the Day

In SQL Server 2000 and SQL Server 2005, to get the date part of any given date of DATETIME data type you can do the following:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) 
       AS [DateOnly]

Using the same technique, to get the first day of the month and first day of the quarter for any given date of DATETIME data type, you can do the following:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0 ) 
       AS [FirstDayOfTheMonth]

SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()), 0 ) 
       AS [FirstDayOfTheQuarter]

In SQL Server 2008, to get the date part of a DATETIME data type you can also do the same thing as above or you simply need to cast it to a DATE data type:

SELECT CAST(GETDATE() AS DATE)
SELECT CAST(SYSDATETIME() AS DATE)