Jamie Thomson : expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/tags/expressions/default.aspxTags: expressionsenCommunityServer 2.1 SP2 (Build: 61129.1)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.aspxTue, 09 Apr 2013 09:05:33 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48586jamiet8http://sqlblog.com/blogs/jamie_thomson/comments/48586.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=48586http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=48586<p>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</p> <p>Take the following expression:</p> <blockquote> <p><font face="Consolas">(DT_WSTR,30) @[System::ContainerStartTime]</font></p> </blockquote> <p>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:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5618F800.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_2073BD87.png" width="509" height="181" /></a> </p> </blockquote> <p>If I set my OS Regional Settings to English (United States) I see this:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_115CDEAD.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5E20A543.png" width="508" height="174" /></a> </p> </blockquote> <p>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:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_7CF71921.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_74FF76BF.png" width="420" height="110" /></a>&#160;</p> </blockquote> <p>however with a simple change of my regional settings to English (United States) I see this:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_4CECC7A0.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_52C76B39.png" width="418" height="123" /></a> </p> </blockquote> <p>and when you run that particular SQL statement in SSMS:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_2AB4BC1A.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_498B2FF8.png" width="544" height="130" /></a> </p> </blockquote> <p>it blows up!</p> <p></p> <p></p> <p></p> <p></p> <p></p> <p></p> <p>Definitely one to be aware of! Watch those Regional Settings and their affect on casting of dates in the SSIS expression language!</p> <h3>What should you do instead?</h3> <p>If you need a failsafe way of constructing a date that doesn’t rely on Regional settings then consider something like the following:</p> <blockquote> <p><font face="Consolas">(DT_WSTR,4)YEAR( @[System::ContainerStartTime] ) + &quot;-&quot; + <br />RIGHT(&quot;0&quot; + (DT_WSTR,2)MONTH( @[System::ContainerStartTime] ), 2) + &quot;-&quot; + <br />RIGHT(&quot;0&quot; + (DT_WSTR,2)DAY(@[System::ContainerStartTime] ), 2)</font> </p> </blockquote> <p>That expression will build a date string with format YYYY-MM-DD (which is <a href="http://xkcd.com/1179/" target="_blank">the ISO-ratified unambiguous way of representing a date</a>) regardless of Regional Settings:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0EE91966.png"><img title="image" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_62CC1C74.png" width="530" height="199" /></a> </p> </blockquote> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=48586" width="1" height="1">expressionssql server integration servicesssisFun and games with Reporting Services expressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2009/12/10/fun-and-games-with-reporting-services-expressions.aspxThu, 10 Dec 2009 16:23:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:19741jamiet6http://sqlblog.com/blogs/jamie_thomson/comments/19741.aspxhttp://sqlblog.com/blogs/jamie_thomson/commentrss.aspx?PostID=19741http://sqlblog.com/blogs/jamie_thomson/rsscomments.aspx?PostID=19741<p>I have today been messing about with Reporting Services' expression language and as such have learnt a few things that I figured might be worth sharing.</p> <p>The report that I have been attempting to build has two parameters that define the effective reporting period for the report, effectively they are arguments in a WHERE clause:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/20091210params_76BF90FC.jpg"><img style="border-bottom:0px;border-left:0px;display:inline;border-top:0px;border-right:0px;" title="20091210params" border="0" alt="20091210params" src="http://sqlblog.com/blogs/jamie_thomson/20091210params_thumb_2E91D520.jpg" width="565" height="36" /></a> </p> <p>The business rule that I had to implement for these parameters was:</p> <blockquote> <p><i>The effective reporting period is the most recently completed month</i> <br /></p> </blockquote> <p>In other words, I had to determine the first day and last day of the previous month which for me (as I was trying to do this on 8th December 2009) was 1st November 2009 and 30th November 2009. </p> <p>How would you have gone about this? Here was my first attempt at determining &quot;From Effective Date&quot; (i.e. 1st November 2009) based on today's date:</p> <blockquote> <p><span>=CDate( <br />&#160;&#160;&#160; CStr( <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; Year(DateAdd(&quot;mm&quot;, -1, Now())) * 10000 + <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; Month(DateAdd(&quot;mm&quot;, -1, Now())) * 100 + <br />&#160;&#160;&#160;&#160;&#160;&#160;&#160; 1 <br />&#160;&#160;&#160; ) <br />)</span></p> </blockquote> <p>You can probably decipher my logic here. Subtract one month from today's date and use the first day of the month of the result. Sound logic but unfortunately it failed with a <a href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=519531#details" target="_blank">totally unhelpful error message </a>:</p> <blockquote> <p><b>&quot;<span>An error occurred during local report processing. &lt;parameter name&gt;</span>&quot;</b> <br /></p> </blockquote> <p>After investigation it seems as though the CDate() function does not like strings in the format YYYYMMDD so here was my next attempt:</p> <blockquote> <p>=CDate( <br />&#160;&#160;&#160; CStr(Year(DateAdd(DateInterval.Month,-1,Now()))) + &quot;-&quot; + <br />&#160;&#160;&#160; RIGHT(&quot;0&quot; + CStr(Month(DateAdd(DateInterval.Month,-1,Now()))), 2) + <br />&#160;&#160;&#160; &quot;-01&quot; <br />)</p> </blockquote> <p>This one worked fine and I was going to stick with it until <a href="http://twitter.com/summitcloud">Summitcloud</a> suggested using the DateSerial(...) function instead like so:</p> <p align="center">=DateSerial(Year(<font color="#0000ff">DateAdd(DateInterval.Month,-1, Now()))</font>, <font color="#008000">Month(DateAdd(DateInterval.Month,-1, Now()))</font>, <font color="#ff00ff">1</font>)</p> <p>That's a much nicer method in my opinion, no hacky string manipulation going on here, only three numeric arguments (which I have highlighted in different colours) to define year/month/day thus I changed to use DateSerial(…). Happy with that I turned to working out my &quot;To Effective Date&quot; using the same DateSerial() function. This time my logic was to get the first day of the <i>current</i> month and then subtract one day from it:</p> <p align="center">=DateAdd(DateInterval.Day, -1, DateSerial(<font color="#0000ff">Year(Now())</font>, <font color="#008000">Month(Now())</font>, <font color="#ff00ff">1</font>))</p> <p>Worked a treat!</p> <p>So I guess the lesson here is to make sure you know all the tools in your toolbox and what they all do. Don't use pliers (string manipulation) to tighten a nut when you already have a spanner(DateSerial)! (Does that analogy work? Maybe not!! :)</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p><img src="http://sqlblog.com/aggbug.aspx?PostID=19741" width="1" height="1">expressionssql serverSQL Server Reporting Services