Re: TO_DATE defaults to Current Month - Please Help!

Many thanks to all who replied to my query for pointing out the glaring error in my statement.

The solution to the problem was however more complex than might have been thought. The original problem was compounded by the fact that the UPDATE statements that we were using were being created on the fly by PowerBuilder, so we could not hack in a statement like : TO_DATE ("01/01/00 13:00:00", "DD/MM/YY HH:MM:SS").

The ORACLE database contained times in the native format - including year/month/day/hour etc. These were being converted on retrieval to times for display. On converting back to datetimes (PowerBuilder term) the date portion that was lost during the retrieval defaulted to the 1st of the current month, causing problems.

The solution for this problem was to _not_ discard the date portion of the datetime, and to store it in a non-visible editmask field. So the columns retrieved from the database were:

select field_datetime,

	field_datetime fdt_date,
	field_datetime fdt_time,
	etc...

where fdt_date is of PowerBuilder type date and fdt_time is of type time (PowerBuilder handles the conversions internally). Any changes then made to the fdt_time field are then reflected in field_datetime, and the update works correctly.

Sorry if you're not interested in this, but thanks again for the help.

Charlie Received on Mon Dec 04 1995 - 00:00:00 CET