Re: TO_DATE function question

From: David N McCann <cs91dnm_at_brunel.ac.uk>
Date: Fri, 2 Jul 1993 09:13:16 GMT
Message-ID: <C9J5M5.64H_at_brunel.ac.uk>

In article <1993Jul1.144005.20361_at_icf.hrb.com> gml_at_icf.hrb.com (Gretchen M. Lenze) writes:
>
>I am having the following problem with the "TO_DATE" function ... can
>anyone help me out?
>
>I am trying to update a field of type DATE with a date in the form
>
> Thu Jul 1 11:00:00 1993
> ^ ^^ ^ ^
>
>To do this I use the call:
>
> insert into testtable(testcolumn) values (
> TO_DATE('Thu Jul 1 11:00:00 1993',
> DY Mon DD HH24:MI:SS YYYY'));
>
>My problem is that in my date string, UNIX puts a blank before the
>day if the date is earlier than the 10th of the month, and ORACLE
>expects a leading 0 (or _something_ other than a blank). Is there
>a way to get around this problem without inserting a leading zero
>if the date is only one digit?

I'm not absolutely sure that I know what your problem is as I can get the row to insert with no trouble. The problem you might be having is with retrieving the row, in which case can I point you in the direction of the FM option in date format specifiers. It is a toggle that controls whether leading zeros (and trailing spaces) are chopped from dates as they get converted to char values. It is initally off. But try this:-

	select to_char(testcolumn,
	'Dy Mon fmDDfm HH24:MI:SS YYYY')
	from testtable;

Result

---

Wed Jul 21 11:00:00 1993 /* this row added to show FM in action */ Thu Jul 1 11:00:00 1993

What's happening here is that the retrieved column is being converted to a char value, but just before the day-number is converted Fill Mode is turned on so that the leading zero is chopped nad then its switched back off so that the rest of the format is unchanged.

        The same also works for the TO_DATE function which you use with inserts. From what you wrote it is not clear, but I wondered if you were using the output of UNIX's date command, presumably to get the current time and date, Can I strongly recommend that if this _is_ what you are wishing to do, that you use ORACLE's SYSDATE function instead. (see manuals for details.)

	+----------------------------------------------------------+
	| The above answer is provided direct from the output of   |
	| ORACLE v6.0.33 so I know that it works there.  I think   |
	| should work in other versions (you don't say what        |
	| version you are using.)				   |
	+----------------------------------------------------------+

		Any problems, mail me,

			David McCann

Received on Fri Jul 02 1993 - 11:13:16 CEST