SQL Convert Date – Never Have A Random Date Format Again

sqlconvertdateOne of the most common mistakes you can make as a programmer is to overlook some details such as time and date conversion and just assume that the automatic conversion will get it right. That might be the case sometimes, but what if things don’t go as planned? Do you really want to take any chances?

There are a lot of SQL courses out there that introduce you to the basics of SQL programming and, while they do explain common functions such as Convert(), they usually fail to mention its importance and what can happen if you skip conversion, especially when handling the date and time.

Getting familiar with the Convert() function

As its name suggest, the Convert() function is used to convert data from one type to another. This operation is especially useful when you are working with multiple data formats that need to be brought to the same type before being processed any further. However, the more common usage for the Convert() function is to bring all the date and time entries from a database to the same format for easier manipulation.

The syntax of the Convert() function is fairly simple, CONVERT (data_type(length), expression, style), where data_type is the current data type of the data that will be converted (optionally including its length in the parenthesis), expression is the data that is to be converted and style is the output format. When used to convert date and time, since the type of data you will be converting will be a variable and the actual data will be gathered using the GETDATE() function, an actual example of a Convert() function syntax for date and time would look something like this: CONVERT (VARCHAR, GETDATE(), 0) – the type of function every beginner or intermediate SQL coder should be familiar with.

Style values and their usage

When it comes to styles to choose from for displaying date and time in your SQL database, you’ve got quite some options to pick from. Because some styles have the year displayed as a two-digit number only, there are also variations of those styles that use the full four-digit format for the year. The styles that display the year as a two-digit number are usually identified by a one or two-digit number (1, 2, 3…10, 11, etc.), whereas the styles that use a four-digit style for the year are usually identified with a three-digit number(101, 102, 103, etc.).

Let’s see some examples of date styles:

  • 0 or 100 – this is the default style, and will display the date in the following format: mon dd yyyy hh:miAM (or PM)

Example: CONVERT(VARCHAR, GETDATE(), 0) — Oct 28 2013 11:36PM

  • 1 and 101 – US-specific date style: mm/dd/yy / mm/dd/yyyy

Examples: CONVERT(VARCHAR, GETDATE(), 1) — 10/28/13

CONVERT(VARCHAR, GETDATE(), 101) — 10/28/2013

  • 2 and 102 – ANSI date style: yy.mm.dd / yyyy.mm.dd

Examples: CONVERT(VARCHAR, GETDATE(), 2) — 13.10.28

                 CONVERT(VARCHAR, GETDATE(), 102) — 2013.10.28

  • 3 and 103 – British/French date style: dd/mm/yy / dd/mm/yyyy

Example: CONVERT(VARCHAR, GETDATE(), 3) — 28/10/13

CONVERT(VARCHAR, GETDATE(), 103) — 28/10/2013

  • 4 and 104 –German date style: dd.mm.yy / dd.mm.yyyy

Examples: CONVERT(VARCHAR, GETDATE(), 4) — 28.10.13

CONVERT(VARCHAR, GETDATE(), 104) — 28.10.2013

  • 5 and 105 – Italian date style: dd-mm-yy / dd-mm-yyyy

Examples: CONVERT(VARCHAR, GETDATE(), 5) — 28-10-13

CONVERT(VARCHAR, GETDATE(), 105) – 28-10-2013

  • 6 and 106 – this style uses the dd mon yy / dd mon yyyy format.

Examples: CONVERT(VARCHAR, GETDATE(), 6) — 28 Nov 13

           CONVERT(VARCHAR, GETDATE(), 106) –– 28 Nov 2013

  • 7 and 107 – this style uses the Mon dd, yy / Mon dd, yyyy format.

Examples: CONVERT(VARCHAR, GETDATE(), 7) — Nov 28, 13

            CONVERT(VARCHAR, GETDATE(), 107) — Nov 28, 2013

  • 8 and 108 – this style displays the time, without a date, in the following format: hh:mi:ss

Examples: CONVERT(VARCHAR, GETDATE(), 8) — 23:36:42

  • 9 or 109 – this is the equivalent of the default style, the only difference being the added seconds and milliseconds counter. The format is mon dd yyyy hh:mi:ss:mmmAM (or PM).

Examples: CONVERT(VARCHAR, GETDATE(), 9) — Nov 28 2013 11:36:42:054PM

  • 10 and 110 – USA date style:  mm-dd-yy / mm-dd-yyyy

Examples: CONVERT(VARCHAR, GETDATE(), 10) — 10-28-13

            CONVERT(VARCHAR, GETDATE(), 110) — 10-28-2013

  • 11 and 111 – Japan date style: yy/mm/dd / yyyy/mm/dd

Examples: CONVERT(VARCHAR, GETDATE(), 11) — 31/10/28

            CONVERT(VARCHAR, GETDATE(), 111) — 2013/10/28

  • 12 and 112 – ISO date style: yymmdd / yyyymmdd

Examples: CONVERT(VARCHAR, GETDATE(), 12) — 131028

            CONVERT(VARCHAR, GETDATE(), 112) — 20131028

  • 13 or 113 – this is the European default style and uses the dd mon yyyy hh:mi:ss:mmm (24h) format.

Examples: CONVERT(VARCHAR, GETDATE(), 13) — 28 Nov 2013 23:36:42:054

  • 14 and 114 – this style displays the time, without a date, in the following format: hh:mi:ss:mmm (24h)

Examples: CONVERT(VARCHAR, GETDATE(), 14) — 23:36:42:054

  • 20 and 120 –  this style displays the date and time in the following format: yyyy-mm-dd hh:mi:ss(24h)

Examples: CONVERT(VARCHAR, GETDATE(), 20) — 2013-10-28 23:36:42

  • 21 and 121 – same as above, plus milliseconds counter.

Examples: CONVERT(VARCHAR, GETDATE(), 21) — 2013-10-28 23:36:42.054

  • 126 – same as above, without spaces.

Examples: CONVERT(VARCHAR, GETDATE(), 126) — 2013-10-28T23:36:42.054

Why use Covert() instead of automatic conversion

Besides the obvious advantage of having the possibility to format the date and time exactly the way you want it, there is also another advantage of using the Convert() function over the automatic conversion: reliability. If one of the dates is not interpreted correctly or is interpreted as NULL during automatic conversion, the output you will get will most likely be a generic date like Jan 01 1900 00:00 – not very useful now, is it?

Speaking of weird years, you should be careful when using two-digit year formats, as SQL Server has a cut-off time set for the year 2049, meaning that you won’t be able to use “45” to note the year World War II ended, as it will be interpreted as 2045. However, using what you’ve just learned about the Convert() function, we are pretty sure you know how to handle this.

Last but not least, you should consider using the Convert() function, along with other necessary functions, in order to make your code as exact and as safe as possible. Few people are aware that an incomplete code can lead to security leaks that might get your SQL server compromised.