I have a column in my database named signup_date with a datatype of
bigint. I want to know how to convert this bigint into datetime
correctly as a result of a sql query.

My sql query is this

SELECT FROM_UNIXTIME(LEFT(signup_date, LENGTH(signup_date) - 3)) AS
date_mili, users_id FROM users WHERE users_id = 210693

Signup_date = 1163609047
But results is 1970-01-14 19:13:29

Thanks for the help!

-robi

On 10-02-11 09:32, robie wrote:
"DB2 Basics: Fun with Dates and Times"
http://www.ibm.com/developerworks/da.../0211yip3.html

If that page does not help, could you post some values, and the date
that is should be ?

SELECT BIGINT(CURRENT TIMESTAMP) FROM sysibm.sysdummy1
20110210102808

--
Luuk

On 2011-02-10 09:32, robie wrote:
Assuming signupd_date is seconds since 1970

db2 "values cast('1970-01-01-00.00.00.0' as timestamp) + 1163609047 seconds"

1
--------------------------
2006-11-15-16.44.07.000000

1 record(s) selected.


/Lennart

To answer this question, you first need to tell us what signup_date is having. Is it julian day (doesn't look like) or seconds past first julian day?

Submitted via EggHeadCafe
SQL Operations on a Text File with ADO.NET
http://www.eggheadcafe.com/tutorials...th-adonet.aspx