Syntax of Time value

Results 1 to 3 of 3

Thread: Syntax of Time value

  1. #1
    SQLBOY'sBrother Guest

    Default Syntax of Time value

    Hi,<BR><BR>I have a field for date and another for time in my table. I like the long date with the medium time (access2000) I have them separate. In my query I have...<BR><BR>&#060;=Date() for a date comparison, but I am unsure of what the medium time (has just hour/minutes, no seconds) comparison should look like?<BR><BR>&#060;=Time() ?? This adds the seconds in vbscript... I don&#039;t find a Time constant in the access expresson builder (just date/time together).<BR><BR>Thanks for any ideas...<BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default Understanding OLE date/time values

    &#062; I have a field for date and another for time in my table. <BR>&#062; I like the long date with the medium time (access2000) I have them separate. <BR><BR>Ummm...unless you do a lot of viewing and reporting of the data via Access, that&#039;s a mistake, I think.<BR><BR>From SQL, all formatting you do in Access is lost, so all you are doing by keeping the two fields separate is making things more complicated when you use ASP/ADO/SQL to make queries of the data.<BR><BR>See, in VBS (and, indeed, in OLE/COM in general), there is no such thing as *just* a DATE value or *just* a TIME value. Period. So any Access date or time field is converted to the DATETIME Variant of OLE. And that variant works like this: It is actually a double precision floating point number. The portion of the number left of the decimal point is the number of days since (or before) 1/1/1900. The portion to the right of the decimal point represents the time of day as a *fraction* of 24 hours.<BR><BR>WHAT ACCESS does not tell you: Guess what! It uses the SAME SYSTEM, under the covers! No matter WHAT date or time format you choose for display, you are *actually* storing a double precision (8 bytes) number in the database!<BR><BR>So there ISN&#039;T any special format for "medium time" because there isn&#039;t *REALLY* any medium time datatype! ONLY the display in reports, etc., is affected by your choice of format. <BR><BR>FINALLY:<BR><BR>What does "Time() -- This adds the seconds in VBScript" mean????<BR><BR>It does *NOT* add anything. It just gives you the time of day? Oh, wait! You mean that it doesn&#039;t give you just hours and minutes! Yep. Too bad.<BR><BR>And Time() is just 100% as available in Access as it is in VBScript. And it, too, shows seconds.<BR><BR>Actually, that&#039;s a lie. Time() *actually* is given as a fraction of 24 hours, remember? So it is actually accurate to milliseconds...or at least maybe to a hundredth of a second. It&#039;s just the routines that *display* a date/time value for you ROUND the time to the nearest second. <BR><BR>ANYWAY...<BR><BR>What this means is that you should never count on doing equals or not equals (= or &#060;&#062;) comparisons vs. times specified to the second (or hour or minute). Instead, you should look for a *range* of times... like in<BR>... WHERE myMediumTimeField BETWEEN #1:30 PM# AND #1:31 PM# <BR><BR>Either that, or use the Hour and Minute functions and compare with them:<BR><BR>... WHERE Hour(myTimeField) = 1 AND Minute(myTimeField) = 30<BR><BR>But, really, would you ever do that??? Ah, well...your choice.<BR><BR>

  3. #3
    SQLBOY'sBrother Guest

    Default Thanks Bill

    Thanks very much Bill...<BR><BR>I will use the general date in access and use some date manipulation in vbscript in my asp page to make it look how I want...time to reread my sams asp 3.o using dates section...<BR><BR>Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts