none
Use of Timevalue on datetime field RRS feed

  • Question

  • The solution is working but I was hoping to receive a comment over my last observation:

    https://social.msdn.microsoft.com/Forums/en-US/8f7f6202-04a3-475c-8a20-328ea1f0057f/vba-syntax-for-sql-or-dlookup-to-restrict-on-time-component-of-the-datetime-field?forum=accessdev


    And that is the following:

    1) Initially, in Access Back End, the time values were stored without date

    DataType - Date/Time  - Format - Medium Time - Value - 7:00 AM

    2) Post SSMA for Access Migration, in the SQL Server Database it is now

    DataTpe - datetime    Value - 1899-12-30 07:00:00.000

    3) The VBA Code originally with DLookup would no longer be work.

    4) There was a suggestion in this forum to use the TimeValue in SQL to only use the Syntax

    5) There a summary in another forum how another developer stores their time values in Decimal places

    6) Thanks to the tips on this forum, I did some testing on everyline of code, and could only get the code to work with TimeValue

    Eg. This code DOES NOT work connected SQL Server Database

            RateMethodID = DLookup("[RateMethodID]", "[Rate Method]", "#" & Format([Forms]![Dashboard Details]![Start Time], "h:nn AM/PM") & "#" & _
                           " BETWEEN [Start Time Rate] AND [Finish Time Rate]" & _
                           " AND [Rate Category] = 1 " & _
                           " AND [Rate Day] LIKE " & "'*" & WeekdayName(Weekday([Forms]![Dashboard Details]![Resolved Date], 0), 0) & "*'")

    Eg. This Code DOES work connected to the SQL Server Database.

        

            'SQL Server Table to use TimeValue
            RateMethodID = DLookup( _
                "[RateMethodID]", _
                "[Rate Method]", _
                "#" & TimeValue([Forms]![Dashboard Details]![Start Time]) & "# " & _
                "BETWEEN TimeValue([Start Time Rate]) AND TimeValue([Finish Time Rate]) " & _
                "AND [Rate Category] = 1 " & _
                "AND [Rate Day] LIKE '*" & WeekdayName(Weekday([Forms]![Dashboard Details]![Resolved Date], 0), 0) & "*'")

    I just wanted to relay this observation if anyone had any similar experience in using TimeValue in this manner

    Thank you.



    Thank you in Advance

    Friday, July 27, 2018 2:18 AM

