How to format Date and Time in SQL Server and Sybase? Examples

How to format a date in SQL Server like in the "yyyymmdd" format? Suppose you have a date and time column in Sybase or Microsoft SQL Server,  which is displaying values in "Dec  31, 2011, 12:00 AM" and you want to display it in any particular DATE format like YYYYMMDD or DDMMYYYY, how will you do that? This is also one thing you need to keep in mind when you convert a DATE, TIME, DATETIME column into CHAR or VARCHAR values.  It's easy to format dates using the convert function in Sybase or SQL Server, but it's slightly difficult to remember the cryptic formatting style codes that go with it. For example, using style code, 112 is used to format dates in the "YYYYMMDD" format e.g. "20170329".

Similarly, the following query will format the birthday column (with value 11th Feb 1980) as 19840211 as shown below:

select convert(char(8), birthday, 112) from Employee
19840211

For quick reference, these formatting codes are listed below:

STYLE   OUTPUT
0       mon dd yyyy hh:miAM (or PM)
1       mm/dd/yy
2       yy.mm.dd
3       dd/mm/yy
4       yy.mm.dd
5       dd-mm-yy
6       dd mon yy
7       mon dd, yy
8       hh:mm:ss
9       mon dd yyyy hh:mi:ss:mmmAM (or PM)
10      mm-dd-yy
11      yy/mm/dd
12      yymmdd
100     mon dd yyyy hh:miAM (or PM)
101     mm/dd/yyyy
102     yyyy.mm.dd
103     dd/mm/yyyy
104     yyyy.mm.dd
105     dd-mm-yyyy
106     dd mon yyyy
107     mon dd, yyyy
108     hh:mm:ss
109     mon dd yyyy hh:mi:ss:mmmAM (or PM)
110     mm-dd-yyyy
111     yyyy/mm/dd
112     yyyymmdd

select convert(char(10), getdate(), 23)

By the way, if you are new to Microsoft SQL Server and T-SQL then I also suggest you join these online SQL Server courses to learn SQL Server fundamentals and how to work with T-SQL. 

Example of formatting, Date in SQL Server

Here is a couple of examples of formatting DATETIME data type in SQL Server. I have used the GETDATE function to get the current date for example purpose, this method returns a DATETIME data type.

 -- 0       mon dd yyyy hh:miAM (or PM)
 PRINT 'formatting date in mon dd yyyy hh:miAM (or PM) format'
 select convert(VARCHAR(255), getdate(), 0) 
as 'Date in mon dd yyyy hh:miAM (or PM) format'

-- 1       mm/dd/yy
select convert(char(10), getdate(), 1) as 'Date in mm/dd/yy format'

-- 2       yy.mm.dd
select convert(char(10), getdate(), 2) as 'Date in yy.mm.dd format'

-- 3       dd/mm/yy
select convert(char(10), getdate(), 3) as 'Date in  dd/mm/yy format'

-- 4       yy.mm.dd
select convert(char(10), getdate(), 4) as 'Date in yy.mm.dd format'

-- 5       dd-mm-yy
select convert(char(10), getdate(), 5) as 'Date in dd-mm-yy format'

-- 6       dd mon yy
select convert(char(10), getdate(), 6) as 'Date in dd mon yy format'

-- 7       mon dd, yy
select convert(char(10), getdate(), 7) as 'Date in mon dd, yy format'

-- 8       hh:mm:ss
select convert(char(10), getdate(), 8) as 'Date in hh:mm:ss format'

-- 9       mon dd yyyy hh:mi:ss:mmmAM (or PM)
select convert(char(10), getdate(), 9) 
as 'Date in mon dd yyyy hh:mi:ss:mmmAM (or PM)'

-- 10      mm-dd-yy
select convert(char(10), getdate(), 10) as 'Date in  mm-dd-yy format'

-- 11      yy/mm/dd
select convert(char(10), getdate(), 11) as 'Date in yy/mm/dd format'

-- 12      yymmdd
select convert(char(10), getdate(), 12) as 'Date in yymmdd format'

