Handling DATETIME values in the queries is often challenging due to various factors which include choosing wrong datatype(VARCHAR instead of DATETIME),formatting in sql,expressing values in regional specific format,removing time part,etc.
Well. Here is the question that came up in a forum.
I have this query:
update table
set initTime = '2012-08-20 09:30:00.000'
WHERE id='0124'
and it says:
conversion from varchar to datetime generated an out of range value.
Can you help me out please?
Ok. What do you think about this question? The date is expressed correctly and as most people think it is unambiguous. Can you simulate the above error? Well. See what happens you do the following
set dateformat ydm
select cast('2012-08-20 09:30:00.000' as datetime)
You get the following error
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
So what is the solution? You should learn how to express DATETIME values in an unambiguous ways.
The three true unambiguous date formats are
YYYYMMDD
YYYYMMDD HH:MM:SS
YYYY-MM-DDTHH:MM:SS
The third format has T, the time seperator which seperates TIME from DATE. So when you use YYYY-MM-DD you should use time seperator T to make the format a true unambiguous. Now see what happens when you execute the following
set dateformat ydm
select cast('2012-08-20T09:30:00.000' as datetime)
It returns the DATETIME value 2012-08-20 09:30:00.000
So you should always use UNAMBIGUOUS date format when expressing DATETIME values in your queries and NEVER worry about how datetime values are internally stored or what the current date format of the server is
I have already explained about DATETIME datatype in four different blog post which are listed below
Understanding Datetime column - Part I
Understanding Datetime column - Part II
Understanding Datetime column - Part III
Understanding Datetime column - Part IV
In particular Part II will explain on how to deal with different date formats and usage of unambiguous date formats