Thanks June, that answer works a treat.

Regards,
Bryce Stenberg.
Harness Racing New Zealand computer department,
emailto:bryce@hrnz.co.nz

> -----Original Message-----
> From: June Hunt [mailto:june_c_hunt@hotmail.com]
> Sent: Wednesday, 30 July 2003 7:20 a.m.
> To: Bryce Stenberg
> Subject: Re: to_date function: format parameter for fractions of a
> second?
>
>
> Building on something that Jonathan Leffler suggested back on
> 2002-11-14
> (see Re: merge DATE and TIME to DATETIME), the following
> might be helpful:
>
> UPDATE xxx SET new_date = EXTEND(my_date, YEAR TO SECOND) +
> (INTERVAL(00:00:00) HOUR TO SECOND + my_time)
>
> I ran a quick I4GL test with this code, and it worked for me
> against an
> older SE database.
>
> --
> June Hunt
>


DISCLAIMER: http://www.hrnz.co.nz/eDisclaimer.htm

sending to informix-list

I encountered the same problem working with IDS 9.4.
I looked at GL_DATETIME environment variable. It gave me the formatin
directives. I am pasting them here. But it did not tell me how to us
the "%Fn" format option. So I messed around with it and finally got i
to work.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Formatting Directives Description
%c Is replaced by a special datetime representation that the local
defines.
%Fn Is replaced by the value of the fraction of a second wit
precision that is specified by the integer n. The default value of n i
2; the range of n is 0 ≤ n ≤ 5. This value overrides an
width or precision between the % and F character. For more information
see Optional Time Format Qualifiers.
%H Is replaced by the hour as an integer (00 through 23) (24-hou
clock).
%I Is replaced by the hour as an integer (00 through 12) (12-hou
clock).
%M Is replaced by the minute as an integer (00 through 59).
%p Is replaced by the A.M. or P.M. equivalent as defined in th
locale.
%r Is replaced by the commonly used time representation for a 12-hou
clock format (including the A.M. or P.M. equivalent) as defined in th
locale.
%R Is replaced by the time in 24-hour notation (%H:%M).
%S Is replaced by the second as an integer (00 through 61). The secon
can be up to 61 instead of 59 to allow for the occasional leap secon
and double leap second.
%T Is replaced by the time in the %H:%M:%S format.
%X Is replaced by the commonly used time representation as defined i
the locale.
%% Is replaced by % (to allow % in the format string).
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

when I tried using --
TO_DATE('2006-04-10 08:50:16.000', '%Y-%m-%d %H:%M:%S.%F3')

watch for the "dot" between %S and %F. It kept throwing error 1271.


I played enough with it and got it to work when I use the followin
format

TO_DATE('2006-04-10 08:50:16.000', '%Y-%m-%d %H:%M:%S %F3')

watch for "SPACE" between %S and %F.

It is confusing, but it works....

regards
Ram

***********************************************
ORIGINAL MESSAGE
***********************************************

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_dat
function.

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

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

Which looks like I need a format string for combining date and tim
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): Inpu
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 o
the
necessary formatting string values. %R looked promising but cuts of
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 separat
date
and time fields to a single datetime field and then drop the separat
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 (AT) hrnz (DOT) co.n

--
ramm
-----------------------------------------------------------------------
rammo's Profile: http://www.dbtalk.net/m34
View this thread: http://www.dbtalk.net/t3571