Convert the strings into Date Format

Database
Enthusiast

Convert the strings into Date Format

Hi everyone, i need to convert all the strings into Date format in a single query.

2012/11/24

2012-11-24

2012 11 24

20121124

24/11/2012

24-11-2012

24 11 2012

24112012

11/24/2012

11-24-2012

11 24 2012

11242012

12/11/24

12-11-24

24/11/12

24-11-12

11/24/12

11-24-12

1121124

Not only the formats i mentioned above, whatever the formats i give, the query has to convert into right format. Is it possible?

Tags (1)
4 REPLIES
Senior Apprentice

Re: Convert the strings into Date Format

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.

Dieter

Enthusiast

Re: Convert the strings into Date Format

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..

WAQ
Enthusiast

Re: Convert the strings into Date Format

Hi Mathuram,

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.

WAQ
Enthusiast

Re: Convert the strings into Date Format

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
(
date_t varchar(12)
) on commit preserve rows;

insert into te select '2012/11/24';
insert into te select '2012-11-24';
insert into te select '20121124';

select date_t
,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')
end
from te;