-- 100     mon dd yyyy hh:miAM (or PM)
select convert(char(10), getdate(), 100) 
as 'Date in mon dd yyyy hh:miAM (or PM) format'

-- 101     mm/dd/yyyy
select convert(char(10), getdate(), 101) as 'Date in  mm/dd/yyyy format'

-- 102     yyyy.mm.dd
select convert(char(10), getdate(), 102) as 'Date in yyyy.mm.dd format'

-- 103     dd/mm/yyyy
select convert(char(10), getdate(), 103) 
as 'Date in dd/mm/yyyy format in SQL'

-- 104     yyyy.mm.dd
select convert(char(10), getdate(), 104) 
as 'Date in yyyy.mm.dd format in SQL Server'

-- 105     dd-mm-yyyy
select convert(char(10), getdate(), 105) as 'Date in dd-mm-yyyy format'

-- 106     dd mon yyyy
select convert(char(10), getdate(), 106) as 'Date in  dd mon yyyy format'

-- 107     mon dd, yyyy
select convert(char(10), getdate(), 107) as 'Date in mon dd, yyyy format'

-- 108     hh:mm:ss
select convert(char(10), getdate(), 108) as 'Time in hh:mm:ss format'

-- 109     mon dd yyyy hh:mi:ss:mmmAM (or PM)
select convert(char(10), getdate(), 109) 
as 'Date time in mon dd yyyy hh:mi:ss:mmmAM (or PM) in SQL Server'

-- 110     mm-dd-yyyy
select convert(char(10), getdate(), 110) 
as 'Date in mm-dd-yyyy format in SQL Server'

-- 111     yyyy/mm/dd
select convert(char(10), getdate(), 111) 
as 'Date in yyyy/mm/dd format in SQL Server'

-- 112     yyyymmdd
select convert(char(10), getdate(), 112) as 'Date in yyyymmdd format'

and here is the output of executing all these commands in SQL Server:

You can see from the output that same date value, which is today's date is formatted into a different format by using the same convert() function but by using different styles.

You can replace the GETDATE() with any column which represents date or date and time. I have just used the GETDATE for displaying output in today's date. In most of cases, you will be putting a date, time, or DateTime column there.

That's all about how to format a DATETIME in SQL Server. You can use the same technique to convert a DATETIME column to VARCHAR in SQL Server. Just remember that convert() function can be used for converting one data type to another and the same is used for formatting dates as well because formatting date and time is nothing but converting them into VARCHAR or CHAR values.

The only thing, which you need to keep in mind is the style codes. You can just print these style codes and keep a handy reference with you.



Other Microsoft SQL Server articles you may like

• Querying Microsoft SQL SERVER 2012 Training Kit for Exam 70-461 (see here) 
• Microsoft SQL SERVER 2012 T-SQL Fundamentals (check here)
• How to check for NULL values in SQL server? (tutorial)
• How to replace NULL with empty String in SQL Server? (example)
• How to increase the length of existing columns in SQL Server? (tips)
• How to compare Dates in Microsoft SQL Server? (solution) 
• How to join three tables in one SQL Query? (tutorial) 
• How to replace null with empty String in SQL Server? (solution) 
• How to find the length of String in MSSQL? (solution) 
• How to add the primary key into an existing table in SQL? (tip)
• How to add columns on the existing table in Microsoft SQL Server? (solution) 
• What is the difference between row_number(), rank(), and dense_rank() in SQL? (answer) 
• The difference between ISNULL() and COALESCE() in SQL? (answer)
• The difference between SQL queries in Oracle and Microsoft SQL Server? (answer) 
• How many characters you can store in VARCHAR(2) column? (answer)
• SQL query to find all table names in a database? (query) 
• How to convert the result of a SELECT command to CSV String in SQL? (tutorial)
• How to delete from a table using join in SQL? (tutorial) 
• 5 Things to remember while running SQL Queries on Production Server? (tips)
• What is the difference between WHERE and HAVING clause in SQL? (answer)
• How to delete rows from a table using Join? (answer)

Thanks for reading this article, if you like this tutorial then please share with your friends and colleagues. If you have comments or suggestions or feedback then please drop a note.