Format Date And Time In SQL Server Using FORMAT Function

Introduction 

In this article, you will learn how to Format Dates in SQL Server. 

Here we will use the “FORMAT” function to format a date in SQL Server

Prerequisites

  • Install SQL Server
  • Install SQL Server Management Studio (SSMS)

For the above prerequisites, you can follow the article How to Install SQL Server and SQL Server Management Studio (SSMS)

Currently, I have installed SQL Server 2019 and SQL Server Management Studio 18.10 (SSMS) on my windows 11 machine.

Built-in function in SQL Server to get the DateTime value in a specific format

By using some built-in function in SQL Server we can get the DateTime value in a specific format.

For example,

GETDATE()

It returns server DateTime in “YYYY-MM-DD HH:mm:ss.fff” format.

SELECT GETDATE() AS [GETDATE()]
Result:-2022-06-09 12:28:37.787

GETUTCDATE()

It returns DateTime in GMT.

SELECT GETUTCDATE() AS [GETDATE()];
Result:-2022-06-09 07:10:54.350

SYSDATETIME()

It returns the server’s DateTime

SELECT SYSDATETIME() AS [GETDATE()];
Result:2022-06-09 12:41:46.8713228

SYSDATETIMEOFFSET()

It returns the server’s DateTime with time zone in which SQL Server instance is running.

SELECT SYSDATETIMEOFFSET() AS [GETDATE()];
Result:2022-06-09 12:42:15.7936382 +05:30

SYSUTCDATETIME()

It returns server DateTime in GMT.

SELECT SYSUTCDATETIME() AS [GETDATE()];
Result:2022-06-09 07:12:54.4664815

CURRENT_TIMESTAMP

It returns current DateTime of the server.

SELECT CURRENT_TIMESTAMP AS [GETDATE()];
Result:2022-06-09 12:43:40.650

After the CONVERT function, SQL Server added a function (FORMAT) to handle date formatting, giving us a new way to format dates in SQL Server.

To format the date and time data types from a date column (Date, DateTime, etc. Data type) in a table or a variant such as GETDATE(), use the FORMAT function.

Date Format with FORMAT Function

We have many ways to format dates as given below

DD/MM/YYYY

SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date;
Result:09/06/2022 

DD/MM/YYYY, HH:MM:SS

SELECT FORMAT (getdate(), 'dd/MM/yyyy, hh:mm:ss ') as date;
Result:09/06/2022, 04:56:44 

DDDD,MMMM,YYYY

SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy') as date;
Result:Thursday, June, 2022

MMM DD YYYY

SELECT FORMAT (getdate(), 'MMM dd yyyy') as date;
Result:Jun 09 2022

MM.DD.YY

SELECT FORMAT (getdate(), 'MM.dd.yy') as date;
Result:06.09.22

MM-DD-YY

SELECT FORMAT (getdate(), 'MM-dd-yy') as date;
Result:06-09-22

HH:MM:SS TT

SELECT FORMAT (getdate(), 'hh:mm:ss tt') as date;
Result:05:17:37 PM

MM/DD/YYYY (Standard: USA)

SELECT FORMAT (getdate(), 'd','us') as date;
Result:06/09/2022

YYYY-MM-DD HH:MM:SS TT

SELECT FORMAT (getdate(), 'yyyy-MM-dd hh:mm:ss tt') as date;
Result:2022-06-09 05:18:55 PM

YYYY.MM.DD HH:MM:SS T

SELECT FORMAT (getdate(), 'yyyy.MM.dd hh:mm:ss t') as date;
Result:2022.06.09 05:19:53 P

DDDD,MMM,YYYY in Spanish

SELECT FORMAT (getdate(), 'dddd, MMMM, yyyy','es-es') as date;
Result:jueves, junio, 2022

DDDD DD, MMMM,YYYY in Japanese 

SELECT FORMAT (getdate(), 'dddd dd, MMMM, yyyy','ja-jp') as date;
Result:木曜日 09, 6月, 2022

Date Format with Culture 

We can get regional formatting by using the culture option as shown below:

English-USA

SELECT FORMAT (getdate(), 'd', 'en-US') as date;
Result:6/10/2022

French-France

SELECT FORMAT (getdate(), 'd', 'fr-FR') as date;
Result:10/06/2022

French - Belgium

SELECT FORMAT (getdate(), 'd', 'fr-BE') as date;
Result:10-06-22

French - Canada

SELECT FORMAT (getdate(), 'd', 'fr-CA') as date;
Result:2022-06-10

Danish - Denmark

SELECT FORMAT (getdate(), 'MM.dd.yy') as date;
Result:06.10.22

Dari - Afghanistan

SELECT FORMAT (getdate(), 'd', 'prs-AF') as date;
Result:1401/3/20

Simplified Chinese

