RE: to_date function: format parameter for fractions of a second?

I encountered the same problem working with IDS 9.4. I looked at GL_DATETIME environment variable. It gave me the formating directives. I am pasting them here. But it did not tell me how to use the "%Fn" format option. So I messed around with it and finally got it to work. @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Formatting Directives Description %c Is replaced by a special datetime representation that the locale defines. %Fn Is replaced by the value of the fraction of a second with precision that is specified by the integer n. The default value of n is 2; the range of n is 0 ≤ n ≤ 5. This value overrides any 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-hour clock). %I Is replaced by the hour as an integer (00 through 12) (12-hour 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 the locale. %r Is replaced by the commonly used time representation for a 12-hour clock format (including the A.M. or P.M. equivalent) as defined in the 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 second can be up to 61 instead of 59 to allow for the occasional leap second 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 in 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 following 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_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 (AT) hrnz (DOT) co.nz -- rammo ------------------------------------------------------------------------ rammo's Profile: http://www.dbtalk.net/m343 View this thread: http://www.dbtalk.net/t35718