Sentry Answers>SQL Server>

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

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.

Click to Copy
SELECT GETDATE();

-- 2023-08-09T11:33:38.513Z
Click to Copy
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.

Click to Copy
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.

Click to Copy
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:

Click to Copy
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:

Click to Copy
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
  • Syntax.fmListen to the Syntax Podcast
  • Community SeriesIdentify, Trace, and Fix Endpoint Regression Issues
  • Syntax.fm logo
    Listen to the Syntax Podcast

    Tasty treats for web developers brought to you by Sentry. Get tips and tricks from Wes Bos and Scott Tolinski.

    SEE EPISODES

Considered “not bad” by 4 million developers and more than 100,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
© 2024 • Sentry is a registered Trademark
of Functional Software, Inc.