Conversion failed when converting datetime from character string.

Question

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

DECLARE @TABLE AS TABLE(

Linedate VARCHAR(40),

Date VARCHAR(40))

INSERT INTO @TABLE

SELECT '4/15/2005', '4/29/2005 12:00:00 PM'

SELECT CONVERT(DATETIME, linedate, 101),

CONVERT(DATETIME, DATE, 101)

FROM @TABLE

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 2

the only difference i can see in the syntax is ' '

but i have tried and got this:

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

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 3

looks like i don't have to convert a string into datetime format while inserting the rows to another table,sql server will automatically do it.

but i have some empty string fields which sql can't convert while inserting the column to another table.......

can anyone please tell me how should i convert empty string into empty datetime or null.

David Burrows SSC Guru Points: 65142 More actions · Answer 4

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

Most common reason is the default language setting on your logins as this defines date format of either dd/mm/yyyy or mm/dd/yyyy

You can override this by using

SET DATEFORMAT mdy

can anyone please tell me how should i convert empty string into empty datetime or null.

CAST(NULLIF(@date,'') as datetime)

Far away is close at hand in the images of elsewhere.
Anon.

srilu_bannu Hall of Fame Points: 3100 More actions · Answer 5

Thank you very much for the replies,and i thought i gave reply for this ,the real problem is with my data ..

Most of the date fields have the dates like: 07/29/0200,06/14/0210 etc.........

my destination column date will accept nulls ........so i have changed the data manually for dates less than 1900-01-01 and imported successfully...

Abhijit More SSCertifiable Points: 6897 More actions · Answer 6

you can use cast function to covnert it.

DECLARE @LineDate varchar(40), @Date varchar(40)

SET @LineDate = '4/15/2005'

SET @Date = '4/29/2005 12:00:00 PM'

SELECT CAST(@LineDate AS DATETIME), CAST(@Date AS DATETIME)

Abhijit - http://abhijitmore.wordpress.com

pankushmehta Ten Centuries Points: 1217 More actions · Answer 7

This should work

SELECT CAST(@LineDate AS DATETIME), convert(datetime, convert(varchar,@date,101),101 )

(tweak the 101 to the kind of format you want).