Answers

  • >> 1) Initially, in Access Back End, the time values were stored without date

    Actually, Access ALWAYS stores a date value (but hides for 0 date).

    This access code shows what a data value of zero looks like:

      Dim dtStart      As Date

      

       dtStart = 0

       Debug.Print dtStart      output:12:00:00 AM

       Debug.Print dtStart + 1 output:1899-12-31

       Debug.Print dtStart - 1  output:1899-12-29

       Debug.Print Format(dtStart, "mm/dd/yyyy")  output: 12-30-1899

    Note the LAST one above. If I use a format command, notice how access spits out a date!!!

    This is a LEGAL date in Access. So YES you ARE storing a date of 1899-12-30

    All access dates are actually a decimal number, with the part before the decimal as a integer that represents the date (with 0 starting at 1899-12-30, and then the “fraction” part is the time part of 24 hours past midnight.

    >> 5) There a summary in another forum how another developer stores their time values in Decimal places

    Not sure of the discussion, but in “theory”, access ALWAYS stores a date value, and this is in fact a decimal value.

    You can convert a date to decimal with cdec(), and you see the integer/fraction parts spit out by access.

    Eg:

    ? cdec(#12:00:00#)

     0.5  (half a day)

    And in above, the “0” part would be considered a date of 1899-12-30

    So access ALSO uses a decimal number “behind the scene”, but you really never have to care.

    To be REALLY honest?

    If you were doing a query with a date/time value and time of day, your dlookup would work.

    However, when using a date/time column, you are 100% correct that you have to use timevalue() WHEN the date part is 0.

    This is ONLY when you storing a “0” date value.

    To be “really” honest here?

    I would say this is a “bug” in access.

    I just fired up the sql profiler, and access strips out the 1899 date part (it should not).

    If you by accident (or by intention) pass what access “thinks” is a zero date value, then it is stripped out of the sql that access sends to the server. This means that the query passed to sql server fails because access “leaves out” the date part (it should not!).

    This issue EVEN occurs when we use the native 11 drivers.

    So when one wants no date values, sql server + access requires you to use timevalue().

    Why so few posts and issues?

    Well, most work with dates. Or most work with dates + time. And in these cases code “similar” to yours will work.

    You  ONLY get bitten when date value = 0.

    I am willing to state this is a bug in Access. Access will strip out the date part of the query send to SQL server if it is a “zero” date. Access thus only sends the timepart to sql server.

    This means even a simple query like this will fail:

    Select * from tblTimes where EndTime > #5 pm#

    The above actually fails for any case with date part = 0.

    I don’t see a way around this issue.

    And to be really open here, I am 100% wrong that your code should work – it clearly as you point out does not!

    I can only say this is a eye opening post + issue for me.

    Access will “display” only the time value (just like when not using sql back end). However any sql access sends to sql server “needs” to include the hidden date part, and Access/odbc driver strips out this part. As a result, you REALLY do have to use timevalue() in this case.

    In effect, I consider this a bug.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Friday, July 27, 2018 7:30 AM

All replies

  • >> 1) Initially, in Access Back End, the time values were stored without date

    Actually, Access ALWAYS stores a date value (but hides for 0 date).

    This access code shows what a data value of zero looks like:

      Dim dtStart      As Date

      

       dtStart = 0

       Debug.Print dtStart      output:12:00:00 AM

       Debug.Print dtStart + 1 output:1899-12-31

       Debug.Print dtStart - 1  output:1899-12-29

       Debug.Print Format(dtStart, "mm/dd/yyyy")  output: 12-30-1899

    Note the LAST one above. If I use a format command, notice how access spits out a date!!!

    This is a LEGAL date in Access. So YES you ARE storing a date of 1899-12-30

    All access dates are actually a decimal number, with the part before the decimal as a integer that represents the date (with 0 starting at 1899-12-30, and then the “fraction” part is the time part of 24 hours past midnight.

    >> 5) There a summary in another forum how another developer stores their time values in Decimal places

    Not sure of the discussion, but in “theory”, access ALWAYS stores a date value, and this is in fact a decimal value.

    You can convert a date to decimal with cdec(), and you see the integer/fraction parts spit out by access.

    Eg:

    ? cdec(#12:00:00#)

     0.5  (half a day)

    And in above, the “0” part would be considered a date of 1899-12-30

    So access ALSO uses a decimal number “behind the scene”, but you really never have to care.

    To be REALLY honest?

    If you were doing a query with a date/time value and time of day, your dlookup would work.

    However, when using a date/time column, you are 100% correct that you have to use timevalue() WHEN the date part is 0.

    This is ONLY when you storing a “0” date value.

    To be “really” honest here?

    I would say this is a “bug” in access.

    I just fired up the sql profiler, and access strips out the 1899 date part (it should not).

    If you by accident (or by intention) pass what access “thinks” is a zero date value, then it is stripped out of the sql that access sends to the server. This means that the query passed to sql server fails because access “leaves out” the date part (it should not!).

    This issue EVEN occurs when we use the native 11 drivers.

    So when one wants no date values, sql server + access requires you to use timevalue().

    Why so few posts and issues?

    Well, most work with dates. Or most work with dates + time. And in these cases code “similar” to yours will work.

    You  ONLY get bitten when date value = 0.

    I am willing to state this is a bug in Access. Access will strip out the date part of the query send to SQL server if it is a “zero” date. Access thus only sends the timepart to sql server.

    This means even a simple query like this will fail:

    Select * from tblTimes where EndTime > #5 pm#

    The above actually fails for any case with date part = 0.

    I don’t see a way around this issue.

    And to be really open here, I am 100% wrong that your code should work – it clearly as you point out does not!

    I can only say this is a eye opening post + issue for me.

    Access will “display” only the time value (just like when not using sql back end). However any sql access sends to sql server “needs” to include the hidden date part, and Access/odbc driver strips out this part. As a result, you REALLY do have to use timevalue() in this case.

    In effect, I consider this a bug.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Friday, July 27, 2018 7:30 AM
  • Thank you kindly Albert for your detailed analysis!

    Thank you in Advance

    Friday, July 27, 2018 9:24 AM
  • It is VERY interesting this issue. What this means is that with Access to sql server, you can NOT query a date of 12-30-1899 on sql server!

    While dates before and after are perfect legal. And such a date is also legal in Access back end, you cannot have that as a date on SQL server!

    So if you build a art collection system, and something happens to be made in 12-30-1899, your database system will not work when using SQL server!

    The only workaround is to use a pass-though query - that will work, and if you include that zero date time in the PT query (12-30-1899 and time part), then such a query will work with sql server.

    So a passthough (PT) query would also fix this issue. But one simply has to conclude that to query such information, Access is not able to do this. In fact, it really hard to query such data (you could use the date before and after zero date as a range from the access sql, and that also would work!!!).

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Friday, July 27, 2018 7:56 PM
  • Thank you Albert, 

    Is it worth raising this with Access Support, to rectify the problem... not particularly the 1899 specific you detail, but rather from your last post

    https://access.uservoice.com/

    Thank you most kindly for your input.

    Simon


    Thank you in Advance

    Saturday, July 28, 2018 12:25 AM
  • It also dawned upon me....

    Was this the Y1k bug that they had to resolve at the end of the century on last day of 1899, just like we had the Y2k bug to resolve on last day of 1999... had to say it!



    Thank you in Advance

    Saturday, July 28, 2018 10:16 AM