Date and Time Conversion in SQL Server (MSSQL)

SQL Server Date and Time Conversion

To get different SQL Server date and time formats, use the following process. It will help in date and time conversion in MSSQL.

  1. Use the date format option along with CONVERT function
  2. To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23)
  3. To get MM/DD/YYYY use SELECT CONVERT(varchar, getdate(), 1)
  4. Check out the chart to get a list of all format options

A comprehensive list of formats supported by MSSQL/SQL server are provided below along with example output.  The date used for all of these examples is “2006-12-30 00:38:54.840”.

Date Formats

Format #QuerySample
1select convert(varchar, getdate(), 1)12/31/19
2select convert(varchar, getdate(), 2)19.12.31
3select convert(varchar, getdate(), 3)31-12-2019
4select convert(varchar, getdate(), 4)31.12.19
5select convert(varchar, getdate(), 5)31-12-2019
6select convert(varchar, getdate(), 6)31-Dec-19
7select convert(varchar, getdate(), 7)Dec 31, 19
10select convert(varchar, getdate(), 10)12-31-19

Time Formats

Format #QuerySample
8select convert(varchar, getdate(), 8)00:16:32
14select convert(varchar, getdate(), 14)00:16:32:610
24select convert(varchar, getdate(), 24)00:16:32
108select convert(varchar, getdate(), 108)00:16:32
114select convert(varchar, getdate(), 114)00:16:32:610

Date & Time Formats

Format #QuerySample
0select convert(varchar, getdate(), 0)Dec 12 2019 4:32PM
9select convert(varchar, getdate(), 9)Dec 31 2019 04:32:610PM
13select convert(varchar, getdate(), 13)31 Dec 2019 00:16:32:610PM
20select convert(varchar, getdate(), 20)31-12-2019 00:16

SQL server date and time conversion

Above conversion formats can be applied to majority of the database applications. Because, these are the most commonly used database date and time formats, for instance. Moreover, if you are looking for other date and time formats, feel free to comment below.

But, if you are looking for the most commonly used date and time formats, use the above conversion techniques and get the date/time in your desired format. SQL server offers various date time conversion techniques, but, above provided are a list of most common date and time conversions using SQL server.

Why Date/Time conversion is required in SQL server

As data captured across various of sources is uncleaned and contains date/time in raw format. As a result, SQL server offered various conversion techniques to get the date/time in the desired format based on your project requirements. Data extracted from multiple sources requires lot of cleanup because each tool has its own capturing/storage formats and requires re-formatting.
To achieve it, you convert the requested date to a string and specify the format number corresponding to the format needed.

To test it out, I would suggest you to use getdate() function of SQL to fetch the current date and time. As the examples provided above makes use of getdate() function, you can just copy and paste it in your console to check the date/time format. As a result, you can view the date and time format in the result console of SQL server.

If you are looking for top free SQL server management studio addins, visit here.

Feel free to comment in case you need further guidance. Will be happy to help!

A list of featured posts on this website:
Top 10 Easy to Build Chatbot Platform in 2020
Top 10 World Richest Person in Year 20202020
Your first NodeJS App Hello World
Top 10 Startups in India to Watch in Year 2020

Related posts

Leave a Comment