BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

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()))

VARCHAR Conversion

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