SQL Server 2019 Date Format

We are using frequently the default data types in relation database is DateTime function, DBA’s and SQL developer have challenges to pull the data based on different dateTime filter, but we don’t know how to play with Datetime function and how to use the existing available inbuilt functions instead off writing the unnecessary script and its impact the performance as well.

In this article I would like to explain on that how we can get the date and time values and how we can change the date format.

Please go through links and get to know about these topics from Microsoft official website..

Before going to DateTime function we can understand the about CAST and CONVERT function, here we can see the high-level difference between these functions

Difference between CAST AND COVERT.

• CAST function is ANSI standard and its compatible to use in other database, CONVERT Function is specific function of SQL SERVER.
• CAST function is used to convert a data type without a specific format, the CONVERT function does convert and formatting data types at the same time.
• CAST function will truncate the data when we are doing the casting from a float or numeric to an integer.

Date Formats : (DD/MM/YY)

SELECT CONVERT(Varchar(10),GETDATE(),1) AS [MM/DD/YY]

Date Formats : (YY.MM.DD)

SELECT CONVERT(Varchar(10),GETDATE(),2) AS [YY.MM.DD]

Date Formats : (DD/MM/YY)

SELECT CONVERT(Varchar(10),GETDATE(),3) AS [DD/MM/YY]

Date Formats : (DD.MM.YY)

SELECT CONVERT(Varchar(10),GETDATE(),4) AS [DD.MM.YY]

Date Formats : (DD-MM-YY)

SELECT CONVERT(Varchar(10),GETDATE(),5) AS [DD-MM-YY]

Date Formats : (DD-mon-YY)

SELECT CONVERT(Varchar(10),GETDATE(),6) AS [DD-mon-YY]

Date Formats : (mon Day, YY)

SELECT CONVERT(Varchar(10),GETDATE(),7) AS [mon Day, YY]

Date Formats : ( hh:mi:ss )

SELECT CONVERT(Varchar(10),GETDATE(),8) AS [ hh:mi:ss  ] 
--OR
SELECT CONVERT(Varchar(10),GETDATE(),24) AS [ hh:mi:ss ] 
--OR
SELECT CONVERT(Varchar(10),GETDATE(),108) AS [ hh:mi:ss ] 

Date Formats : (mon DD YYYY hh:mi:ss )

SELECT CONVERT(Varchar(10),GETDATE(),9) AS [ mon DD YYYY hh:mi:ss] 
or
SELECT CONVERT(Varchar(10),GETDATE(),109) AS [ mon DD YYYY hh:mi:ss] 

Date Formats : ( MM-DD-YY )

SELECT CONVERT(Varchar(10),GETDATE(),10) AS [MM-DD-YY ] 

Date Formats : ( YY/MM/DD )

SELECT CONVERT(Varchar(10),GETDATE(),11) AS [ YY/MM/DD ] 

Date Formats : ( YYMMDD )

SELECT CONVERT(Varchar(10),GETDATE(),12) AS [ YYMMDD ] 

Date Formats : ( DD mon YYYY hh:mm:ss )

SELECT CONVERT(Varchar(10),GETDATE(),13) AS [ YYMMDD ]
--or
SELECT CONVERT(Varchar(10),GETDATE(),113) AS [ YYMMDD ]

Date Formats : ( hh:mm:ss:mmm )

SELECT CONVERT(Varchar(10),GETDATE(),14) AS [ hh:mm:ss:mmm ]
-- OR
SELECT CONVERT(Varchar(10),GETDATE(),114) AS [ hh:mm:ss:mmm ]

Date Formats : ( Mon DD YYYY HH:MM AM (or PM) )

SELECT CONVERT(Varchar(10),GETDATE(),100) AS [ Mon DD YYYY HH:MM AM (or PM)  ]

Date Formats : ( MM/ DD/YYYY )

SELECT CONVERT(Varchar(10),GETDATE(),101) AS [ MM/DD/YYYY ]

Date Formats : ( YYYY.MM.DD )

SELECT CONVERT(Varchar(10),GETDATE(),102) AS [ YYYY.MM.DD ]

Date Formats : ( MM/DD/YYYY )

SELECT CONVERT(Varchar(10),GETDATE(),103) AS [ MM/DD/YYYY ]

Date Formats : ( DD.MM.YYYY )

SELECT CONVERT(Varchar(10),GETDATE(),104) AS [ DD.MM.YYYY ]

Date Formats : ( DD-MM-YYYY )

SELECT CONVERT(Varchar(10),GETDATE(),105) AS [ DD-MM-YYYY ]

Date Formats : ( DD mon YYYY )

SELECT CONVERT(Varchar(10),GETDATE(),106) AS [ DD mon YYYY ]

Date Formats : ( mon DD, YYYY )

SELECT CONVERT(Varchar(10),GETDATE(),107) AS [ mon DD, YYYY  ]

Date Formats : ( MM-DD-YYYY )

SELECT CONVERT(Varchar(10),GETDATE(),110) AS [ MM-DD-YYYY ]

Date Formats : ( YYYY/MM/DD )

SELECT CONVERT(Varchar(10),GETDATE(),111) AS [ YYYY/MM/DD ]

Date Formats : ( YYYYMMDD )

SELECT CONVERT(Varchar(10),GETDATE(),112) AS [ YYYYMMDD ]

NOTE: The default values (0 or 100, 9 or 109, 13 or 113, 20 or 120, 23, and 21 or 25 or 121) always return the century (yyyy).

Date Formats:

SELECT CONVERT(Varchar(20),GETDATE(),20) AS [YYYY-MM-DD hh:mm:ss]   
SELECT CONVERT(Varchar(20),GETDATE(),21) AS [YYYY-MM-DD hh:mm:ss.] 
SELECT CONVERT(Varchar(20),GETDATE(),22) AS [MM/DD/YY hh:mm:ss AM or PM]  
SELECT CONVERT(Varchar(20),GETDATE(),23) AS [YYYY/MM/DD] 
SELECT CONVERT(Varchar(20),GETDATE(),25) AS [YYYY-MM-DD hh:mm:ss.]

Thank You!!