September 23, 2008 at 5:49 am
Our application SQL server system has UK regional settings, that is DD/MM/YYYY. If I try to insert a date with MM/DD/YYYY format, it gives error "out of range datetime value". This is fine.
But If I convert it with format 101 - which is again MM/DD/YYYY format, it doesn't give any error. Why is it like this ?
Convert(datetime,'09/17/2008',101)
And If I convert with 103 format which is DD/MM/YYYY format, it gives the same error.
Convert(datetime,'09/17/2008',103)
I can't understand, how is it like this ? Is my understanding incorrect regarding this date conversion ?
September 23, 2008 at 6:22 am
As your data stores in the format of YYYY-MM-DD
In case of format 101 is will return MM/DD/YYYY
In case of format 103 is will return DD/MM/YYYY
So in case of 103 the month will go out of date time value so you will get the error.
Ex:If your storing the to-day's date into the table it will store in the following way if you use 101 format.
2008-09-23 00:00:00.000
If you use 103 format where it had to store like 2008-23-09 in which the month exceeds more than 12 you will get any error.
September 23, 2008 at 6:53 am
Ok. I got it.
convert(datetime,'09/17/2008',101)
Earlier I was misunderstanding above statement. What I thought was above statement converts the given date in 101 format. But actually it means that we want to store the given date in 101 format and not convert it in that format.
This is fine. My next question is Do we have to do the same thing for retrieving the date also ?
September 23, 2008 at 7:26 am
Actually, SQL Server stores date times as 2 4byte ints. the first is the number of days before or after the base date (1900-01-01) and the second stores the number of miliseconds since Midnight on that date.
You don't have to convert it one way or another to insert it, you just have to make sure it's a proper datetime. I've never had trouble inserting dates as 'yyyy-mmm-dd' as the server will handle the character conversion and always get the appropriate month and day without any guessing.
-Luke.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply