THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

SSIS gotcha – Regional Settings can affect your expressions

I recently stumbled across a nuance of the SSIS expression language which, when you think about, kinda make sense – but it does help to be aware of it. Its concerned with casting of datetime values using the SSIS expression language

Take the following expression:

(DT_WSTR,30) @[System::ContainerStartTime]

That expression casts a datetime value into a string value. If I evaluate that with my OS Regional Settings set to English (United Kingdom) I see this:

image

If I set my OS Regional Settings to English (United States) I see this:

image

Note how that simple change to the regional settings has caused the result of my expression to change. This could have dangerous consequences; for example, if you are using the result of this expression in a dynamically built SQL statement (as I was) then one of two things will happen, either you will get the wrong result or you’ll get an error. Observe how, n my dynamically built SQL statement, I’m CONVERTing a string literal (which is constructed using the above expression) to a datetime value:

image 

however with a simple change of my regional settings to English (United States) I see this:

image

and when you run that particular SQL statement in SSMS:

image

it blows up!

Definitely one to be aware of! Watch those Regional Settings and their affect on casting of dates in the SSIS expression language!

What should you do instead?

If you need a failsafe way of constructing a date that doesn’t rely on Regional settings then consider something like the following:

(DT_WSTR,4)YEAR( @[System::ContainerStartTime] ) + "-" +
RIGHT("0" + (DT_WSTR,2)MONTH( @[System::ContainerStartTime] ), 2) + "-" +
RIGHT("0" + (DT_WSTR,2)DAY(@[System::ContainerStartTime] ), 2)

That expression will build a date string with format YYYY-MM-DD (which is the ISO-ratified unambiguous way of representing a date) regardless of Regional Settings:

image

@Jamiet

Published Tuesday, April 09, 2013 11:05 AM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mike S said:

Surely the best way is to change the short date format to show "MMM", that way there is no mistaking which is the month and which is the day

April 9, 2013 6:04 AM
 

Ian Yates said:

@Mike S

The ISO format has the nice property of easy sorting even if the date is being treated purely as a string.  I personally wish ISO format dates were used EVERYWHERE on the web - I hate trying to guess if a page is doing dd/mm/yyyy or mm/dd/yyy.  (I am biased towards the former since I'm in Australia, but frankly it also makes more sense since months, as a concept, sit in between days and years)

April 9, 2013 11:28 AM
 

jamiet said:

Ian, agree completely. The argument about sorting is often forgotten (including by me) but is an important one.

Mike, I would respond but Ian has done it for me :)

Thanks both for the comment.

April 10, 2013 3:40 AM
 

Greg Low said:

Sadly, the only safe format within the database engine for a date is YYYYMMDD without the dashes. Once you have dashes, it's language dependent on smalldatetime and datetime but not on datetime2, date, datetimeoffset, etc.

It would be nice if there was a way to tell SQL Server that when I say AAAA-BB-CC that I always mean YYYY-MM-DD, without SET options.

April 10, 2013 6:21 AM
 

jamiet said:

Greg,

Thanks for the clarification.

I sorta blogged about this once at:

http://sqlblog.com/blogs/jamie_thomson/archive/2009/12/08/unambiguous-date-formats-t-sql-tuesday-001.aspx

and Tibor Karaszi provided the same clarification that you have just done in the comments, so you'd think I would have known this :).

Well worth a read, anyway!

JT

April 10, 2013 7:53 AM
 

Herbit said:

Date format issues can be a nightmare in any application, be it a database or some executable.  It drives me insane when I  have to work across networks and the device I am talking to has its date format set differently to the one I am working on. I have taken to explicitly querying the target machine to detect such conflicts before they bite me.  The folk who install the servers seem to be ignorant of this and always leave date formats in the out of the box setting.

April 14, 2013 1:00 AM
 

Robert Heinig said:

IMHO for date/string handling in SSIS there's no way around .net script, the "o" format specifier and DateTimeOffset.TryParseExact. And Greg, appending 'T00:00:00' to your AAAA-BB-CC does not need a SET option ;)

May 3, 2013 10:18 AM
 

Kenneth M. Nielsen said:

Dateformat is a constant key to annoyment for all us database developer. I wish we could have one universal standard for date, but wait we have! Lets use ISO format, and save us all some real trouble. Ithen someone would like to show the date in another format on the screen, then by all means convert, cast, replace or even concatenate the s*#! out of the ISO date stored in the database. But always store it in ISO.

Just my 50 € cents on the topic

May 21, 2013 5:51 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement