Results 1 to 4 of 4

Thread: Time Conversion

  1. #1
    Join Date
    Oct 2007

    Unanswered: Time Conversion


    I'm not very experienced in using Informix-SQL.
    So my problem might be quite simple.

    I retrieve a time from an Informix-DB. This time is stored in a very odd format.
    The results of the select statement should be a real time - so I have to convert it.

    I retrieve the times in the following format:
    0 -> 00:00
    30 -> 00:30
    100 -> 01:00
    130 -> 01:30
    200 -> 02:00
    230 -> 02:30
    2300 -> 23:00
    2330 -> 23:30

    With MS-SQL i can do this by:
    dateadd(d,-2,dateadd(n,starttime / 100 *60 + starttime % 100,0))

    But this doesn't work under Informix-SQL.

    Could someone give me a hint?



  2. #2
    Join Date
    Oct 2007
    What data type are you storing your time field in? If you want the system to give you times in a time format, you need to store the data ina datetime data type. The drawback is that you will have to store a "Date" with it too (but you don't need to actually use the date). If you store the data in that data type, you will need to format the data correctly when you insert it (i.e. the data should be inserted in the format "2007-10-24 12:12:23". Whe you go to select the fiedl you can select it in such a way that you can tell it to give you only the time. Example: table name test_tbl and the time you want is in column named tran_time, which is a datetime datattype. Yuo select could be:

    Select extend(tran_time, hour to second) from test_tbl where.....

    This will return the time to you as "12:12:23". If you only wanted the hour and minutes, you would change the extend statment to be "extend(tran_time, hour to minute)".

    Hope this helps,

  3. #3
    Join Date
    Oct 2007

    it seems as if I decsribed my problem a little bit ambiguous.

    Maybe this could be because of my bad english.

    Ok - next try:

    I want to transfer data from a Informix-DB on another server into a MS-SQL-DB.
    On my MS-SQL2005 Server I have the possibility to make select-statements of the Informix DB.
    So I can do something like

    insert into my_ms_sql_table (select starttime from my_informix_table)

    I starttime column in my MS-SQL-DB is defined as datetime-datatype.

    From the select-statement I get the starttime-data like '0','30','100','130','200','230',...,'2300','2330'
    But to insert it into my MS-SQL-table it must be in a datetime-format (or maybe a string that could be a datetime-string).

    So I'd like to convert the data within the select-statement.

    I hope that you're able to understand what I mean.



  4. #4
    Join Date
    Oct 2007
    Thx. Got it.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts