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

Give an example of how it is stored. Based on that you should be able to use functions to do this.

HTH.

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

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.

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

http://www.dbforums.com/t1004639.html