|
View previous topic :: View next topic
|
| Author |
Message |
hcl_ln
New User
Joined: 14 Sep 2005
Posts: 21
|
|
|
|
Hello -
I am trying to insert a row into a table. In that table, the first row is a Timestamp value. Here I am using the UNIQUE_TIMESTAMP() function to insert into the table value and I am getting a -180. Has anybody used this function in the DB2 before? If so, can anybody please tell me how to use this function.
Thanks for your time! |
|
| Back to top |
|
 |
MFRASHEED
Active User
Joined: 14 Jun 2005
Posts: 186
Location: USA
|
|
| Back to top |
|
|
hcl_ln
New User
Joined: 14 Sep 2005
Posts: 21
|
|
|
|
Much thanks for the reply.
Yes, it is a user defined function, I beleive.
The idea behind using the UNIQUE_TIMESTAMP() function is: to capture the exact time in micro seconds.
From CURRENT TIMESTAMP function it is returning : 2006-12-04-12.17.14.724213
From UNIQUE_TIMESTAMP() function it is returning : 2006-12-04-12.17.14.394080
And the syntax which I am using is:
| Code: |
EXEC SQL
SET :HOST-TS-VARIABLE=UNIQUE_TIMESTAMP()
END-EXEC
SELECT TABLE_TS_VARIABLE
INTO :HOST-TS-VARIABLE
FROM FINAL TABLE (
INSERT INTO TABLE_TS
(
TABLE_TS_VARIABLE
)
VALUES
(
UNIQUE_TIMESTAMP()
)
) |
Much thanks for your time! |
|
| Back to top |
|
|
sgaid21
New User
.jpg)
Joined: 12 Sep 2006
Posts: 16
Location: Canada
|
|
|
|
If you are getting SQLCODE=-180 that means invalid data value.
Check if your column definition compatible with the UNIQUE_TIMESTAMP() return value.
Make sure :HOST-TS-VARIABLE PIC x(26).
I hope this answer would help you.[/img] |
|
| Back to top |
|
|
hcl_ln
New User
Joined: 14 Sep 2005
Posts: 21
|
|
|
|
Hello All -
Forgot to post my results set.
I got the answer.
When using this UNIQUE_TIMESTAMP() function in DB2 V8, for some reason the date value in the time stamp is inserted as YYYY/MM/DD instead of the normal DB2 valid value YYYY-MM-DD. So I had to convert the date using some date conversion routine to get the correct results set. Then my -180 is gone and inserted into the DB2 table correctly.
Hope this helps in someone else's research.
Thanks |
|
| Back to top |
|
|
ravi17s
Warnings : 1
New User
Joined: 15 Aug 2003
Posts: 57
|
|
|
|
Cool.
You can also use the following conversion to convert the date into valid format
Name Layout Example
ISO yyyy-mm-dd 2002-10-22
USA mm/dd/yyyy 10/22/2002
EUR dd.mm.yyyy 22.10.2002
JIS yyyy-mm-dd 2002-10-22
LOCAL Locally defined layout N/A
CHAR(START_DATE,USA) |
|
| Back to top |
|
|
|
|
