Retrieving the Date From a SQL Server DateTime
The SQL Server DATETIME and SMALLDATETIME data types are ideal for storing date and time information. Often you will want to extract the date part of such a value with the time part set to midnight. This article describes several ways to achieve this.
Using the GetDate Function
There are many methods for extracting the date part from a DATETIME value. This article describes three options, each demonstrated using the GetDate() function to retrieve an example date and time. In real applications, GetDate() can be replaced with a variable or literal value.
To test the sample code, simply run it within Query Analyser, SQL Server Management Studio or any other tool for executing SQL queries.
The Floor Function
The Floor function rounds a numeric value downwards to the nearest integer value. If a DATETIME value is converted to a numeric value, the date is held in the integer part and the time is represented by the fraction. This means that converting a DATETIME to a FLOAT and rounding to an integer removes the time part. The resultant value can then be converted back to a DATETIME.
SELECT convert(DATETIME, floor(convert(FLOAT, getdate())))
DateDiff and DateAdd
The DateDiff function returns the difference between two dates expressed in days, minutes, hours, etc. The measure of difference is dependent upon the date part character used. The result is always an integer value. This means that we can easily find the number of days between any previous date and our DATETIME value. If we add this number of days to the chosen date using DateAdd, we get the desired rounded date. The following example uses zero for the reference date; the earliest DATETIME known to SQL Server.
SELECT dateadd(d, 0, datediff(d, 0, getdate()))
When a DATETIME value is converted to a VARCHAR string value a format may be applied. Format '111' gives the year, month and date without a time in a format that can easily be converted back to a DATETIME. This removes the time part.
2 September 2007