Data Operations / SQL Server

Various ways to use the SQL CONVERT date function

Avatar photo
Post author:Written By

Various Ways to Use the SQL CONVERT Date Function

In this article, we will explore using the different SQL CONVERT date formats within SQL Server.

Date interpretation varies between different countries. Suppose you have a global SQL Server database with a table that holds a specific date format. For example, it has a date column that has the value 01/05/2020.

How do you interpret it? Let’s look at the following interpretations across different countries.

  • USA: Jan 5, 2020 (Standard format – mm/dd/yyyy)
  • Europe: 1 May 2020 (Standard format – dd/mm/yyyy)

In addition, other countries follow different date formats:

  • Turkey: dd.mm.yyyy
  • India: dd-mm-yyyy
  • Bulgaria: yyyy-m-d
  • Hungary: yyyy.mm.dd.

You can refer to Wikipedia for more information about date formats by country.

Apart from this, sometimes we also want to include the timestamp along with dates. A few examples of this are:

  • 01/05/2020 10:00 AM
  • 0/05/2020 14:00
  • 0/05/2020 02:00 PM
  • 01/05/2020 02:00:55 AM

It’s not possible to store dates in a SQL Server table in different formats, so we need a way to convert date formats. Let’s explore the different SQL CONVERT date format methods.

SQL CONVERT date function

Typically, database professionals use the SQL CONVERT date function to get dates into a specified and consistent format. This applies the style codes for specific output dates.

Syntax of CONVERT() function:

CONVERT(datatype, datetime [,style])

In the below SQL query, we convert the datetime into two formats using the CONVERT() function.

  • mm/dd/yy format: style code 1
  • mm/dd/yyyy format: style code 101
DECLARE @Inputdate datetime = '2019-12-31 14:43:35.863';
Select CONVERT(varchar,@Inputdate,1) as [mm/dd/yy],
CONVERT(varchar,@Inputdate,101) as [mm/dd/yyyy]

SQL convert date code

Similarly, we can specify different style codes so you can convert dates into your required format.

