Getting the Date Portion of a SQL Server DateTime
Provided by : Jim Ferguson, Senior Application Architect
Dates in Microsoft SQL Server can include both the date and the time.
Frequently it is desirable to extract portions of the date field and ignore
the other portions. Often this is done for display purposes. For example the
Transact SQL DatePart() function can be used to get just the day, year,
month, etc. portions of a date value. The DatePart() function returns an
What if you need to extract the entire date portion of a datetime field,
but want to ignore the time portion? This is frequently desirable if you are
testing date ranges in a “where” clause, and want to include rows where a
date value is on an ending date boundary condition:
SELECT * FROM Orders WHERE OrderDate <= '2016-08-25'
Rows dated August 25, 2016 with any time portion included would not be
included in this selection.
Simply using DatePart() to get the MM, DD, and YYYY portions of the date
would not turn the value into an expression that could be tested in the
Transact SQL provides the Cast and Convert functions to convert from one
data type to another. One way to solve the problem of extracting out the
date portion of a datetime field while leaving the expression as a date type
than can be tested, is to use the Convert function:
SELECT CONVERT(varchar(8), OrderDate, 112) AS OrderDateOnly FROM orders
This works, but returns the date as a string of 8-character values in
YYYYMMDD format. You can then turn that character string back into a bona
fide date value, by wrapping the expression with the Cast function to cast
the string back into a date:
SELECT cast(CONVERT(varchar(8), OrderDate, 112) AS datetime) AS OrderDateOnly FROM Orders
Now the output result is a true date value with the date part included,
but excluding the time portion. The example above shows how to create a
calculated column in the output. To perform the conversion for use in a
“where” clause do the same thing there:
SELECT * FROM Orders WHERE cast(CONVERT(varchar(8), OrderDate, 112) AS datetime) <= '2016-08-25'