Explicit date format conversions

Don't let it to the system configuration

Josep

Implicit date format conversions, when no format is specified, are always a source of troubles when:

Passing dates as parameters in scripts to databases, other scripts, applications, web services or other targets, because each system is waiting the dates in specific format.
Running a SQL script in databases with different language settings.

Using implicit date conversions make the conversion relying on the culture-specific server’s configuration or/and every software configuration. The problem usually appears when:

Installing the script in another server that has a different configuration.
For any reason the configuration is changed and suddenly the script fails. Or even worst, a script that’s not very used fails some weeks or months after the change and then it’s more difficult to find the error.

Implicit date format conversions: It fails easily

In these examples it’s easy to identify when and why they fail, but you can get crazy when you’ve one powershell script in one server that works with a SQL Server database in another server and a PostgreSQL in a 3rd server running in Linux. And things can be worst when your are not administrating the servers.

Example in powershell with different culture server configuration

# Windows configured with: English (United States)
Get-Date -Format G
# Result: 6/26/2012 3:46:55 PM

# Windows configured with: Spanish (Spain)
Get-Date -Format G
# Result:  26/06/2012 15:46:55

Example in SQL Server with different language settings

In this example I use CAST to convert the string to date using implicit format conversion:

-- Works
SET Language Spanish
DECLARE @d DATETIME
SET @d = CAST('30/05/2012' AS DATETIME)
SELECT CONVERT(VARCHAR, @d, 120)
-- Result:  2012-05-30 00:00:00

-- Fails
SET Language us_English
DECLARE @d DATETIME
SET @d = CAST('30/05/2012' AS DATETIME)
SELECT CONVERT(VARCHAR, @d, 120)
--Error:
--   Mens. 242, Nivel 16, Estado 3, Línea 3
--   The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Or what’s even worst, a silent malfunction when the number of the day is <= 12 it won’t fail and will interchange the day for the month. In the following example, instead of May 12th the DATETIME value will be December 5th. Detecting and solving it is not trivial when you’ve many registers inserted or updated erroneously.

SET Language us_English
DECLARE @d DATETIME
SET @d = CAST('12/05/2012' AS DATETIME)
SELECT CONVERT(VARCHAR, @d, 120)
-- Result:  2012-12-05 00:00:00

Explicit date format conversions: Always works

To avoid these errors, the best thing is to make conversions explicit, using the format you feel more comfortable. In my case, I like to use yyyy-MM-dd HH:mm:ss when I’m working with date + time, while when I only need the date I use yyyy-MM-dd or dd/MM/yyyy. Now I’ll show some examples how to do it in Powershell, SQL Server and PostgreSQL.

Powershell

With Powershell we use System.Date struct, available in .NET framework. To convert from date to string I use ToString function and from string to date I use ParseExact. Powershell to output a date to a computer screen or a file has to convert it to string and uses the computer’s culture configuration.

# Date to String:
(get-date).ToString("yyyy-MM-dd HH:mm:ss")
# Result: 2012-06-27 09:55:51
# String to date:

[System.Datetime]::ParseExact('2012-05-30 09:07:39',"yyyy-MM-dd HH:mm:ss", [System.Globalization.CultureInfo]::CreateSpecificCulture("es-ES"))
#Result: miércoles, 30 de mayo de 2012 9:07:39

[System.Datetime]::ParseExact('30/05/2012',"dd/MM/yyyy",[System.Globalization.CultureInfo]::CreateSpecificCulture("es-ES"))
# Result: miércoles, 30 de mayo de 2012 0:00:00

SQL Server

As we’ve seen before, CAST is used for implicit format conversion, but to use the conversion we want, we must use CONVERT, a non SQL standard command. The numbers used in the 3rd parameter of CONVERT can be found in SQL Server help.

-- Date to String:
DECLARE @d DATETIME
SET @d = CONVERT(DATETIME, '2012-05-30 09:07:39', 120)
SELECT CONVERT(VARCHAR, @d, 120) --> 2012-05-30 09:07:39
SELECT CONVERT(VARCHAR, @d, 103) --> 30/05/2012

-- String to date:
SELECT CONVERT(DATETIME, '30/05/2012 09:07:39', 120)

PostgreSQL

The official PostgreSQL documentation it’s really good and has several examples.

-- Date to String:
SELECT to_char(current_timestamp, 'YYYY-MM-DD HH24:MI:SS')
-- String to date:
SELECT to_timestamp('2012-05-30 09:07:39', 'YYYY-MM-DD HH24:MI:SS')

Conclusion

Using explicit date format conversions will help you to write safer scripts, less prone to bugs and there’s no extra time needed to do it :-)

It’s funny but I got the idea of writing this post two weeks ago when a workmate had problems with a Powershell script because the servers had different configurations. But today, when had this post already prepared to be published, another workmate has sent me a SQL Server script to be run in a server. It failed because the SQL Server in his computer was using Spanish language while the server was in English.