Converting timestamp to int?

[Hemant Shah]

Folks,

How can I convert date/time/timestamp to an integer?

According to UDB 8.1 docs that I have,
INTEGER('1964-07-20') should return 19640720, but when I run the SQL
statement I get different answer:

# db2 "VALUES INTEGER(CHAR(1964-07-20))"

1
-----------
1937

1 record(s) selected.

How can I convert CURRENT TIMESTAMP to a integer?

# db2 "VALUES INTEGER(CHAR(CURRENT TIMESTAMP))"

1
-----------
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018


--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Sanjuro]

On Dec 12, 10:19 am, Hemant Shah <[e-mail]:

Folks,

How can I convert date/time/timestamp to an integer?

According to UDB 8.1 docs that I have,
INTEGER('1964-07-20') should return 19640720, but when I run the SQL
statement I get different answer:

# db2 "VALUES INTEGER(CHAR(1964-07-20))"

1
-----------
1937

1 record(s) selected.

How can I convert CURRENT TIMESTAMP to a integer?

# db2 "VALUES INTEGER(CHAR(CURRENT TIMESTAMP))"

1
-----------
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: [e-mail] \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

There is a function called DAYS(expression) that does something
similar to what you want.

DAYS scalar function

>>-DAYS--(--expression--)--------------------------------------><

The schema is SYSIBM.

The DAYS function returns an integer representation of a date.

The argument must be a date, timestamp, or a valid character string
representation of a date or timestamp that is neither a CLOB nor a
LONG VARCHAR. In a Unicode database, if a supplied argument is a
graphic string, it is first converted to a character string before the
function is executed.

The result of the function is a large integer. If the argument can be
null, the result can be null; if the argument is null, the result is
the null value.

The result is 1 more than the number of days from January 1, 0001 to
D, where D is the date that would occur if the DATE function were
applied to the argument.

[Ian]

Hemant Shah wrote:

Folks,

How can I convert date/time/timestamp to an integer?

According to UDB 8.1 docs that I have,
INTEGER('1964-07-20') should return 19640720, but when I run the SQL
statement I get different answer:

# db2 "VALUES INTEGER(CHAR(1964-07-20))"

1
-----------
1937

1 record(s) selected.

This doesn't work as you typed it.

values integer(current date) returns:

== 20071212

values integer(date('1964-07-20')) returns:

== 19640720

How can I convert CURRENT TIMESTAMP to a integer?

# db2 "VALUES INTEGER(CHAR(CURRENT TIMESTAMP))"

1
-----------
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018

Use the BIGINT() function:

values bigint(current timestamp)

== 20071212085812

[Hemant Shah]

While stranded on information super highway Ian wrote:

Hemant Shah wrote:

>Folks,

How can I convert date/time/timestamp to an integer?

According to UDB 8.1 docs that I have,
INTEGER('1964-07-20') should return 19640720, but when I run the SQL
statement I get different answer:

# db2 "VALUES INTEGER(CHAR(1964-07-20))"

1
-----------
1937

1 record(s) selected.

This doesn't work as you typed it.

values integer(current date) returns:

== 20071212

values integer(date('1964-07-20')) returns:

== 19640720

>How can I convert CURRENT TIMESTAMP to a integer?

# db2 "VALUES INTEGER(CHAR(CURRENT TIMESTAMP))"

1
-----------
SQL0420N Invalid character found in a character string argument of the
function "INTEGER". SQLSTATE=22018

Use the BIGINT() function:

values bigint(current timestamp)

== 20071212085812

I tried bigint, but it does not work:

# db2 "values BIGINT(CURRENT TIMESTAMP)"
SQL0440N No function by the name "BIGINT" having compatible arguments was
found in the function path. SQLSTATE=42884
I am running DB2 v7.1.0.77 on Linux.

--
Hemant Shah /"\ ASCII ribbon campaign
E-mail: No************@xnet.com \ / ---------------------
X against HTML mail
TO REPLY, REMOVE NoJunkMail / \ and postings
FROM MY E-MAIL ADDRESS.
-----------------[DO NOT SEND UNSOLICITED BULK E-MAIL]------------------
I haven't lost my mind, Above opinions are mine only.
it's backed up on tape somewhere. Others can have their own.

[Serge Rielau]

Hemant Shah wrote:

I am running DB2 v7.1.0.77 on Linux.

What one finds in the basement, eh?
http://www.cbc.ca/arts/artdesign/sto...galleries.html

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Tonkuma]

On Dec 13, 5:42 am, Hemant Shah wrote:

I tried bigint, but it does not work:

# db2 "values BIGINT(CURRENT TIMESTAMP)"
SQL0440N No function by the name "BIGINT" having compatible arguments was
found in the function path. SQLSTATE=42884

I am running DB2 v7.1.0.77 on Linux.

INTEGER(date | time) and BIGINT(date | time | timestamp) are supported
on DB2 V8 or later. DB2 V7 doesn't support those arguments.
Although it will be better to upgrade DB2 as Serge suggested, another
temporary solution may be to create UDF(s).