Format Date in SQL Server using Format Function

Introduction 

In this article, you will learn how to format dates in SQL Server using the format function. In this article, we will learn how to format dates using Format function in SQL Server. 

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.