Microsoft SQL Server ResourcesGetting the Date Portion of a SQL Server DateTime Field

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 integer value.

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 "where" clause.

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) <= '2017-08-25'

Strategic Overview

SQL Server Express

SQL Azure Cloud

What it Means to Information Workers

Link Microsoft Access to SQL Azure Databases

Deploy MS Access DBs linked to SQL Azure

Eliminate SQL Azure Security Holes

Monitor SQL Server Usage and DTU Limits on Azure

Convet Azure SQL Server to Elastic Pools

SQL Server Tips

Database Users and Permissions

Database Backup and Restore on Network

AM/PM Date Time Format

Get Date Portion

Set NOCOUNT ON

Running Total

Videos

Migrate Your Data Tier to SQL Server: Strategies for Survival

Microsoft Access Database and Migration Challenges

Are we there yet? Successfully navigating the bumpy road from Access to SQL Server

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Services

Microsoft SQL Server Consulting Services

Visual Studio .NET programmers for web development

General Microsoft Access consulting services

Additional Resources

Microsoft Access Help

Microsoft Access Developer Help Center

MS Access Developer Programming

More Access and SQL Tips

Technical Papers

Microsoft Access Tools

Connect with Us

 

Free Product Catalog from FMS