CREATE TABLE ##T(MyDateTime DATETIME)
INSERT INTO ##T (MyDateTime) VALUES('2008-09-23 09:12:44.310')-- Result: 2008-09-23 09:12:44.310 inserted.
INSERT INTO ##T (MyDateTime) VALUES('20080923') --Must be passed as string. Result 2008-09-23 00:00:00.000
inserted.
INSERT INTO ##T (MyDateTime) VALUES('23/09/2008') --Error- The conversion of a char data type to a datetime
data type resulted in an out-of-range datetime value.
--The statement has been terminated. In the format ../../....
SQL Server expects first value as the month. Month value 23 is invalid.
INSERT INTO ##T (MyDateTime) VALUES('09/23/2008') --Result 2008-09-23 00:00:00.000 inserted.
INSERT INTO ##T (MyDateTime) VALUES('SEPT 23 2008')--Error- Conversion failed when converting datetime from
character string.
-- Reason- We should either specify three characters for
month or full name.
INSERT INTO ##T (MyDateTime) VALUES('SEP 23 2008') --Result- 2008-09-23 00:00:00.000 inserted.
INSERT INTO ##T (MyDateTime) VALUES('SEPTEMBER 23 2008') --Result- 2008-09-23 00:00:00.000 inserted.
INSERT INTO ##T (MyDateTime) VALUES('SEP 23 08') --Result- 2008-09-23 00:00:00.000 inserted.
INSERT INTO ##T (MyDateTime) VALUES('SEP 23 50')--Result- 1950-09-23 00:00:00.000 inserted.
--Reason- When we specify only the last two digits of the year, from 00 to 49
will be accepted for the years from 2000 to 2049. I suggest we should always
specify four digit years to prevent any unexpected errors. So finally I shall
drop the temp table to finish the example.
