Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Convert Bigint to DateTime

2,592 views
Skip to first unread message

robie

unread,
Feb 10, 2011, 3:32:54 AM2/10/11
to
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

Luuk

unread,
Feb 10, 2011, 4:29:00 AM2/10/11
to

"DB2 Basics: Fun with Dates and Times"
http://www.ibm.com/developerworks/data/library/techarticle/0211yip/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

Lennart Jonsson

unread,
Feb 10, 2011, 4:37:57 AM2/10/11
to

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

sanjay kumar

unread,
Feb 10, 2011, 7:53:53 AM2/10/11
to
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/aspnet/37ed9e1b-c5de-4c0b-afbe-d8f78f9a6ecf/sql-operations-on-a-text-file-with-adonet.aspx

0 new messages