This is the query

SELECT EMPLOYEECODE,cast(CAST(RIGHTS(LEFTS(DATEOFJOINING,5),2) AS
INTEGER)||'/'||
CAST(LEFTS(DATEOFJOINING,2) AS INTEGER)||'/'||
CAST(RIGHTS(DATEOFJOINING,4) AS INTEGER) as
date) FROM EMPLOYEEMASTER

(mm/dd/ccyy format )


Thanks for your help.

Sivaraman

At 08:53 AM 31/01/2003 +0000, you wrote:

>Hi,
>
> > >The short answer is that you can't cast
> > >this string directly to a date type via SQL, because it is not a
>valid date
> > >literal (the engine does not support mm/dd/ccyy as a date format).
>
>
> > > > I have one field as String which is storing the date
>value as
> > > >string.(Eg: 16/01/2003 as string ).
> > > >Now how can i convert this string into Date in query.
> > > >
> > > >I tried this query.But it is giving conversion error from
>string.
> > > >
> > > >select cast(field_name as date) from table_name;
>
> I think, there is a little misunderstanding.
>16/01/2003 is a dd/mm/ccyy format, not mm/dd/ccyy.
>AFAIK the engine supports the latter one.
>
>One (not very elegant) solution would be to convert the string
>into a recognizable format "on fly". For example (only for
>Firebird!):
>
>select cast(substring(field_name from 1 for 2) || '.' ||
> substring(field_name from 4 for 2) || '.' ||
> substring(field_name from 7 for 4) as date) field_name2
> from table_name;
>
>If you use InterbaseX.X then you must use a UDF function instead of
>"substring".
>
> Robi
>
>
>
>
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to the
><http://docs.yahoo.com/info/terms/>Yahoo! Terms of Service.