September 10, 2009 at 5:45 am
Came across an interesting issue the other day. I wrote a UDF to convert a 4 character code into a date (this is some user data which uses a code to define a period eg. 2804 equates to 1 Apr 2008, don't ask me what happens at the end of this year!). I used this function in a stored procedure.
The code worked fine when run from Management Studio, however when I ran the stored procedure from a VB .NET (VS2008) app it appeared at first that the procedure was not running. I eventually found that the reason was that the date being returned by my function was in US format rather than UK.
I reworked the routine to use month names instead of numbers when casting to the date (which I realise makes the function more robust anyway in the event of moving to a server with different settings).
I was surprised however as my query in Management Studio just provided a 4 char string and the parameters to the command object in VB was a four char string. I would have expected that all execution from there on would be down to the SQL Server.
Does anyone know of a reason for this.
September 10, 2009 at 9:42 am
Can't really tell what you are talking about just from this. You should show us your code and some examples of what you are talking about.
thnx
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 10, 2009 at 8:32 pm
My psychic powers predict that the login you were using to run the procedure had a language setting defined which determined the default format when casting a DATETIME to a string data type (like VARCHAR) as the return value.
See http://www.karaszi.com/SQLServer/info_datetime.asp (#8 on my Google search for 'sql server date and time format')
Just a guess really - the code you post might tell us 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 11, 2009 at 1:29 am
I guess my question would be... why did you bother writing this as a CLR?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 11, 2009 at 2:23 pm
Right, if I were to guess then I would guess that you are converting or casting your date to string format without specifying an explicit format and SET LANGUAGE differences were messing you up, so you would need to use an *explicit* date conversion format.
Of course that would be just a guess. 😛
(We could give much more definitive advice if we could see something concrete.)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 11, 2009 at 2:27 pm
And yes, as Jeff implies, nine times out of ten, SQLCLR is the harder (and slower) way to do something this (apparently) simple. (And that tenth time is when it turns out to be very odd and not simple at all, which in part is why we need more information).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 11, 2009 at 2:28 pm
If I were to guess....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 11, 2009 at 4:31 pm
Thanks, Matt. I needed that. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 15, 2009 at 3:28 am
My guess (place your bets now people) is that in your UDF you are calling DateTime.Parse(). If that's the case, use DateTime.ParseExact(). Pass in null for the IFormatProvider.
I've got to say though, I can't really see how 2804 equates to the 1st of April! 😀
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
September 17, 2009 at 7:50 pm
Matt Whitfield (9/15/2009)
My guess (place your bets now people) is that in your UDF you are calling DateTime.Parse(). If that's the case, use DateTime.ParseExact(). Pass in null for the IFormatProvider.I've got to say though, I can't really see how 2804 equates to the 1st of April! 😀
Not sure here and that's why everyone has been asking the OP for more info. If I had to guess, it's like several other "Julian" style formats that even Excel will sometimes use. The "2" indicates the number of the century after 1900. The "8" is the year and the "04" is the month. But, typically, this is a 5 digit listing to accomodate 2 digit years and that's what my question was... what will the date be shown as in 2010?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply