Dates - Cast 1

[sdempsey]

I am having a problem with the dates. In my database the dates are stored as varchars in the format yyyyddmm and I would like them changed to yyyymmdd. However when I try to cast them to a date to allow me to do a convert operation. I am am getting an out of range error.

Is there an other way of doing it.

Thanks in advance

 

[JamesLean]

I would probably do:

SELECT LEFT(date, 4) + RIGHT(date, 2) + SUBSTRING(date, 5, 2)

--James

 

[micha123]

Is it possible that you have "garbage" data within that char field?
You can

SET DATEFORMAT YDM
select * from TABLE WHERE ISDATE ( dateCharField ) = 0

this will bring you all the fields that are not valid, and you would have to fix them.


"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.