Hi everyone, i need to convert all the strings into Date format in a single query.
2012 11 24
24 11 2012
11 24 2012
Not only the formats i mentioned above, whatever the formats i give, the query has to convert into right format. Is it possible?
I don't think it's possible (unless there's additional information):
Is there a difference between 12/11/24 and 11/12/24?
Both could be 2012-12-11 or 2012-11-12.
Thanks Dieter, Format of the data keep on changes, so that the month/Year/date position wil get change.. i think it wont possible in a single query..
As you already mentioned, it can not be done in a query because the format of your data is not consistent. You can certainly go for workarounds, if you want.
Well one workaround is that if you know that your date would be in specifc formats like YYYY/MM/DD, YYYY-MM-DD etc then u can use:
create volatile table te
) on commit preserve rows;
insert into te select '2012/11/24';
insert into te select '2012-11-24';
insert into te select '20121124';
,case when position('/' in date_t) > 0
then cast(date_t as date format 'YYYY/MM/DD')
when position('-' in date_t) > 0
then cast(date_t as date format 'YYYY-MM-DD')
when position(' ' in date_t) = 0
then cast(date_t as date format 'YYYYMMDD')