By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,226 Members | 1,166 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,226 IT Pros & Developers. It's quick & easy.

Converting timestamp to int?

P: n/a

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.
Dec 12 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Dec 12, 10:19 am, Hemant Shah <s...@typhoon.xnet.comwrote:
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: NoJunkMails...@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.
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.
Dec 12 '07 #2

P: n/a
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

Dec 12 '07 #3

P: n/a
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.
Dec 12 '07 #4

P: n/a
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
Dec 12 '07 #5

P: n/a
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).
Dec 13 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.