Re: sysdate/time syntax

Hi,

Han Thomas <han_at_royal.net> wrote in article <37a7ac14.167119404_at_Oracle8>...
> On Tue, 3 Aug 1999 14:41:22 -0500, "Joet Singh" <jsingh_at_bigfoot.com>
wrote:
<snip>
> to set a date and time of 4 August 1999 21:58 pm , use the function
to_date
> which will convert any date and time in character string format to a date
> (and time) value to store in a date field as follows:
>
> to_date(date_string, format)
>
> so
>
> to_date('04-AUG-99-2158', 'DD-MON-YY-HH24MI')
>
> Note that the colon (:) between hours, minutes and seconds may at times
> confuse the syntax, so better not use it when entering time values.
>
NOT TRUE. You can always use : or / or anything else to separate the individual date items
in the string and certain characters in the date format. IMHO as long as you use the correct format
to pick out those date items from the string, it works. The following statements work fine. I have
5 blanks between 99 and 21 and also in the format between YY and HH.

select to_char(to_date('04/AUGX99 21$58', 'DD-MON-YY HH24:MI'),'dd/mm/yy hh:mi:ss')
 from dual;
select to_char(to_date('04/AUGX99 21$58', 'dd+mon:yy HH24-MI'),'dd-mon-yy "HELLO THERE" hh24:mi:ss') from dual;

Of course I don't recommend it. But SQL in this case is very forgiving.

Suresh Bhat
Oracleguru
www.oracleguru.net
oracleguru_at_mailcity.com

> Han.
>
> +----------------------------------------------------------+
> | Han Thomas 30/1 Ton Kham Road, Tha Sala |
> | Han_at_Royal.net Muang Chiang Mai 50000 THAILAND |
> | www.afmgroup.com/han tel 053-308958 fax 249854 |
> +----------------------------------------------------------+
>
Received on Wed Aug 04 1999 - 16:15:05 CEST