SQL Server Datetime – How to Fix Date Conversion Error

datetimesql server

This is just an example:

SELECT * FROM A.B where B_DATE='19-05-2016 12.00.00.000000000 AM';

I always get an error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.#

I have looked at trying to use CONVERT, but I can't see any format that matches the datetime format I have above. Any advice on how I can rectify this?

Best Answer

If you are using a 'literal date' you need to type the format differently. If you are using a variable or field you will need to use substring and parse into the correct format.

I have a DateTime2 field in a table and the following works, similar to your query but formatted differently:

SELECT * FROM Table_1 where CreatedOn='2016-05-19T00:00:00.0000000';

Related Solutions

Sql-server – SQL Server 2008 Convert String to Datetime question

Use CONVERT and specify 1 as the date time style 1 = mm/dd/yy.

CONVERT(datetime, RIGHT('CXL P/D 08/15/13', 8), 1)

SQL Server – How to Convert VARCHAR to DATETIME Format

There is too much precision in the varchar to be converted into datetime.

One option(better in my opinion) would be to change the target column to datetime2(7). Then you can convert like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(@dt as datetime2(7));

If changing the column is not an option the you can do the conversion like this:

declare @dt varchar(50)
set @dt = '2015-12-02 20:40:37.8130000'

select cast(cast(@dt as datetime2(7))as datetime)

Best Answer

Related Solutions

Sql-server – SQL Server 2008 Convert String to Datetime question

SQL Server – How to Convert VARCHAR to DATETIME Format

Related Question