SELECT '0' AS [StyleCode],
'Default format' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 0) AS [OutputFormat]
UNION ALL
SELECT '1' AS [StyleCode],
'USA - mm/dd/yy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 1) AS [OutputFormat]
UNION ALL
SELECT '2' AS [StyleCode],
'ANSI - dd.mm.yy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 2) AS [OutputFormat]
UNION ALL
SELECT '3' AS [StyleCode],
'British and French - dd/mm/yy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 3) AS [OutputFormat]
UNION ALL
SELECT '4' AS [StyleCode],
'German - dd.mm.yy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 4) AS [OutputFormat]
UNION ALL
SELECT '5' AS [StyleCode],
'Italian - dd-mm-yy ' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 5) AS [OutputFormat]
UNION ALL
SELECT '6' AS [StyleCode],
'Shortened month name -dd mon yy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 6) AS [OutputFormat]
UNION ALL
SELECT '7' AS [StyleCode],
'Shortened month name - mon dd, yy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 7) AS [OutputFormat]
UNION ALL
SELECT '8' AS [StyleCode],
'24 hour time -hh:mm:ss' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 8) AS [OutputFormat]
UNION ALL
SELECT '9' AS [StyleCode],
'Default + milliseconds - mon dd yyyy hh:mm:ss:mmmAM (or PM)' AS
[Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 9) AS [OutputFormat]
UNION ALL
SELECT '10' AS [StyleCode],
'USA - mm-dd-yy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 10) AS [OutputFormat]
UNION ALL
SELECT '11' AS [StyleCode],
'Japan -yy/mm/dd' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 11) AS [OutputFormat]
UNION ALL
SELECT '12' AS [StyleCode],
'ISO format -yymmdd' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 12) AS [OutputFormat]
UNION ALL
SELECT '13' AS [StyleCode],
'Europe default + milliseconds -dd mon yyyy hh:mm:ss:mmm' AS
[Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 13) AS [OutputFormat]
UNION ALL
SELECT '14' AS [StyleCode],
' 24 hour time with milliseconds -hh:mm:ss:mmm(24h)' AS
[Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 14) AS [OutputFormat]
UNION ALL
SELECT '20' AS [StyleCode],
'ODBC canonical -yyyy-mm-dd hh:mm:ss(24h)' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 20) AS [OutputFormat]
UNION ALL
SELECT '21' AS [StyleCode],
'ODBC canonical (with milliseconds) -yyyy-mm-dd hh:mm:ss.mmm' AS
[Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 21) AS [OutputFormat]
UNION ALL
SELECT '22' AS [StyleCode],
'mm/dd/yy hh:mm:ss AM (or PM)' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 22) AS [OutputFormat]
UNION ALL
SELECT '23' AS [StyleCode],
'ISO 8601 - yyyy-mm-dd' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 23) AS [OutputFormat]
UNION ALL
SELECT '100' AS [StyleCode],
'mon dd yyyy hh:mmAM' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 100) AS [OutputFormat]
UNION ALL
SELECT '101' AS [StyleCode],
'USA -mm/dd/yyyy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 101) AS [OutputFormat]
UNION ALL
SELECT '102' AS [StyleCode],
'ANSI -yyyy.mm.dd' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 102) AS [OutputFormat]
UNION ALL
SELECT '103' AS [StyleCode],
'British/French -dd/mm/yyyy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 103) AS [OutputFormat]
UNION ALL
SELECT '104' AS [StyleCode],
'German - dd.mm.yyyy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 104) AS [OutputFormat]
UNION ALL
SELECT '105' AS [StyleCode],
'Italian -dd-mm-yyyy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 105) AS [OutputFormat]
UNION ALL
SELECT '106' AS [StyleCode],
'Shortened month name -dd mon yyyy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 106) AS [OutputFormat]
UNION ALL
SELECT '107' AS [StyleCode],
'Shortened month name -mon dd, yyyy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 107) AS [OutputFormat]
UNION ALL
SELECT '108' AS [StyleCode],
'24 hour time -hh:mm:ss' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 108) AS [OutputFormat]
UNION ALL
SELECT '109' AS
[StyleCode],
'Default + milliseconds -mon dd yyyy hh:mm:ss:mmmAM (or PM) ' AS
[Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 109) AS [OutputFormat]
UNION ALL
SELECT '110' AS [StyleCode],
'USA -mm-dd-yyyy' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 110) AS [OutputFormat]
UNION ALL
SELECT '111' AS [StyleCode],
'JAPAN -yyyy/mm/dd' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 111) AS [OutputFormat]
UNION ALL
SELECT '112' AS [StyleCode],
'ISO -yyyymmdd' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 112) AS [OutputFormat]
UNION ALL
SELECT '113' AS [StyleCode],
'Europe default + milliseconds -dd mon yyyy hh:mm:ss:mmm' AS
[Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 113) AS [OutputFormat]
UNION ALL
SELECT '114' AS [StyleCode],
' 24 hour time with milliseconds -hh:mm:ss:mmm(24h)' AS
[Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 114) AS [OutputFormat]
UNION ALL
SELECT '120' AS [StyleCode],
'ODBC canonical- yyyy-mm-dd hh:mm:ss(24h)' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 120) AS [OutputFormat]
UNION ALL
SELECT '121' AS [StyleCode],
'ODBC canonical (with milliseconds) -yyyy-mm-dd hh:mm:ss.mmm' AS
[Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 121) AS [OutputFormat]
UNION ALL
SELECT '126' AS [StyleCode],
'ISO8601 -yyyy-mm-ddThh:mm:ss.mmm' AS [Standard and Format],
CONVERT(VARCHAR(50), Getdate(), 126) AS [OutputFormat]
UNION ALL
SELECT '127' AS [StyleCode],
'ISO8601 with time zone Z-yyyy-mm-ddThh:mm:ss.mmmZ' AS
[Standard and Format],
CONVERT(VARCHAR(100), Getdate(), 127) AS [OutputFormat]
UNION ALL
SELECT '131' AS [StyleCode],
'Arabic Hijri date - Islamic calendar' AS [Standard and Format],
CONVERT(VARCHAR(100), Getdate(), 131) AS [OutputFormat]

In the below screenshot, you can see the style code, their standards, formats and output dates.

SQL code format and output dates

Converting dates using the FORMAT() function

In the above CONVERT() function, we are required to specify style codes for a specific format output. Usually, we do not want to have to remember these codes; therefore, Microsoft introduced the FORMAT() function in SQL Server 2012.

The syntax is shown below.

FORMAT (value, format [, culture])

Value: It requires a value in the supported format. You can refer to Microsoft documentation for a detailed list.

Format: In the format, we can specify the format codes or pattern to covert the input date data. The below script shows the format codes, pattern and output format.

