SQL – Date Formats – GETDATE() examples

The following table shows some of the most common date
formats available using the SQL function GETDATE().

The commands listed below can be used to store the date
value in a variable to be used on the scope of a script / stored procedure.

The following example with print the current date as
DD/MM/YYYY:

DECLARE @DATE AS VARCHAR(10)
SET @DATE = CONVERT(VARCHAR(10),GETDATE(),103)

PRINT @DATE

This example shows how to display yesterday’s date as
DD/MM/YYYY:

DECLARE @DATE AS VARCHAR(10)
SET @DATE = CONVERT(VARCHAR(10),GETDATE() 1, 103)

PRINT
@DATE

DATE ONLY

FORMAT

SQL COMMAND

MM/DD/YY

SELECT CONVERT(VARCHAR(8),GETDATE(),1)

MM/DD/YYYY

SELECT CONVERT(VARCHAR(10),GETDATE()
,
101)

YY.MM.DD

SELECT CONVERT(VARCHAR(8),GETDATE(),2)

YYYY.MM.DD

SELECT CONVERT(VARCHAR(10),GETDATE()
,
102)

DD/MM/YY

SELECT CONVERT(VARCHAR(8),GETDATE(),3)

DD/MM/YYYY

SELECT CONVERT(VARCHAR(10),GETDATE()
,
103)

DD.MM.YY

SELECT CONVERT(VARCHAR(8),GETDATE(),4)

DD.MM.YYYY

SELECT CONVERT(VARCHAR(10),GETDATE()
,
104)

DD-MM-YY

SELECT CONVERT(VARCHAR(8),GETDATE(),5)

DD-MM-YYYY

SELECT CONVERT(VARCHAR(10),GETDATE()
,
105)

DD Month YY

SELECT CONVERT(VARCHAR(9),GETDATE(),6)

DD Month YYYY

SELECT CONVERT(VARCHAR(11),GETDATE()
,
106)

Month DD, YY

SELECT CONVERT(VARCHAR(10),GETDATE(),7)

Month DD, YYYY

SELECT CONVERT(VARCHAR(12),GETDATE()
,
107)

MM-DD-YY

SELECT CONVERT(VARCHAR(8),GETDATE(),10)

MM-DD-YYYY

SELECT CONVERT(VARCHAR(10),GETDATE()
,
110)

YY/MM/DD

SELECT CONVERT(VARCHAR(10),GETDATE()
,110)

YYYY/MM/DD

SELECT CONVERT(VARCHAR(10),GETDATE(),
111)

YYMMDD

SELECT CONVERT(VARCHAR(6),GETDATE(),12)

YYYYMMDD

SELECT CONVERT(VARCHAR(8),GETDATE(),112)

TIME ONLY

FORMAT

SQL COMMAND

HH:MM:SS

SELECT CONVERT(VARCHAR(8),GETDATE(),108)

HH:MM:SS:Milli(24H)

SELECT CONVERT(VARCHAR(12),GETDATE()
,
114)

DATE AND TIME

FORMAT

SQL COMMAND

Month DD YYYY HH:MM[AM/PM]

SELECT CONVERT(VARCHAR(20),GETDATE(),100)

Month DD YYYY HH:MM:SS:Milli[AM/PM]

SELECT CONVERT(VARCHAR(26),GETDATE(),109)

DD Month YYYY HH:MM:SS:Milli (24h)

SELECT CONVERT(VARCHAR(24),GETDATE(),113)

DD Month YYYY HH:MM:SS:Milli(24h)

SELECT CONVERT(VARCHAR(24),GETDATE(),113)

YYYY-MM-DD HH:MM:SS (24h)

SELECT CONVERT(VARCHAR(19),GETDATE(),120)

YYYY-MM-DD HH:MI:SS.Milli(24h)

SELECT CONVERT(VARCHAR(23),GETDATE(),121)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s