programmerist programmerist - 2 days ago 5
SQL Question

How can I handle a SQL query by using convert decimal string to datetime in SQL Server?

I am trying to convert a decimal string to datetime:

20160709.0000000 => 09-07-2016 (dd-MM-YYYY)


but the code here returns an error:


Conversion failed when converting date and/or time from character string


Code:

select
CONVERT(DATETIME, CONVERT(nvarchar(50), MyBirthDate), 112) BirthDate, Test, Test2
from
tbl


This code also does not work:

select
CONVERT(DATETIME, CONVERT(VARCHAR(50), '20160709.0000000'), 112)

Answer

At first you need to convert string to int or else you can use replace function

SELECT CONVERT(DATETIME, REPLACE('20160709.0000000', '.0000000', ''))

or

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(50), CAST(20160709.0000000 AS INT )), 112)

or

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(50), CAST(20160709.0000000 AS INT )))
Comments