DECLARE @InputDate DATETIME = '2020-12-08 15:58:17.643'
SELECT 'd' AS [FormatCode],
'Short Date Pattern' AS 'Pattern',
Format(@InputDate, 'd') AS 'Output'
UNION ALL
SELECT 'D' AS [FormatCode],
'Long Date Pattern' AS 'Pattern',
Format(@InputDate, 'D') AS 'Output'
UNION ALL
SELECT 'f' AS [FormatCode],
'Full Date/Time pattern (Short Time)' AS 'Pattern',
Format(@InputDate, 'f') AS 'Output'
UNION ALL
SELECT 'F' AS [FormatCode],
'Full Date/Time pattern (Long Time)' AS 'Pattern',
Format(@InputDate, 'F')
UNION ALL
SELECT 'g' AS [FormatCode],
'General Date/Time pattern (Short Time)' AS 'Pattern',
Format(@InputDate, 'g')
UNION ALL
SELECT 'G' AS [FormatCode],
'General Date/Time pattern (Long Time)' AS 'Pattern',
Format(@InputDate, 'G') AS 'Output'
UNION ALL
SELECT 'm' AS [FormatCode],
'Month/Day pattern' AS 'Pattern',
Format(@InputDate, 'm') AS 'Output'
UNION ALL
SELECT 'O' AS [FormatCode],
'Round trip Date/Time pattern' AS 'Pattern',
Format(@InputDate, 'O') AS 'Output'
UNION ALL
SELECT 'R' AS [FormatCode],
'RFC1123 pattern' AS 'Pattern',
Format(@InputDate, 'R') AS 'Output'
UNION ALL
SELECT 's' AS [FormatCode],
'Sortable Date/Time pattern' AS 'Pattern',
Format(@InputDate, 's') AS 'Output'
UNION ALL
SELECT 't' AS [FormatCode],
'Short Time pattern' AS 'Pattern',
Format(@InputDate, 't') AS 'Output'
UNION ALL
SELECT 'T' AS [FormatCode],
'Long Time Pattern' AS 'Pattern',
Format(@InputDate, 'T') AS 'Output'
UNION ALL
SELECT 'u' AS [FormatCode],
'Universal sortable Date/Time pattern' AS 'Pattern',
Format(@InputDate, 'u') AS 'Output'
UNION ALL
SELECT 'U' AS [FormatCode],
'Universal Full Date/Time pattern' AS 'Pattern',
Format(@InputDate, 'U') AS 'Output'
UNION ALL
SELECT 'Y' AS [FormatCode],
'Year Month pattern' AS 'Pattern',
Format(@InputDate, 'Y') AS 'Output'

SQL format code

Culture: It is an optional argument and defines the culture. If we do not specify any culture, SQL Server uses the language of the current session.

In the below query, we will convert a date format into a specified culture. We need to specify the culture code. For example, the culture code for the U.S. is en-US and hi-IN is for India.

The scripts use the d format code for short date patterns.

DECLARE @d DATETIME ='2020-12-08 16:36:17.760';
SELECT FORMAT (@d, 'd', 'en-US') AS 'US English',
FORMAT (@d, 'd', 'no') AS 'Norwegian Result',
FORMAT(@d, 'd', 'hi-IN') AS 'India',
FORMAT(@d, 'd', 'ru-RU') AS 'Russian',
FORMAT(@d, 'd', 'gl-ES') AS 'Galician (Spain)',
FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English',
FORMAT (@d, 'd', 'zu') AS 'Zulu',
FORMAT ( @d, 'd', 'de-de' ) AS 'German',
FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC)';

You get the date format in a specified culture, as shown below.

SQL convert date format in a specified culture

If you want the date output in full date/time (long time) pattern, specify the format code F, and it quickly changes your date format.

SQL Convert date format in long time pattern

In the date format, you can also specify the custom formats and it converts the input date string as per your requirements.

To specify the custom strings, we can use the following abbreviations.

  • dd: Day of the month (01 to 31)
  • dddd: day spelling
  • MM: Month number (01 to 12)
  • MMMM: Month spelling
  • yy: Year in the two-digit
  • yyyy: Four-digit year
  • hh: It is the hour 01 to 12
  • HH: It gives 24 hrs. format hour 00 to 23
  • mm: minute 00 to 59
  • ss: second from 00 to 59
  • tt: AM or PM

In the below script, we converted the input date formats into multiple formats using the above abbreviations or codes.

