Numeric "date" in the database. How do I interact with this using "regular" dates?
I am using this database where colomn date is a numeric value and not a Date value.
Yes, I know I can change this with mouseclick, but all the apps using this database were made by one of my predecessors (and everyone after him just ignored it and built). So if I change it to Date, many af applications will fail. :(
Okay, I'm reviewing this database from one specific date to the next. I tried using a dropdown, but as you can tell, having a thousand options in one list is awfully awkward, even ugly.
I have small input fields for day-month-year, but waltz comes on a numeric date in the database. I would need to somehow calculate the date to a numeric value ...
There should be a simple solution. Right?
I am using ASP (vbscript) for an application, this is for intweb and I have an Access database.
source to share
Access will convert to a number for you, as mentioned, 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:
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
source to share
That's right .. it was so easy. I'll accept Remou's answer, WHERE NumberDate = # 2008/12/7 # does the trick.
But to get closer to this question, here is a solution to my own game 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-2008BECAUSE CDate ('1') gives: 31-12-1899
This made it so that 12/31/1899 is not the day, but the first.: /
Thanks guys!
source to share
Access stores the date internally as a floating point number (number of days since 12/31/1899 or whatever), have you tried using CDate () to convert the number back to a date? Then you can request the use of BETWEEN.
Another possibility is that the date is stored numerically, but not converted (e.g. 31121899), in which case you should be able to get the corresponding date details using Format () or Day () or Month () or Year ().
Hope it helps.
source to share