Answers by Sentry

How to return only the date from a SQL Server DateTime datatype

Richard C.

The Problem

SQL Server returns a date with time information included.

SELECT GETDATE();

-- 2023-08-09T11:33:38.513Z

SELECT * from Person;

-- Id     Name    CreatedAt
-- --   ----    ---------
-- 1      Amir    2023-08-09T11:37:03.32Z

So how do you return or display just the date part of the datetime? For example, 2023-08-09.

The Solution

The simplest solution is to use CAST. This code works in SQL Server 2008 and later versions.

SELECT CAST(GETDATE() AS DATE);

-- 2023-08-09

You can further format this date in your application’s code to look however you prefer.

Specifying a Custom Date Format

If you want to specify a custom date format in SQL itself, such as “month-day-year” used in the United States of America, you can use the FORMAT function.

SELECT FORMAT (GETDATE(), 'MM-dd-yy');

-- 08-09-23

SQL Server 2008 and Earlier

If you are using an old version of SQL Server then the date type isn’t available since only datetime is supported. You have to use the following code to remove the time portion from the date:

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()));

-- 2023-08-09T00:00:00Z

This uses days (dd) to create a new date with zero hours, in other words, midnight. To strip the time portion away completely, use the following CONVERT function:

SELECT CONVERT(VARCHAR, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())), 112);

-- 20230809

Unlike CAST, CONVERT allows you to specify a date format. Here we use 112.

You can’t use a custom format with CONVERT, but there are many formats to choose from. Pick one from the first two columns in the table below.

Without century (yy)With century (yyyy)StandardInput/output
-0 or 100Default for datetime and smalldatetimemon dd yyyy hh:miAM (or PM)
1101U.S.1 = mm/dd/yy, 101 = mm/dd/yyyy
2102ANSI2 = yy.mm.dd, 102 = yyyy.mm.dd
3103British/French3 = dd/mm/yy, 103 = dd/mm/yyyy
4104German4 = dd.mm.yy, 104 = dd.mm.yyyy
5105Italian5 = dd-mm-yy, 105 = dd-mm-yyyy
6106-6 = dd mon yy, 106 = dd mon yyyy
7107-7 = Mon dd, yy, 107 = Mon dd, yyyy
8 or 24108-hh:mi:ss
-9 or 109Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)
10110USA10 = mm-dd-yy, 110 = mm-dd-yyyy
11111JAPAN11 = yy/mm/dd, 111 = yyyy/mm/dd
12112ISO12 = yymmdd, 112 = yyyymmdd
-13 or 113Europe default + millisecondsdd mon yyyy hh:mi:ss:mmm (24-hour)
14114-hh:mi:ss:mmm (24-hour)
-20 or 120ODBC canonicalyyyy-mm-dd hh:mi:ss (24-hour)
-21 or 25 or 121ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffsetyyyy-mm-dd hh:mi:ss.mmm (24-hour)
22-U.S.mm/dd/yy hh:mi:ss AM (or PM)
-23ISO8601yyyy-mm-dd
126ISO8601yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127ISO8601 with time zone Zyyyy-MM-ddThh:mm:ss.fffZ (no spaces)
-130Hijridd mon yyyy hh:mi:ss:mmmAM
-131Hijridd/mm/yyyy hh:mi:ss:mmmAM

Loved by over 4 million developers and more than 90,000 organizations worldwide, Sentry provides code-level observability to many of the world’s best-known companies like Disney, Peloton, Cloudflare, Eventbrite, Slack, Supercell, and Rockstar Games. Each month we process billions of exceptions from the most popular products on the internet.

learn more about sentryjoin our discord
Share on Twitter
Bookmark this page
Ask a questionJoin the discussion

Related Answers

A better experience for your users. An easier life for your developers.

Try Sentry For FreeRequest a Demo
    TwitterGitHubDribbbleLinkedinDiscord
© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.