Numeric "date" in database - How to interact with that using "normal" dates?

Question

I'm using this database where the date colomn is a numeric value instead of a Date value.

Yes, I know I can change that with a mouseclick, but all the applications using that database were made by one of my predecessors (and everyone after him just ignored it and built on). So if I'd change it to Date a lot af applications would fail. :(

Well, I am making an overview of that database, ranging from one specfic date to another. I tried using a dropdown list but as you can tell, a thousand options in one list is terribly inconvenient, even ugly.

I rather have small inputfields for day - month - year, but there comes waltzing in the numeric date in the database. I would have to calculate the date back to the numeric value somehow...

There must be an easy solution to this. Right?



I'm using ASP(vbscript) for the application, it's for an intraweb, and I have an Access Database.

Answer 1

Access stores date internally as a floating point number (number of days since 31.12.1899 or something), have you tried using CDate() to convert the number back to a date? You should be able to query using BETWEEN then.

Another possibility is that the date is stored numerically but not converted (i.e 31121899), in this case you should be able to get the appropriate date parts using either Format() or Day() or Month() or Year().

Hope this helps.

Answer 2

I actually prefer storing dates in a database as an integer, in milliseconds since the epoch (Java timestamp). And twice a year, when daylight saving time comes and goes, eBay messes it up and I laugh.

Answer 3

Sadly the date is stored as 39791 -> 9-dec-2008. The floating point number.

I have a javascript function (also written by the same predecessor) that convertes the date to a readable format. It's the other way around, the way back to that numeric value that baffles me.

Answer 4

Access will convert to a number for you, as was mentioned, the dates are stored as numbers.

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("TestTable")
rs.AddNew
rs!NumberDate = Now()  'Value stored, eg, 39791.4749074074 '
rs.Update

rs.MoveLast

'To show that it converts back to the correct date / time '
Debug.Print Format(rs!NumberDate, "dd/mm/yyyy hh:nn:ss")

EDIT re comment: Here is a small test that shows dates returned:

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

strFile = "C:\Docs\LTD.mdb"

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
       "Data Source=" & strFile & ";" & _
       "Persist Security Info=False"

strSQL = "SELECT NumberDate FROM TestTable WHERE NumberDate= #2008/12/7#"

rs.Open strSQL, cn, 3, 3
rs.MoveLast

MsgBox rs.RecordCount

Answer 5

I stumbled upon DateDiff().

Yet, when I test this, it goes mad.

I have this code:

Function DateToNumeric(dayDate)
    DateToNumeric=DateDiff("d","01/01/1900",dayDate) 
    //I want difference in days, that's what the 'd' is for. The date in there is day 1.
End Function

    response.Write("09/12/2008, should be 39791.<br /><br />") //according to Excel :P
    response.Write("DateToNumeric('09/12/2008') gives: " &DateToNumeric("09/12/2008")& "<br />")
    response.Write("CDate('39791') gives: " &CDate("39791"))

The output that code gives me is this:

Given: 09/12/2008, should be 39791.

DateToNumeric('09/12/2008') gives: 39789
CDate('39791') gives: 9-12-2008

I'm two days off! D:<

Edit: Remou, forgive me if I read you post wrong, but converting the number to a date is not the problem. I use CDate for that. It's the other way round where the problem lies.

If I have a user giving me a date (eg. 9/12/2008) in an input field, I want to be able to read the dababase record for that date (which is 39791).

Also, is that code snippet Visual Basic? I'm using VBscript... :$

Answer 6

Right.. so it was real easy. I'll accept Remou's answer, the WHERE NumberDate= #2008/12/7# does the trick.

But, to put a close to the matter, here's the solution to my own fiddling with Functions:

Function DateToNumeric(dayDate)
    DateToNumeric=DateDiff("d","31/12/1899",dayDate) +1 //yup
End Function

    response.Write("9/12/2008, should be 39791.<br /><br />")
    response.write("DateToNumeric('9/12/2008') gives: " &DateToNumeric("9/12/2008")& "<br />")
    response.write("CDate('39791') gives: " &CDate(39791)&"<br /><br />")
    response.write("BECAUSE CDate('1') gives: " &CDate(1))

output:

9/12/2008, should be 39791.

DateToNumeric('9/12/2008') gives: 39791
CDate('39791') gives: 9-12-2008

BECAUSE CDate('1') gives: 31-12-1899

It's made it so that 31/12/1899 is not day Zero, but day One. :/

Thanks guys!