HI,
I am new here and need small help, can i add hour minutes or days to a datetime using a user function in access, i am middle of calculation on input table of datetime format in a user function and need to set any datetime to sunday 22:30:00 if a condition becomes true, can i do this directly like " set hour(dtin) = 22 and minutes(dtin) = 30 and day(dtin)= day(dtin)+1" or i have to use datediff and dateadd function.
If this is from your other thread, I *thought* you were trying to calculate the end time for a ticket.?
So you would calculate and store the end time for a ticket allowing for the period Friday to Sunday.? Then if you stored the date time the ticket was closed, you would be able to see those within SLA and those not.?
I'm going to give you a quick-and-dirty tutorial on Access date variables. They are actually date/time variables. By convention, a Date variable is a DOUBLE (scientific long precision) number of days and fractions of a day from a given reference point, which for Access is at midnight of 31-Dec-1899 as day 1. The formatting routines that involve dates are able to convert Date variables into a string such as "12/31/1899 00:00" or other formats as appropriate.
If I recall correctly, the current date is bigger than 32,768 but less than 65,536, so that gives you an idea of the current number range. For a DOUBLE, that means the integer part is 17 bits. Since you get 64 bits in a double and the mantissa is over 50 bits, you have at least 33 bits of fractional data in that double. A day is 86,400 seconds, which is between 64k and 128k, or fits in 18 bits. So that means the fraction of the day is accurate to the second EASILY. The whole date/time combo takes 36 bits and you have more than that available to you.
The way those numbers are formatted, MIDNIGHT is fraction 0 and NOON is fraction 0.5, with other times from 0 to .4999...999 for AM and 0.5 to 0.9999...999 for PM. When a date-only string is converted, it converts to the day number corresponding to the date using the reference date as the starting point and uses 0.0 (midnight) as the fractional part. So the comparison of two dates, both without times, implies you are computing from midnight to midnight of both dates. If the date variables have fractions that are NOT 0, then you are computing the exact elapsed time from a specific date and time to another specific date and time.
When you take the difference between two dates, the result is a time difference. When you ADD two variables that were dates, you have performed an improper operation since both of those dates were represented as differences from the reference time. BUT if you add a date and something that is merely a number of days and fractions, THAT works.
So if you want to make some date have a specific time, you have to do something that looks like this:
This truncates the date you wanted back to midnight, then adds back the desired time of day as a fraction. The above discussion might explain to you why it is such a tough thing to compute working hours when what you actually have is wall-clock times. You have 16 hours of each day that are nominally NOT working hours as part of every computed difference.
Note also that the standard formatting routines will have trouble converting any elapsed times that total more than 24 hours, because those will look like dates from January of 1900. This forum has a lot of discussions involving conversion of time differences that require you to "roll your own" formatting to meet your needs.
