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 Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 4,589 total views,  2 views today