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