Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2005
    Posts
    40

    Unanswered: Is there any db2 date function to convert from integer to date?

    Hi all,

    Working on db2 8.2.2 and solaris 5.9. Is there any db2 date function which converts integer value to actual date. As we are storing date as integer in our database and wanted to convert it into date with a date function while retriving it.

    Can anyone help here please.

    Reply's will be appreciated

    thanks in advance...

  2. #2
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Give an example of how it is stored. Based on that you should be able to use functions to do this.

    Code:
    $ db2 "create table dd(dd integer)"
    DB20000I  The SQL command completed successfully.
    $ db2 "insert into dd values(01102005)"
    DB20000I  The SQL command completed successfully.
    $ db2 "select date(dd) from dd"
    
    1
    ----------
    03/10/3018
    
      1 record(s) selected.
    
    $
    HTH.

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I don't think that conversion was correct 01102005 <> 03/10/3018

    Just wondering why store date as an integer and spend resources converting it on the retrieval? I think it would only benefit if you were simply storing data with out ever touching it. because if you are going to ask for this data your performance is not going to be so good because it will have to convert every record you ask for. I think performnace always takes a front row seat vs. storage, considering how cheap storage is now days. just my 2 cents
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Jan 2005
    Posts
    40
    Quote Originally Posted by Cougar8000
    I don't think that conversion was correct 01102005 <> 03/10/3018

    Just wondering why store date as an integer and spend resources converting it on the retrieval? I think it would only benefit if you were simply storing data with out ever touching it. because if you are going to ask for this data your performance is not going to be so good because it will have to convert every record you ask for. I think performnace always takes a front row seat vs. storage, considering how cheap storage is now days. just my 2 cents
    Cougar,

    You are right but the design is like that. Can u sujjest any db2 date function which converts integer to date and vice versa.

    thanks in advance....

  5. #5
    Join Date
    Dec 2005
    Posts
    39
    I think you might need to write your own function. You can try with to_date & to_char functions as well but it probably expects a timestamp field hence your own function would be the ideal way to go.

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I do not beleave it is possible to convert integer to date. On the other hand with some creativity you can convert to char followed with substr followed with convertion to the date, month and year. However, you have put your self into a corner by putting those values into an integer field because now you have lost your leading zero. So, if you are going to substr where do you start? Record with 12202005 month will start at position 3, but for 1202005 it will start at position 2. See what I mean.

    I understand that it is designed that way, but walls are built with the intent to be broken later on. I think it is very simple once you make them realize that what they are trying to do is not possible because of the current design.

    There is however another solution that is probably equal to playing Russian rullet. They,Application folks, can do the conversion using their native program. It should not be very hard to store the value and then use VB or anything like that to put a logik and break it apart. I do not want to think what it will do to your performance.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Jan 2005
    Posts
    40
    Quote Originally Posted by Cougar8000
    I do not beleave it is possible to convert integer to date. On the other hand with some creativity you can convert to char followed with substr followed with convertion to the date, month and year. However, you have put your self into a corner by putting those values into an integer field because now you have lost your leading zero. So, if you are going to substr where do you start? Record with 12202005 month will start at position 3, but for 1202005 it will start at position 2. See what I mean.

    I understand that it is designed that way, but walls are built with the intent to be broken later on. I think it is very simple once you make them realize that what they are trying to do is not possible because of the current design.

    There is however another solution that is probably equal to playing Russian rullet. They,Application folks, can do the conversion using their native program. It should not be very hard to store the value and then use VB or anything like that to put a logik and break it apart. I do not want to think what it will do to your performance.



    Cougar,

    Actually we are storing the date value as 1104555600 which represents 01/01/2005 00:00:00 GMT-5.

    i guess u r thinking in the other way like storing the date (01/01/2005) as 01012005, which is wrong.

    thanks....

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

Posting Permissions

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