Read date time from database

I read a date field from an existing database (old no longer supported app). The app and the database are not developed be me.

The application has a date field with 2012-05-03

External database:
Field: date
Type: numeric

SELECT zdate FROM ptable
zdate = 3.57693e+08

If I use this:
var date d as new date
d.totalseconds = rs.Column(“zdate”).DoubleValue
d.SQLDateTime = 1915-05-02 23:04:33

If I use:
SELECT datetime(zdate,‘unixepoch’) FROM ptable
zdate = 1981-05-02 23:04:33

SELECT datetime(zdate,‘unixepoch’, ‘localtime’) FROM ptable
zdate = 1981-05-03 01:04:33

But it should be 2012-05-03

Is this SQLite? Do you mean that the zdate field contains a string with ‘2012-05-03’ ?

3.57693e+08 is about 358 million. With a year being about 33 million secs, a resulting date of May 1981 seems reasonable.

Sorry Tim… you are off by 66 years

Dim dt As New date
dt.TotalSeconds =  3.57693e+08
msgbox dt.SQLDateTime

2012-05-03 is around 3.418931e+09 seconds

Seems his database is giving dates from “2001-01-01” … NOT Unix Epoch

Dim dt As New date
dt.TotalSeconds =  dt.TotalSeconds+ 3.57693e+08
MsgBox dt.SQLDate

He was probably using the API 2 epoch of 1970-01-01.

Dave beat me to it. You can determine the “epoch” for this custom date format with API 2 code like this:

Var startdt As DateTime = DateTime.FromString("2012-05-03") Var di As New DateInterval di.Seconds = 3.57693e+08 Var enddt As DateTime = startdt - di MessageBox enddt.ToString
Of course, the seconds you list have been been truncated because of the exponential format, which is probably why the above results in Dec 31, 2000 at 11:50:00 PM. Using the full, accurate value from the db should result in 2001-01-01 as Dave suggests. You should test this against a few of the values to confirm this assumption.

Well it was fairly clear that if the expectation was that the date should be 2012-05-03, then 3.57693e+08 was not going to be anywhere near enough. I was just pointing out that with this value, a date of May 1981 would be OK if you have the belief that the number given was a unixepoch.

It clearly isn’t and since posting the above I’ve been trying to see how 2012-05-03 relates to 3.57693e+08 but I’ve been going about it the wrong way.

if the date is supposed to be 2012-05-03 and you have 3.57693e+08 it appears that the date value in the DB is based off of Jan 1, 2001(an offset of 30611155000 seconds)

Dim d2 As New date
d2.TotalSeconds = 3.57693e+08

Dim baseDate As New Date
baseDate.TotalSeconds = 3061155000

Dim d3 As New date
d3.TotalSeconds = d2.TotalSeconds + baseDate.TotalSeconds


but you’d have to test this with other well know values to see if this holds true for other date values