faryzeusz faryzeusz - 4 months ago 47
SQL Question

SQL Server : how to convert float yyyymmdd to date yyyy-mm-dd?

In other posts you advice to use

TO_DATE(your_date as String, 'YYYY-MM-DD')


but in SQL Server there is no function
to_date
:(

I tried something like this:

select
start_date2 = replace(substring(cast(start_date as varchar),1,5),'.','') + '-' +
case when substring(cast(start_date as char),6,2) ='1e' then '10' else substring(cast(start_date as char),6,2) end + '-' +
cast(substring(cast(start_date as char),8,2)as char)
from
my_table


but it returns for example:

1971-05-e+


instead of:

1971-05-08

Answer

Assuming the original data type is actually float, this can be done with multiple castings:

DECLARE @Date float = 19710508

SELECT  CAST(
            CAST(
                CAST(@Date as int) 
            as char(8)) 
        as date)

Result (date data type):

1971-05-08

The inner cast removes all the digits after the decimal point, The middle cast converts the int value to a char value, since explicit cast from int to date is not allowed, and the outer cast converts the char value to date.