How to form a date value from decimal or smallint values

[nidhinidhi]

Hi,
I have a table with fields like yr and month of type decimal and smallint respectively. I want to compare the date value formed by the yr and the month values in the tables by taking the day by default as 01. So the date should look like yr-month-01 in DB2. How do I combine these values and form a datre value out of them?


Please reply if you know the solution.

Thanks,
Nidhi.

[spider007]

What you can try to do is a concat and then cast it as a date. so it should look something like:

concat (char(dat), char(month), '01')
and then u can use date while comparing

date(concat (char(dat), '-',char(month), '-','01') )

I am not sure whether this will work or not. but worth a try. other experts can also response

[docdiesel]

Hi,

you'd better use || instead of concat. The latter is limited to two parameters only, so you'd have to do a lot of contat's. Furthermore, you've got to cast the decimal into an integer. Otherwise you'll get a decimal point (or comma, depends on local settings) after the year. Last but not least you've got to strip all the blanks from the casted chars with a rtrim(). Otherwise you'd get something like "2008 -10 -01".

select
  date(
    rtrim(char(integer(yr)))
    || '-'
    || rtrim(char(month))
    || '-01'
  ) as gen_date

If you've got to use this often, you should think about adding an autogenerated column based on this code to your table. If needed real often, an index on that column would be very recommended.

Regards

Doc Diesel

[docdiesel]

P.S.: You'll find a little howto about autogenerated ('generated always') columns in this thread.

Regards,

Doc Diesel