In this article, we will learn how to use the Date Formats in SQL.

SQL Server has many built-in Date Formats.

GETDATE() is a date function used to return the current database system date and time.

FORMAT() is a string function used to format a value with the specified format.

 

FormatSQL Statement
MM/dd/yyyy
declare @date datetime = '2019-01-01'
SELECT FORMAT(@date, 'MM/dd/yyyy');

Output: 01/01/2019

M/dd/yyyy
declare @date datetime = '2019-01-01'
SELECT FORMAT(@date, 'M/dd/yyyy');

Output: 1/01/2019

dddd, MMMM dd, yyyy
SELECT FORMAT(getdate(), 'dddd, MMMM dd, yyyy');

Output: Tuesday, December 10, 2019

ddd, MMMM dd, yyyy
SELECT FORMAT(getdate(), 'ddd, MMMM dd, yyyy');

Output: Tue, December 10, 2019

dd MMM yyyy
declare @date datetime = '2019-01-01'
SELECT FORMAT(@date, 'dd MMM yyyy');

Output: 01 Jan 2019

d MMM yyyy
declare @date datetime = '2019-01-01'
SELECT FORMAT(@date, 'd MMM yyyy');

Output: 1 Jan 2019

dd/MM/yy
SELECT FORMAT(getdate(), 'dd/MM/yy');

Output: 10/12/19

hh:mm:ss tt
SELECT FORMAT(getdate(), 'hh:mm:ss tt');

Output: 03:06:05 PM

h:mm:ss tt
SELECT FORMAT(getdate(), 'h:mm:ss tt');

Output: 3:06:05 PM

h:m:ss tt
SELECT FORMAT(getdate(), 'h:m:ss tt');

Output: 3:6:05 PM

MM/dd/yyyy hh:mm:ss
SELECT FORMAT(getdate(), 'MM/dd/yyyy hh:mm:ss');

Output: 12/10/2019 03:08:29

 

Also, check How To Use Date Functions In SQL

Subscribe

Select Categories