Date and Time Conversions in SQL Server

Sometimes you need to format a date in specific format such as format a date time string YYYY-MM-DD hh:mm:ss. mmm to date MM:DD:YY.



SQL Server provides you a various options that you can use to format a date and time string into different-different format and one of the best option is SQL Server Convert Function which converts an expression of one data type to another.

Lets take a look into how to format a date and time string into different-different format using SQL Convert Function.

We will try to convert current date time string into different different format.

In following T-SQL, variable @date store current date time string returned by Getdate() function.

DECLARE @date datetime
SET @date=GETDATE()
SELECT @Date AS CurrentDate

Format Date Time string to MM/DD/YY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date,1) AS [MM/DD/YY]

Format Date Time string to YY.MM.DD

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date,2) AS [YY.MM.DD]

Format Date Time string to DD/MM/YY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date,3) AS [DD/MM/YY]

Format Date Time string to DD.MM.YY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date,4) AS [DD.MM.YY]

Format Date Time string to DD-MM-YY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date,5) AS [DD-MM-YY]

Format Date Time string to DD MMM YY

Format Date Time string to MMM DD, YY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date,7) AS [MMM DD, YY]





Format Date Time string to HH:MM:SS

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 8) AS [HH:MM:SS]

Format Date Time to MMM DD YYYY hh:mm:ss:mmm(AM|PM)

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, 
CONVERT(VARCHAR, @Date, 9) AS [MMM DD YYYY hh:mm:ss:mmm(AM|PM)]

Format Date Time string to MM-DD-YY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 10) AS [MM-DD-YY]

Format Date time string to YY/MM/DD

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 11) AS [YY/MM/DD]

Format Date time string to YYMMDD

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 12) AS [YYMMDD]





Format Date time string to DD MMM YYYY HH:MM:SS:MMM

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, 
CONVERT(VARCHAR, @Date, 13) AS [DD MMM YYYY HH:MM:SS:MMM]

Format Date time string to HH:MM:SS:MMM

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 14) AS [HH:MM:SS:MMM]

Format Date time string to YYYY-MM-DD HH:MM:SS

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 20) AS [YYYY-MM-DD HH:MM:SS]

Format Date time string to YYYY-MM-DD HH:MM:SS.MMM

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 21) AS [YYYY-MM-DD HH:MM:SS.MMM]

Format Date time string to MM/DD/YY HH:MM:SS (AM|PM)

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, 
CONVERT(VARCHAR, @Date, 22) AS [MM/DD/YY HH:MM:SS (AM|PM)]





Format Date time string to YYYY-MM-DD

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 23) AS [YYYY-MM-DD]

Format Date time string to YYYY-MM-DD

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 24) AS [HH:MM:SS]

Format Date Time string to MM-DD-YYYY HH:MM:SS.MMM

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, 
CONVERT(VARCHAR, @Date, 27) AS [MM-DD-YYYY HH:MM:SS.MMM]

Format Date Time string to MMM DD YYYY HH:SS (AM|PM)

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, 
CONVERT(VARCHAR, @Date, 100) AS [MMM DD YYYY HH:SS (AM|PM)]

Format Date Time to MM/DD/YYYY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 101) AS [MM/DD/YYYY]

Format Date Time to YYYY.MM.DD

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 102) AS [YYYY.MM.DD]





Format Date Time string to DD/MM/YYYY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 103) AS [DD/MM/YYYY]

Format Date Time string to DD.MM.YY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 104) AS [DD.MM.YY]

Format Date Time string to DD-MM-YY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 105) AS [DD-MM-YY]

Format Date Time string to DD MMM YYYY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 106) AS [DD MMM YYYY]

Format Date Time string to MMM DD,YYYY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 107) AS [MMM DD,YYYY]

Format Date Time string to HH:MM: SS

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 108) AS [HH:MM: SS]

Format Date Time string to MMM DD YYYY HH:MM:SS:MMM(AM|PM)

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, 
CONVERT(VARCHAR, @Date, 109) AS [MMM DD YYYY HH:MM:SS:MMM(AM|PM)]

Format Date Time string to MM-DD-YY

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 110) AS [MM-DD-YY]

Format Date Time string to YYYY/MM/DD

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 111) AS [YYYY/MM/DD]

Format Date Time string to YYYYMMDD

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 112) AS [YYYYMMDD]





Format Date Time string to DD MMM YYYY HH:MM:SS:MMM

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, 
CONVERT(VARCHAR, @Date, 113) AS [DD MMM YYYY HH:MM:SS:MMM]

Format Date Time string to HH:MM:SS:MMM

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 114) AS [HH:MM:SS:MMM]

 

 

 

 

 

Format Date Time string to YYY-MM-DD HH:MM:SS

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 120) AS [YYYY-MM-DD HH:MM:SS]

Format Date Time string to YYYY-MM-DD HH:MM:SS.MMM

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, CONVERT(VARCHAR, @Date, 121) AS [YYYY-MM-DD HH:MM:SS.MMM]

Format Date Time sting to YYYY-MM-DDTHH:MM:SS.MMM

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, 
CONVERT(VARCHAR, @Date, 126) AS [YYYY-MM-DDTHH:MM:SS.MMM]

Format Date Time strin to Islamic/Hijri dd mon yyyy hh:mi:ss:mmmAM|PM

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, 
CONVERT(NVARCHAR, @Date, 130) AS [dd mon yyyy hh:mi:ss:mmmAM|PM]

Note that: here mon represents a multi-token Hijri unicode representation of the full month name. This value does not render correctly on a default US installation of SSMS.

Format Date Time string to Islamic/Hijri dd/mm/yyyy hh:mi:ss:mmmAM|PM

DECLARE @Date datetime
SET @Date=GETDATE()

SELECT @Date AS ActualDate, 
CONVERT(NVARCHAR, @Date, 131) AS [dd/mm/yyyy hh:mi:ss:mmmAM|PM]

 

Also Read..

SQL Server Interview Questions And Answers

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 452 total views,  7 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.