Re: SQL question on to_date function

>From mail Thu Jan 18 13:48 EST 1996
>From: Bob Gatelein <GATELERA_at_MCGRAW-HILL.COM>
>
> Following syntax fails with ORA-01840: value not long enough for
> date format :
>
>
> SELECT TO_DATE(FIELD1||'01' , 'YYMMDD')
>
>
> FIELD1 value is 9502 varchar2(4).
>
> I tried parens SELECT TO_DATE((FIELD1||'01') , 'YYMMDD') and every
> other syntax I can think of but no go.
>
> I think the to_date function just does not recognize the special
> '||' concatenation character.
>
> Anybody know a workaround ?
>
>
> Thanks to all who reply.
>

This worked for me with Oracle 7.1.6 and Sqlplus 3.1.3.7.1. However, if you always want the day to be 01 there's no need to concatenate it. It will assume 01 as the day:

SELECT TO_DATE(FIELD1,'YYMM') ..... will return 01-FEB-95 if FIELD1 is '9502'.

Tom Longo
Keane Federal Systems
@U.S. Geological Survey
M/S 518
12201 Sunrise Valley Drive
Reston, VA 22092-0001
703-648-5584
tlongo_at_mpodgw7.er.usgs.gov Received on Fri Jan 19 1996 - 11:44:25 CST