DECLARE @d DATETIME ='2020-12-08 16:36:17.760';
SELECT
FORMAT (@d,'dd/MM/yyyy ') as [Date Format 1] ,
FORMAT (@d, 'dd/MM/yyyy, hh:mm:ss ') as [Date Format 2] ,
FORMAT(@d,'yyyy-MM-dd HH:mm:ss')as [Date Format 3] ,
FORMAT(@d,'Dd MMM yyyy HH:mm:ss')as [Date Format 4] ,
FORMAT(@d,'MMM d yyyy h:mm:ss')as [Date Format 5] ,
FORMAT (@d, 'dddd, MMMM, yyyy')as [Date Format 6] ,
FORMAT (@d, 'MMM dd yyyy') as [Date Format 7] ,
FORMAT (@d, 'MM.dd.yy') as [Date Format 8] ,
FORMAT (@d, 'MM-dd-yy') as [Date Format 9] ,
FORMAT (@d, 'hh:mm:ss tt')as [Date Format 10] ,
FORMAT (@d, 'd-M-yy')as [Date Format 11] ,
FORMAT(@d,'MMMM dd,yyyy')as [Date Format 12]

SQL date format.

Using AT TIME ZONE in SQL Server 2016 or later

Different countries follow different time zones. Usually, these time zones follow the offset from Coordinated Universal Time (UTC) time. A few examples of time zones are:

  • Australian Central Daylight Time: UTC +10:30
  • India Standard Time: UTC +5:30
  • Mountain Daylight Time: UTC-6
  • Singapore Time: UTC+8
  • Central Daylight Time: UTC-5

You can refer to this article for a detailed list of time zones.

Many countries follow daylight saving and the clock is adjusted 1 hour (or 30-45 minutes) depending upon the time zones. For example, Central Daylight Time followed the below schedule:

  • Standard Time began: November 1, 2020 02:00 local time. Clocks were turned back one hour.
  • Standard Time ends March 14, 2021 02:00 local time. Clocks will go forward one hour.

SQL Server is not aware of these time zones and daylight saving. Usually, an organization follows the UTC zones as it does not require any changes.

How can we convert time zones in SQL Server?

You can use AT TIME ZONE starting from SQL Server 2016 and convert the time zones. In the below query, it shows dates for the Central Standard Time, India Standard Time and Samoa Standard Time.

Declare @DateinUTC datetime2='2020-11-01 02:00:00'
select
@DateinUTC AT TIME ZONE 'UTC' AT TIME ZONE 'Central Standard Time' as 'Central Standard Time' ,
@DateinUTC AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time' as 'India Standard Time',
@DateinUTC AT TIME ZONE 'UTC' AT TIME ZONE 'Samoa Standard Time' as 'Samoa Standard Time',
@DateinUTC AT TIME ZONE 'UTC' AT TIME ZONE 'Dateline Standard Time' as 'Dateline Standard Time'

At Time Zone code in SQL Server

To know the supported time zones, you can query sys.time_zone_info and it returns the time zone and the offset.

A query example using sys.time_zone_info

You can then filter the time zones that are currently abiding by daylight saving.

Select * from sys.time_zone_info where is_currently_dst=1

Filtering time zones by daylight savings in SQL Server using the SQL Convert date function.

Now, let’s consider the daylight saving for Eastern Time.

Monitor the health and performance of all your database platforms with a robust database monitoring tool Foglight.

Visibility into it all.

Monitor your databases and resolve performance issues quickly.
Try Foglight
  • Daylight Saving Time began – Sunday, March 8, 2020 at 2:00 AM.
  • Daylight Saving Time ended- Sunday, November 1, 2020, at 2:00 AM.

Convert your UTC zone into the Eastern Standard Time, and you can note the impact of daylight saving.

DECLARE
@PreDST datetime = '2020-03-08 06:59:00',
@PostDST datetime = '2020-03-08 07:00:00';
SELECT
@PreDST AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS [EST time before DST],
@PostDST AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS [EST time before DST];

Changing the UTC zone into the Eastern standard time in SQL Server.

Useful points for using SQL CONVERT date formats

Evaluate your application requirements and choose the appropriate data type date, SmallDateTime, DateTime, DateTime2 and DateTimeOffset.

You can convert the date format using SQL CONVERT date and FORMAT functions; however, it is advisable to use the format that most closely satisfies your workloads. This will help you avoid having to use the explicit date conversion.

You can account for daylight saving in SQL Server using the AT TIME ZONE function starting from SQL.

The zombie survival guide to database performance tuning

Don’t be a database zombie. Learn 6 expert steps for approaching modern database performance tuning.

Get the Guide
Tags: , ,

About the Author

Rajendra Gupta

Rajendra Gupta is a MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" in 2020 and 2021 at SQLShack.

Related Articles