CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
nebra
nebra

Reputation: 55

CONVERT throw Error converting data type date to varchar

I've got a table INTEG_LOG which contains a date column DATE_LOG. From that date, I need to extract the date in the format 20171907 and the hour like 094225.

In the first place I tried to get my date. For that I use this line:

SELECT
    REPLACE(CONVERT(VARCHAR(10), DATE_LOG, 111), '/', '') AS LOG_DATE
FROM
    INTEG_LOG

It's not really clean but it's working fine.

I decided to do the same for the hour with this line:

SELECT
    REPLACE(CONVERT(VARCHAR(8), DATE_LOG, 108), ':', '') AS LOG_HOUR
FROM
    INTEG_LOG

But this SQL throws

Error converting data type date to varchar

I even tried without the replace and I still get the same error.

I don't really get why the first convert with the style 111 is working fine but not the 108.

I'm using SQL Server 2008 R2.

Thanks

Upvotes: 0

Views: 1962

Answers (1)

sepupic
sepupic

Reputation: 8687

I've got a table INTEG_LOG which contains a date column DATE_LOG

The error you've got is legitimate because the date type has no time component, so you cannot convert it to string using style 108

Try this

select REPLACE(CONVERT(VARCHAR(100), CAST(getdate() AS DATE), 108), ':', '')

and you'll get the same error.

So you should just select a constant like this:

select '000000' AS LOG_HOUR

or alternatively cast your date to datetime and you'll get the thame using your code:

SELECT
    REPLACE(CONVERT(VARCHAR(8), cast(DATE_LOG as datetime), 108), ':', '') AS LOG_HOUR
FROM
    INTEG_LOG

Upvotes: 2

Related Questions