473,836 Members | 2,210 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Converting timestamp to int?


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(19 64-07-20))"

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

1 record(s) selected.

How can I convert CURRENT TIMESTAMP to a integer?

# db2 "VALUES INTEGER(CHAR(CU RRENT 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
5 33156
On Dec 12, 10:19 am, Hemant Shah <s...@typhoon.x net.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(19 64-07-20))"

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

1 record(s) selected.

How can I convert CURRENT TIMESTAMP to a integer?

# db2 "VALUES INTEGER(CHAR(CU RRENT 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
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(19 64-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('1 964-07-20')) returns:

== 19640720

How can I convert CURRENT TIMESTAMP to a integer?

# db2 "VALUES INTEGER(CHAR(CU RRENT 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
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('196 4-07-20') should return 19640720, but when I run the SQL
statement I get different answer:

# db2 "VALUES INTEGER(CHAR(19 64-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('1 964-07-20')) returns:

== 19640720

>How can I convert CURRENT TIMESTAMP to a integer?

# db2 "VALUES INTEGER(CHAR(CU RRENT 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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
13524
by: Frank | last post by:
I am timing employees who take a test. I have the start time and end time, which are unix epochs, and subtracing the start from the end will give me a large number of seconds, for example 552. I want to convert this into minutes and seconds. How do I do this? Thanks
1
16711
by: David Greenberg | last post by:
Hi I'm interested in using the timestamp data type & I have some questions. As far as I can understand the contant of a timestamp column is a binary value. Is there any connection between that value and a valid date (as the word timestamp means) or is it a left over from the days when the timestamp value really was a datetime type (so it says in Books Online) ? or is it just a unique identification of a row (a tuple id) ? I tried...
0
6207
by: Eugene | last post by:
Hi, I have been battling with this for days now. If you execute the SQL below you will get the following result: 0x3078303030303030, which is incorrect. It looks like the conversion is more literal than I am wanting. Does anybody know the correct way to convert a timestamp that has been stored as a char type, back into a varbinary(8)? (vb(8) is the suggested data type for storing timestamps) ==============================
2
4380
by: Russell Smith | last post by:
Timestamps support infinity. However if appears dates do not. When timestamps are cast to dates, there is no output. Is this an acceptable option or not? Below are a number of examples showing what I am experiencing. The last own shows how converting timestamps to dates and then ordering doesn't give you the order you want. Maybe you should just order by the timestamp to begin with. However Date does not understand infinity at all.
2
5874
by: shenanwei | last post by:
DB2 V8.2 on AIX, type II index is created. I see this from deadlock event monitor. 5) Deadlocked Connection ... Participant no.: 2 Lock wait start time: 09/18/2006 23:04:09.911774 ...... Deadlocked Statement: Type : Dynamic Operation: Execute
6
34110
by: marc | last post by:
hi im trying to convert Date() into a unix timestamp so i can stick the result into a mysql db, please help!
2
2516
by: Yash3 | last post by:
Hey I am trying this create or replace FUNCTION DatesPast ( v_dueDate Timestamp ) RETURN NUMBER AS DaysPast DATE BEGIN
3
2082
by: wongjoekmeu | last post by:
Hello all, I have a C++ program ( I am not sure if this is the right place to post this question ), One of the function returns two unsigned integers which are referred to be TimestampLo and TimestampHi. Can anyone please help me out how I am supposed to convert these two integer to an understandable timestamp. It says also for the TimestampLo variable that this is the lower 32-bits and for the TimestampHi variable is the higher...
11
2758
by: dougancil | last post by:
I have the following query that was written by someone else that I need help converting to a SQL query, can anyone assist me with this? Thank you SELECT .Last_First_Name, .Employee_Number, format(((-((/1000)/60))/1440)+1,"mm/dd/yy") AS , format(((-((/1000)/60))/1440)+1,"dddd") AS , Sum((/1000/60)) AS LogIn, Sum((/1000/60)) AS OnTime1, Format(((-((/1000)/60))/1440)+1,"yy/mm/dd") AS Da INTO 1_1ScratchPad
0
9810
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10821
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10527
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10241
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9358
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7773
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6973
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5812
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4001
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.