Questions about Date/Time (1 Viewer)

[robcrussiii]

This field is really confusing me. I have a couple questions. Please correct me if I am wrong. Access stores date and time information in a 8 digit 'floating' number with a decimal. Left side of decimal represtents days past some wierd 1880 date, and right side represents the time portion, as a fraction of a day?

If this is correct, could someone try to answer these questions:

1) If you just entered just a date into a field set up as date/time, will it just store the date portion? So it would be something like 3289.0000 for a date with no time entry and 0000.3464 for just a time entry?

2) If the above is true, could I take the [Date],[Time] fields and add them together to get a combined date/time? Even better, could I make the 2 objects in access (1 cbo[Date]using a calendar picker, 1 lstbox[Time]entered by user) automatically combine thier values and store it in a date/time field in my main table?

Thanks for any replies, Im trying to get a grasp on doing calculations against date/time and its getting a little over my head.

 

[WayneRyan]

Rob,

To be honest, I don't care how Access stores dates. I don't think
that it's floating point, I think its a very large integer that
represents the number of 1/1000th of a second from some date. You
can treat them as numbers if you want, but I don't see why.

Access has great Date functions like DateDiff, DateAdd, etc. You
can compare and alter them easily.

You can construct them by using functions like CDate:

CDate(Format(SomeShortDate, "3/8/2005") & " 10:24:00 PM")

You could substitute - Forms![SomeForm]![SomeField] for the " 10:24:00 PM"
or the date component.

Anyway, I just them as dates. I guess someone will enlighten us
as to how they're stored, but it probably won't change my usage
of them.

Just my 2 cents ...

Wayne

 

[robcrussiii]

In the current db Im working on I needed to work a expression on 4 fields: datein, timein, dateout, timeout to figure out if they were gone... I had a confusing mess of a query.
I really just needed to know if I could add the datein/timein fields without it screwing up the actual time they signed out at. I found out you could, and now my query went from:

IsOut: IIf([TODAY]-[DATEOUT]>=0,IIf([TIME]-[TIMEOUT]>=0,IIf([TODAY]-[DATEIN]<=1,IIf([TIME]-[TIMEIN]<=1,-1,0),0),0),0)

to:

Isout: IIf(Nz([combinein],Now())-Now()>=0,-1,0)

I am much relieved! Plus I learned alot searching around for this info!

 

[The_Doc_Man]

Sorry Wayne, a DATE field actually is a DOUBLE reinterpreted as days and fractions of a day since 1-Jan-1900 (If I recall correctly - that's the Windows date ref.) The funky 8-byte integer to which you referred is a Currency field. Date, Currency and Yes/No fields are all cases of more traditional data types (DOUBLE, OCTAWORD, BYTE respectively) being RE-INTERPRETED (cast) into a display format other than their native formats.

Rob, if you have the integer number of days converted to a double, and if you have the fraction of a day as a double, you can indeed add them to get a date/time combo. You can surely compare date/time info directly to get such relationships as > (later than) or < (earlier than) and the other possible comparative operators. If you try to project a time from a starting time, you can surely convert the projected amount to days/fractions, add it to your starting time, and reconvert to a traditional date/time for display purposes.

However, certain other actions are best performed by calling Access functions specifically designed for date manipulation. Therefore, you should familiarize yourself with the Datexxx functions such as DateAdd, DateDiff, DatePart, and the Format function with user-defined date strings, all of which are in the help files. If your number to be converted via Format is actually a date/time field, Format works fine. If it is days and fractions for a duration (not an absolute time), you will have conversion problems.

One reason to use the Datexxx functions is that the alternative (directly manipulating a raw DOUBLE date variable) is legal but confusing to other readers. I.e. future maintainers. Doing what amounts to REAL-NUMBER math on a date will just look wrong.

There is a second reason. The DOUBLE representation can be manipulated in lots of ways, but since it is a REPRESENTATION cast onto a different data type, you can run into some anomalies. Like, depending on exactly how you obtained the time of day, you might have things that, when you compare the times, cause them to be unequal even though they would DISPLAY as the same time. Because one time might be 08:51:31 on the dot and another would be 08:51:31.00021, which is not the same time, but for most of us, it would be considered close enough. The "projected completion time" case I mentioned earlier would be an example of how you could get such strange fractional seconds tacked on to your date/time field.

By converting the REPRESENTATION to a string format and comparing the strings, or by taking the DateDiff in specific units (such as seconds), you cause Access to convert to unequivocal units before you do the comparison. And that avoids the potential anomalies nicely.

 

[Pat Hartman]

since 1-Jan-1900

The origin date is actually 12/30/1899. 12/29/1899 is -1 12/28/1899 is -2, 12/31/1899 is 1, 1/1/1900 is 2, etc.

Date() returns the current date and Now() returns the current date plus the current time. Today() is not recoginzed in Access VBA.

 

[tawkkal]

i also have doubt regarding time...

i have file which contains data with time.. which is calling time.. now when i want to apply filter in query using > 10:30 AM or 10:30 i dont get the output correct.. many times the out put is no records.. can u help me...

 

[The_Doc_Man]

The times have to be in the right format. Look at DatePart to assure that you are comparing times correctly. I.e. in a valid format.