Derbie Derbie - 6 months ago 23
SQL Question

Datetime conversion issue with SQL server

I've generated some fake data for a database i've created using PowerAMC. This database as some filed with timedate format. But when i'm running the file in SQL server (Express edition), i've the following :

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


The line concerned :

insert into INVENTORY (INVENTORY_ID, STATUS, START_DATE, COMPLETION_DATE) values (7, 'MAHSTDIV9', '1005-5-19 9:48:44', '1235-2-16 9:42:10')


So I think that the date format is not the expected one (maybe dd-mm-yyyy instead of what I have) but I dind't find out what I could do to prevent that.

What I've done so far:

Try to convert the date

select CONVERT(DATETIME,'345-12-9 3:23:52',120)
-> Conversion failed when converting date and/or time from character string.
select CAST(CONVERT(VARCHAR(100),'345-12-9 3:23:52',120) AS datetime)
-> The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


Do you have any idea about how to fix it ?

Answer

Datetime only goes back to 1753. Use datetime2 instead, datetime2 was introduced in sql server 2008

example

select CONVERT(DATETIME2,'0345-12-9 3:23:52')