SQL Server – Date Time 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] |
SELECTCONVERT(VARCHAR(20),GETDATE(),100) |
|
Month DD YYYY HH:MM:SS:Milli[AM/PM] |
SELECTCONVERT(VARCHAR(26),GETDATE(),109) |
|
DD Month YYYY HH:MM:SS:Milli (24h) |
SELECTCONVERT(VARCHAR(24),GETDATE(),113) |
|
DD Month YYYY HH:MM:SS:Milli(24h) |
SELECTCONVERT(VARCHAR(24),GETDATE(),113) |
|
YYYY-MM-DD HH:MM:SS (24h) |
SELECTCONVERT(VARCHAR(19),GETDATE(),120) |
|
YYYY-MM-DD HH:MI:SS.Milli(24h) |
SELECTCONVERT(VARCHAR(23),GETDATE(),121) |
