Date conversion in SQL Server
In some situations, working with dates in SQL Server can require additional formatting processes to get the date to show in a certain way. Examples of these situations would include if you have specific reporting requirements for your dataset or if you need to output data to align with another system's data structure.
Lets look at an example scenario:
In your SQL Server database, your date is recorded in the 'YYYY-MM-DD HH:MM:SS' format. With that formatting, a date would appear as '2016-05-12 09:47:36'. However we need to display this information in the local calendar format of dd/mm/yyyy. So with the above example, the output needs to show the date as 12-05-2016 and drop the 09:47:36 part altogether.
Such conversions can be quickly achieved using CONVERT....
SELECT CONVERT(varchar(10), yourdatefield, 103) AS formatted_date
So running this, our 2016-05-12 09:47:36 displays as just the desired 12/05/2016.
If you are wondering what the 103 in the CONVERT statement is, this refers to the style of the output. A few of the common styles are;
- 101 is the US format (mm/dd/yyyy)
- 103 is the British/French date format (dd/mm/yyyy)
- 104 is German which uses periods (dd.mm.yyyy).
Using these style codes means we can manipulate how the date will display in the conversion results. If just a two digit year code is needed, this can be achieved by using the above codes and simply dropping the 100 portion i.e.
- 1 is the US format (mm/dd/yy)
- 3 is the British/French date format (dd/mm/yy)
- 4 is German which uses periods (dd.mm.yy)
A small piece of code which can take the headache out of date formatting.