Hi,

I am trying to select values from a date field and time field ( char(8) ) to
combine into a datetime field.

But I'm having problems with the format string used in the to_date function.

If I just select any datetime field from the database it comes out looking
(for example) like:

"2002-12-19 17:56:27.21"

Which looks like I need a format string for combining date and time fields
with the to_date function something like:

to_date( my_date || " " || my_time, "%Y-%m-%d %H:%M:%S.???")

What should replace the question marks for fractions of a second?

Is this do-able without getting the error "SQL error (-1277): Input does not
match format specification."

Or is there a way to ignore the fractions of a second?

Looking in the Informix manuals I could find no full description of the
necessary formatting string values. %R looked promising but cuts off at
minutes. I tried 'google' and see others have had similar problems but
found no answer.

(I want to the above so ultimately I can update tables with separate date
and time fields to a single datetime field and then drop the separate date
and time fields from the table).

We are running IDS 7.31 on Windows NT.

Thanks for any help,
Bryce Stenberg.
Harness Racing New Zealand computer department,
emailto:bryce@hrnz.co.nz


DISCLAIMER: [url]http://www.hrnz.co.nz/eDisclaimer.htm[/url]

sending to informix-list