SSIS gotcha – Regional Settings can affect your expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/09/ssis-gotcha-regional-settings-can-affect-your-expressions.aspxI 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 followingenCommunityServer 2.1 SP2 (Build: 61129.1)re: SSIS gotcha – Regional Settings can affect your expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/09/ssis-gotcha-regional-settings-can-affect-your-expressions.aspx#48587Tue, 09 Apr 2013 10:04:57 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48587Mike S<p>Surely the best way is to change the short date format to show &quot;MMM&quot;, that way there is no mistaking which is the month and which is the day</p>
re: SSIS gotcha – Regional Settings can affect your expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/09/ssis-gotcha-regional-settings-can-affect-your-expressions.aspx#48591Tue, 09 Apr 2013 15:28:22 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48591Ian Yates<p>@Mike S</p>
<p>The ISO format has the nice property of easy sorting even if the date is being treated purely as a string. &nbsp;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. &nbsp;(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)</p>
re: SSIS gotcha – Regional Settings can affect your expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/09/ssis-gotcha-regional-settings-can-affect-your-expressions.aspx#48608Wed, 10 Apr 2013 07:40:45 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48608jamiet<p>Ian, agree completely. The argument about sorting is often forgotten (including by me) but is an important one.</p>
<p>Mike, I would respond but Ian has done it for me :)</p>
<p>Thanks both for the comment.</p>
re: SSIS gotcha – Regional Settings can affect your expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/09/ssis-gotcha-regional-settings-can-affect-your-expressions.aspx#48615Wed, 10 Apr 2013 10:21:14 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48615Greg Low<p>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.</p>
<p>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.</p>
re: SSIS gotcha – Regional Settings can affect your expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/09/ssis-gotcha-regional-settings-can-affect-your-expressions.aspx#48620Wed, 10 Apr 2013 11:53:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48620jamiet<p>Greg,</p>
<p>Thanks for the clarification.</p>
<p>I sorta blogged about this once at:</p>
<p><a rel="nofollow" target="_new" href="http://sqlblog.com/blogs/jamie_thomson/archive/2009/12/08/unambiguous-date-formats-t-sql-tuesday-001.aspx">http://sqlblog.com/blogs/jamie_thomson/archive/2009/12/08/unambiguous-date-formats-t-sql-tuesday-001.aspx</a></p>
<p>and Tibor Karaszi provided the same clarification that you have just done in the comments, so you'd think I would have known this :).</p>
<p>Well worth a read, anyway!</p>
<p>JT</p>
re: SSIS gotcha – Regional Settings can affect your expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/09/ssis-gotcha-regional-settings-can-affect-your-expressions.aspx#48668Sun, 14 Apr 2013 05:00:16 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48668Herbit<p>Date format issues can be a nightmare in any application, be it a database or some executable. &nbsp;It drives me insane when I &nbsp;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. &nbsp;The folk who install the servers seem to be ignorant of this and always leave date formats in the out of the box setting.</p>
re: SSIS gotcha – Regional Settings can affect your expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/09/ssis-gotcha-regional-settings-can-affect-your-expressions.aspx#48958Fri, 03 May 2013 14:18:07 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48958Robert Heinig<p>IMHO for date/string handling in SSIS there's no way around .net script, the &quot;o&quot; format specifier and DateTimeOffset.TryParseExact. And Greg, appending 'T00:00:00' to your AAAA-BB-CC does not need a SET option ;)</p>
re: SSIS gotcha – Regional Settings can affect your expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/09/ssis-gotcha-regional-settings-can-affect-your-expressions.aspx#49141Tue, 21 May 2013 09:51:22 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:49141Kenneth M. Nielsen<p>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.</p>
<p>Just my 50 € cents on the topic</p>