Hello

I want to convert a string field to a date in a query.
I try CAST(StrField AS DATE) but this probably isn't possible or I miss
something.

What others option I have to change the string field so that the result will
be a formatted date?

Thanks in advance
Sorin

Sorin


What is the format of the string field? To CAST as date it needs to be in the right format.

Roy Lambert

It has only the date numbers 25032012 and I want the result as 25/03/2012

Thanks
Sorin

þþý"Roy Lambert" ýþ ëúá áäåãòä
news:A375DC5A-06BE-4F30-BA1F-0397D3CAF86B@news.elevatesoft.com...

Sorin


What is the format of the string field? To CAST as date it needs to be in
the right format.

Roy Lambert

Sorin

>It has only the date numbers 25032012 and I want the result as 25/03/2012

If you want it as an actual date that still wouldn't work. You'd need it in the format 2012-03-25. In either case you have to build the string yourself using SUBSTR


SUBSTR(strfld,1,2)+'/'+SUBSTR(strfld,3,2)+'/'+SUBSTR(strfld,5,4)

or

SUBSTR(strfld,5,4)+'-'+SUBSTR(strfld,3,2)+'-'+SUBSTR(strfld,1,2)

Roy Lambert [Team Elevate]

Sorin,

in addition to Roy's answer: you can use a function like this (in this
case for the German date format):

CREATE FUNCTION "FormatDate" (IN "InDate" DATE)
RETURNS VARCHAR(10) COLLATE ANSI
BEGIN
   RETURN CAST(EXTRACT(DAY FROM InDate) as VARCHAR(2)) + '.' +
          CAST(EXTRACT(MONTH FROM InDate) as VARCHAR(2)) + '.' +
          CAST(EXTRACT(YEAR FROM InDate) as VARCHAR(4));
END

Uli

Rereading your post: you were looking for the opposite thing: string ->
date. Sorry.

Anyway you can create a function to make things easier.

Uli