SELECT FORMAT (getdate(), 'd', 'zh-CN') as date;
Result:2022/6/10

Divehi - Maldives

SELECT FORMAT (getdate(), 'd', 'dv-MV') as date;
Result:10/06/22

Bosnian Latin

SELECT FORMAT (getdate(), 'd', 'bs-Latn-BA') as date;
Result:10. 6. 2022.

isiXhosa / Xhosa - South Africa

SELECT FORMAT (getdate(), 'd', 'xh-ZA') as date;
Result:2022-06-10

Hungarian - Hungary

SELECT FORMAT (getdate(), 'd', 'hu-HU') as date;
Result:2022. 06. 10.

Spanish - Bolivia

SELECT FORMAT (getdate(), 'd', 'es-bo') as date;
Result:10/6/2022

Here is a list of all CultureInfo codes along with country names and language.

Country Language CultureInfo Code
Afghanistan Pashto ps-AF
Dari prs-AF
Albania Albanian sq-AL
Algeria Arabic ar-DZ
Argentina Spanish es-AR
Armenia Armenian hy-AM
Australia English en-AU
Austria German de-AT
Bahrain Arabic ar-BH
Bangladesh Bengali bn-BD
Basque Basque eu-ES
Belarus Belarusian be-BY
Belgium French fr-BE
Dutch nl-BE
Belize English en-BZ
Bolivarian Republic of Venezuela Spanish es-VE
Bolivia Quechua quz-BO
Spanish es-BO
Brazil Portuguese pt-BR
Brunei Darussalam Malay ms-BN
Bulgaria Bulgarian bg-BG
Cambodia Khmer km-KH
Canada French fr-CA
English en-CA
Caribbean English en-029
Catalan Catalan ca-ES
Chile Mapudungun arn-CL
Spanish es-CL
Colombia Spanish es-CO
Costa Rica Spanish es-CR
Croatia Croatian hr-HR
Cyrillic, Azerbaijan Azeri az-Cyrl-AZ
Cyrillic, Bosnia and Herzegovina Serbian sr-Cyrl-BA
Cyrillic, Bosnia and Herzegovina Bosnian bs-Cyrl-BA
Cyrillic, Mongolia Mongolian mn-MN
Cyrillic, Montenegro Serbian sr-Cyrl-ME
Cyrillic, Serbia Serbian sr-Cyrl-RS
Cyrillic, Serbia and Montenegro (Former Serbian ) sr-Cyrl-CS
Cyrillic, Tajikistan Tajik tg-Cyrl-TJ
Cyrillic, Uzbekistan Uzbek uz-Cyrl-UZ
Czech Republic Czech cs-CZ
Denmark Danish da-DK
Dominican Republic Spanish es-DO
Ecuador Quechua quz-EC
Spanish es-EC
Egypt Arabic ar-EG
El Salvador Spanish es-SV
Estonia Estonian et-EE
Ethiopia Amharic am-ET
Faroe Islands Faroese fo-FO
Finland Finnish fi-FI
Swedish sv-FI
Sami, Northern se-FI
Sami, Skolt sms-FI
Sami, Inari smn-FI
Former Yugoslav Republic of Macedonia Macedonian mk-MK
France French fr-FR
Breton br-FR
Occitan oc-FR
Corsican co-FR
Alsatian gsw-FR
Galician Galician gl-ES
Georgia Georgian ka-GE
Germany German de-DE
Upper Sorbian hsb-DE
Lower Sorbian dsb-DE
Greece Greek el-GR
Greenland Greenlandic kl-GL
Guatemala K'iche qut-GT
Spanish es-GT
Honduras Spanish es-HN
Hungary Hungarian hu-HU
Iceland Icelandic is-IS
India Hindi hi-IN
Bengali bn-IN
Punjabi pa-IN
Gujarati gu-IN
Oriya or-IN
Tamil ta-IN
Telugu te-IN
Kannada kn-IN
Malayalam ml-IN
Assamese as-IN
Marathi mr-IN
Sanskrit sa-IN
Konkani kok-IN
English en-IN
Indonesia Indonesian id-ID
Iran Persian fa-IR
Iraq Arabic ar-IQ
Ireland Irish ga-IE
English en-IE
Islamic Republic of Pakistan Urdu ur-PK
Israel Hebrew he-IL
Italy Italian it-IT
Jamaica English en-JM
Japan Japanese ja-JP
Jordan Arabic ar-JO
Kazakhstan Kazakh kk-KZ
Kenya Kiswahili sw-KE
Korea Korean ko-KR
Kuwait Arabic ar-KW
Kyrgyzstan Kyrgyz ky-KG
Lao P.D.R. Lao lo-LA
Latin, Algeria Tamazight tzm-Latn-DZ
Latin, Azerbaijan Azeri az-Latn-AZ
Latin, Bosnia and Herzegovina Croatian hr-BA
Latin, Bosnia and Herzegovina Bosnian bs-Latn-BA
Latin, Bosnia and Herzegovina Serbian sr-Latn-BA
Latin, Canada Inuktitut iu-Latn-CA
Latin, Montenegro Serbian sr-Latn-ME
Latin, Nigeria Hausa ha-Latn-NG
Latin, Serbia Serbian sr-Latn-RS
Latin, Serbia and Montenegro (Former Serbian ) sr-Latn-CS
Latin, Uzbekistan Uzbek uz-Latn-UZ
Latvia Latvian lv-LV
Lebanon Arabic ar-LB
Libya Arabic ar-LY
Liechtenstein German de-LI
Lithuania Lithuanian lt-LT
Luxembourg Luxembourgish lb-LU
German de-LU
French fr-LU
Malaysia Malay ms-MY
English en-MY
Maldives Divehi dv-MV
Malta Maltese mt-MT
Mexico Spanish es-MX
Mohawk Mohawk moh-CA
Monaco French fr-MC
Morocco Arabic ar-MA
Nepal Nepali ne-NP
Netherlands Dutch nl-NL
Frisian fy-NL
New Zealand Maori mi-NZ
English en-NZ
Nicaragua Spanish es-NI
Nigeria Yoruba yo-NG
Igbo ig-NG
Norway Norwegian, Bokmål nb-NO
Sami, Northern se-NO
Norwegian, Nynorsk nn-NO
Sami, Lule smj-NO
Sami, Southern sma-NO
Oman Arabic ar-OM
Panama Spanish es-PA
Paraguay Spanish es-PY
Peru Quechua quz-PE
Spanish es-PE
Philippines Filipino fil-PH
Poland Polish pl-PL
Portugal Portuguese pt-PT
PRC Tibetan bo-CN
Yi ii-CN
Uyghur ug-CN
Puerto Rico Spanish es-PR
Qatar Arabic ar-QA
Republic of the Philippines English en-PH
Romania Romanian ro-RO
Russia Russian ru-RU
Tatar tt-RU
Bashkir ba-RU
Yakut sah-RU
Rwanda Kinyarwanda rw-RW
Saudi Arabia Arabic ar-SA
Senegal Wolof wo-SN
Simplified, PRC Chinese zh-CN
Simplified, Singapore Chinese zh-SG
Singapore English en-SG
Slovakia Slovak sk-SK
Slovenia Slovenian sl-SI
South Africa Setswana tn-ZA
isiXhosa xh-ZA
isiZulu zu-ZA
Afrikaans af-ZA
Sesotho sa Leboa nso-ZA
English en-ZA
Spain, International Sort Spanish es-ES
Sri Lanka Sinhala si-LK
Sweden Swedish sv-SE
Sami, Northern se-SE
Sami, Lule smj-SE
Sami, Southern sma-SE
Switzerland Romansh rm-CH
German de-CH
Italian it-CH
French fr-CH
Syllabics, Canada Inuktitut iu-Cans-CA
Syria Syriac syr-SY
Syria Arabic ar-SY
Thailand Thai th-TH
Traditional Mongolian, PRC Mongolian mn-Mong-CN
Traditional, Hong Kong S.A.R. Chinese zh-HK
Traditional, Macao S.A.R. Chinese zh-MO
Traditional, Taiwan Chinese zh-TW
Trinidad and Tobago English en-TT
Tunisia Arabic ar-TN
Turkey Turkish tr-TR
Turkmenistan Turkmen tk-TM
U.A.E. Arabic ar-AE
Ukraine Ukrainian uk-UA
United Kingdom Welsh cy-GB
Scottish Gaelic gd-GB
English en-GB
United States English en-US
Spanish es-US
Uruguay Spanish es-UY
Vietnam Vietnamese vi-VN
Yemen Arabic ar-YE
Zimbabwe English en-ZW

As you saw above, we have used a lot of options for date and time formatting, which are detailed below,

  • hh - this is the hour from 01-12
  • HH - this is the hour from 00-23
  • mm - this is the minute from 00-59
  • ss - this is the second from 00-59
  • dd - this is day of month from 01-31
  • dddd - this is the day spelled out
  • MM - this is the month number from 01-12
  • MMM - month name abbreviated
  • MMMM - this is the month spelled out
  • yy - this is the year with two digits
  • yyyy - this is the year with four digits
  • tt - this shows either AM or PM
  • d - this is day of month from 1-31 (if this is used on its own it will display the entire date)
  • us - this shows the date using the US culture which is MM/DD/YYYY

Summary

In this article, we learned how to format DateTime values using the FORMAT function. We also learned the built-in function in SQL Server to get the DateTime value in a specific format

If you have any queries/suggestions on the article, please leave your questions and thoughts in the comment section below.

Thanks for reading and I hope you like it.