Convert CHARACTER to Date

[vladab]

I am trying to convert a field of CHARACTER(8) data type into Date format 'yyyy-mm-dd'. The following query produce 2665 (Invalid date) error :

select cast('20041101' as date format 'yyyy-mm-dd')
from table_name

Please advice how to fix this error.

Vlada

 

[al1x24]

I think that select cast('20041101' as date format 'yyyymmdd') would give a better result.

 

[vladab]

Yes. I already changed it to the way you are suggesting and it worked.

Thank you.

 

[ripperfuel]

How about this:

select cast(cast('20041101' as date format 'yyyymmdd') as date format 'yyyy-mm-dd');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

'20041101'
----------
2004-11-01