date to integer conversion

[hpi]

I have a table with a field which is of type integer. It stores dates
in the form 20011231 (yyyymmdd)

I want to insert records in this table with the current date using the
curdate() function. This function returns a date and cannot be stored
directly in the field.

I already tried to find conversion functions in the DB2 documentation
but no success.

What I can do is strip out the year, month, day and construct the
numeric date but this is'nt very nice.

Does anybody know if there is a function to do this real easy?

[Serge Rielau]

Try:
CREATE FUNCTION today_int() RETURNS INTEGER
CONTAINS SQL NO EXTERNAL ACTION NOT DETERMINISTIC
RETURN
INT(SUBSTR(CHAR(CURRENT DATE, ISO), 1, 4)
|| SUBSTR(CHAR(CURRENT DATE, ISO), 6, 2)
|| SUBSTR(CHAR(CURRENT DATE, ISO), 9, 2));

db2 => values today_int();

1
-----------
20010917

1 record(s) selected.

Cheers
Serge

[Roger]

You could also try with

insert into tabschema.tabname (columname) values DEC(HEX(CURRENT DATE));

DEC(HEX(CURRENT DATE)) will convert current date yyyymmdd

[Doug Doole]

This sort of function has been a popular request and we're adding it in
a future version of DB2. For now, Serge's SQL function is the easiest
thing to do.
--
_____________________________________________________________________
Doug Doole
DB2 Universal Database Development
IBM Toronto Labs

Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support

[Gert van der Kooij]

In article <bc93d2c9.01091...@posting.google.com>,
roger.k...@intentia.se says...

> You could also try with
>
> insert into tabschema.tabname (columname) values DEC(HEX(CURRENT DATE));
>
> DEC(HEX(CURRENT DATE)) will convert current date yyyymmdd
>

That's great, thanks.

[Serge Rielau]

Good one. I didn't know that hex() works on dates :-)

Cheers
Serge

[Hans Pikkemaat]

Hi,

dec does'nt really convert hex back to numeric

so we need the counterpart from hex

HPI

Roger <roger.k...@intentia.se> wrote in message
news:bc93d2c9.01091...@posting.google.com...

[Hans Pikkemaat]

I did'nt test the insert only a select

It could be that the insert in fact does work.

Hex takes the internal representation of the data and could be different for
other platforms. Pretty tricky

HPI

Hans Pikkemaat <hans.pi...@nl.clientlogic.com> wrote in message
news:3ba9cf2f$0$220$4d4e...@news.nl.uu.net...

[Serge Rielau]

gnu...@rcn.com wrote:

> that was 3 (three) years ago. i've run into some old VSAM->DB2
> converted data (& ass. COBOL programs) which store dates this way.
> this functionality in DB2/SQL would be welcome, since these guys
> don't like functions or triggers (too much to remember in
> managing DDL).
>
> any time soon?? i'm on 390/V7 & UDB/v8.2
>
Is that what you're looking for?

VALUES INTEGER(CURRENT DATE);

The function was added in V8.1 GA mostly for MDC.

Cheers
Serge

[gnu...@rcn.com]

that was 3 (three) years ago. i've run into some old VSAM->DB2

[gnu...@rcn.com]

if only it were on the 390.

(and if you've any pull at all with Google, get rid of the new
Groups page. pitiful.)

[Serge Rielau]

gnu...@rcn.com wrote:
> (and if you've any pull at all with Google, get rid of the new
> Groups page. pitiful.)
>

No request for world peace?
You will never become Miss/Mr World that way....

Cheers
Serge

[dotyet]

"Nobody likes a change, but a wet baby"

Even I have been facing problems with postings lately. But, then you
need to understand that nobody becomes a good manager the very moment
he gets out of mom's womb..... it takes him years. so relax and give
these google guys some space to breath, after all, they spent so much
resources, time, energy to reach here.

regards,
dotyet