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.

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.