none
Date incorrectly stored as 1899-12-30 in SQL Server

    Вопрос

  • Hello

    I have Access front-end and the backend database is SQL Server.  When trying to store a date from my Access form into a sql server linked table, it is stored as 1899-12-30. My code is:

       Dim updaterec As String
       InvoiceDate = Date
             
       updaterec = "update dbo_invoice"
       updaterec = updaterec & " set invoicemaileddate = " & InvoiceDate
       updaterec = updaterec & " where invoiceid = " & varInvoiceID
      
       DoCmd.RunSQL updaterec

    What am I doing incorrectly?  Your help is very much appreciated.

    smsmail

        
      

    23 апреля 2012 г. 14:28

Ответы

  •    updaterec = "update dbo_invoice"
       updaterec = updaterec & " set invoicemaileddate = #" & InvoiceDate & "#"
       updaterec = updaterec & " where invoiceid = " & varInvoiceID

    Dates need octothorpes (#) surrounding them. Otherwise they are looked at as a mathimatical experssion such as 4 divided by 23 divided by 2012.

    And just a bit of advice... Use CurrentDB.Execute updaterec, dbFailOnError + dbSeeChanges  rather than DoCmd.RunSQL


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Помечено в качестве ответа smsemail 23 апреля 2012 г. 16:40
    23 апреля 2012 г. 16:27

Все ответы

  • You date is really just the time of day without any date.  Either you need to send the date along with the hours to the server or change the field in the server to Time instead of Date.

    The standard microsoft time/date number formatt is as follows

    1) Jan 1, 1900 is day = 1.  Dec 30, 1899 is day = 0

    2) Each day = 1 so Jan 2, 1900 is = 2

    3) One Hour = 1/24, Six hours = 6/24 = 1/4.  So Jan 1, 1900 at 6:00AM = 1.25


    jdweng

    23 апреля 2012 г. 14:42
  •    updaterec = "update dbo_invoice"
       updaterec = updaterec & " set invoicemaileddate = #" & InvoiceDate & "#"
       updaterec = updaterec & " where invoiceid = " & varInvoiceID

    Dates need octothorpes (#) surrounding them. Otherwise they are looked at as a mathimatical experssion such as 4 divided by 23 divided by 2012.

    And just a bit of advice... Use CurrentDB.Execute updaterec, dbFailOnError + dbSeeChanges  rather than DoCmd.RunSQL


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Помечено в качестве ответа smsemail 23 апреля 2012 г. 16:40
    23 апреля 2012 г. 16:27
  •    updaterec = "update dbo_invoice"
       updaterec = updaterec & " set invoicemaileddate = #" & InvoiceDate & "#"
       updaterec = updaterec & " where invoiceid = " & varInvoiceID

    And just a bit of advice... Use CurrentDB.Execute updaterec, dbFailOnError + dbSeeChanges  rather than DoCmd.RunSQL


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Hi Bill,

    Can you tell us why you advise against using DoCmd.RunSQL?

    I'm sure the advice is good, curious about the why.


    Bob Fitz BizSoftware

    23 апреля 2012 г. 16:36
  • Just for info,  the First of January 1900 is day number 2; and the Second of January 1900, day number 3. It is the 31st of December 1899 which is day number 1.

    ? CDbl( CDate("1 Jan 1900 6AM"))
     2.25

    23 апреля 2012 г. 17:24
  • Now this is strange.  In an excel workbook if you put Jan 1, 1900 into a worksheet function the results is 1, and if you put the same date into VBA code the answer is 2.

    jdweng

    23 апреля 2012 г. 17:50
  • Now this is strange.  In an excel workbook if you put Jan 1, 1900 into a worksheet function the results is 1, and if you put the same date into VBA code the answer is 2.

    jdweng


    Rumour has it that the original Access product developers misunderstood the algorithm which determines which years are leap years and had to adjust the date/time implementation accordingly.  Hence the discrepancy.

    Whether this is true or not I have no idea, but it's a nice story.

    Ken Sheridan, Stafford, England

    23 апреля 2012 г. 18:07
  • http://blogs.msdn.com/b/ericlippert/archive/2003/09/16/eric-s-complete-guide-to-vt-date.aspx

    Extract from above link…
    "And finally, why 30 December 1899? Why not, say, 31 December 1899, or 1 January 1900 as the zero day? Actually, it turns out that this is to work around a bug in Lotus 1-2-3! The details are lost in the mists of time, but apparently Lotus 1-2-3 used this date format but their devs forgot that 1900 was not a leap year. Microsoft fixed this bug by moving day one back one day."


    Chris.


    Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive


    23 апреля 2012 г. 20:38
  • if microsoft fixed the problem, then why does it work one way one in excel spreadsheet and work a different way in excel VBA?

    jdweng

    23 апреля 2012 г. 21:24
  • Probably a good question for the Excel forum.

    However, VBA, in both Excel and Access, behaves the same and can handle negative dates natively. The dates are stored as an IEEE 64 bit number (Double) representing the number of days since day zero which is 30 December 1899. So the stored ‘date’ is not a date at all but rather a duration of time stored as a number of whole days and the fractional part of a day.

    Example, if days stored is 5.25 then that is 5 days and 6 hours which is a duration of time. It only becomes a point in time when added to day zero which is itself a point in time. In other words, a point in time (day zero) plus a duration of time becomes another point in time.

    One caveat here…
    The whole days can be negative whereas the fractional days are always positive.
    So we can end up with the following situation:-
    +0.25 and -0.25 are the same point in time.
    VBA will interpret both as 6 AM (implied on the 30/12/1899) and that is not what we might expect.

    Perhaps it would be best to ask someone on the Excel forum but on an Excel spreadsheet dates are not handled the same way. Excel does not seem to handle negative dates at all and appears to ‘store’ negative dates as text. To handle negative dates in Excel we appear to have to resort to VBA and that then gets us back to common ground, so to speak.

    So Excel seems to have two distinct methods of handling dates, one on the sheet and the other in VBA, and that’s the difference you are looking at.

    Chris.

    Brisbane Australia, GMT+10, Access 2003, Public at SkyDrive

    23 апреля 2012 г. 23:05
  • Hi Bill,

    Can you tell us why you advise against using DoCmd.RunSQL?

    I'm sure the advice is good, curious about the why.


    Bob Fitz BizSoftware

    Bob

    The RunSQL will give the user a warning message to confirm the action. That (among other things) can confuse the user.

     .Execute does not. And you can add a line to return the records affected like this:

        Dim db As DAO>Database
        Dim lngRecsAff As Long
        Set db = CurrentDB
        blah blah
        db.Execute strSQL, dbFailOnError + dbSeeChanges
        lngRecsAffected = db.RecordsAffected


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    • Предложено в качестве ответа Bob Fitz 24 апреля 2012 г. 18:45
    24 апреля 2012 г. 18:05
  • Bob

    The RunSQL will give the user a warning message to confirm the action. That (among other things) can confuse the user.

     .Execute does not. And you can add a line to return the records affected like this:

        Dim db As DAO>Database
        Dim lngRecsAff As Long
        Set db = CurrentDB
        blah blah
        db.Execute strSQL, dbFailOnError + dbSeeChanges
        lngRecsAffected = db.RecordsAffected


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

      Bill

    Thank you for the expanation.  I'm sure I've read this advice before but have wondered about the why.  Now I know.  The bit about adding a line that can return the affected records is something that I didn't know existed.  I shall look into that.  Thank you.


    Bob Fitz BizSoftware

    24 апреля 2012 г. 18:53
  • You're welcome, Bob.

    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    24 апреля 2012 г. 19:03
  • Ken Sheridan wrote:

    Rumour has it that the original Access product developers misunderstood the algorithm which determines which years are leap years and had to adjust the date/time implementation accordingly.  Hence the discrepancy.

    Whether this is true or not I have no idea, but it's a nice story.

    IIRC it was the DOS version of Lotus 123 which had that bug and it has
    been faithfully replicated ever since.

    Ahh, there's even a KB article.   http://support.microsoft.com/kb/214326
    So yes, according to Microsoft, that story is true.  <smile>

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    24 апреля 2012 г. 21:22
  • smsemail wrote:

       Dim updaterec As String
       InvoiceDate = Date

    How is InvoiceDate dimmed?
             

       updaterec = "update dbo_invoice"
       updaterec = updaterec & " set invoicemaileddate = " & InvoiceDate
       updaterec = updaterec & " where invoiceid = " & varInvoiceID

    As pointed out by Bill that should be    updaterec = "update dbo_invoice" & _
       " set invoicemaileddate = #" & InvoiceDate & "#" & _
       updaterec & " where invoiceid = " & varInvoiceID

    Note the use of & _.   I prefer this syntax for declaring long string
    variables as it allows for more text on one line and is cleaner looking.
      

       DoCmd.RunSQL updaterec

    To expand on Bills reply.

    The problem with  DoCmd.RunSQL is that it ignores any errors.  Either of
    the following will display any error messages received by the query.  If
    using DAO, use Currentdb.Execute strSQL,dbfailonerror..  For ADO use
    CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
    adCmdText    You can then remove the docmd.setwarnings lines.

    If you're going to use docmd.setwarnings make very sure you put the True
    statement in any error handling code as well.   Otherwise weird things
    may happen later on especially while you are working on the app.  For
    example you will no longer get the "Do you wish to save your changes"
    message if you close an object.  This may mean that unwanted changes,
    deletions or additions will be saved to your MDB.

    Also performance can be significantly different between the two methods.
    One posting stated currentdb.execute took two seconds while docmd.runsql
    took eight seconds.  As always YMMV.

    Finally if you KNOW that all your users are running MM/DD/YY then this
    date format will work.   But if you have any possibility that users
    might run in dd/mm/yy or yy/mm/dd format then the following standard
    blurb will apply.
    SQL statements require that the dates be either completely unambiguous
    or in mm/dd/yy, or mm/dd/yyyy format.   Otherwise Access/Jet will do
    it's best to interpret the date with unknown results depending on the
    specific date it is working with.  You can't assume that the system you
    are working on is using those date formats.   Thus you should use the
    logic at the following web page.
    Return Dates in US #mm/dd/yyyy# format
    http://www.mvps.org/access/datetime/date0005.htm

    Tony

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    24 апреля 2012 г. 21:29
  • Thanks Bill,  that worked.  Learned something new.

    Question:  You recommended using CurrentDB.Execute instead of DoCmd.RunSQL.  Thanks for the explanation.


    Thanks

    smsmail

    24 апреля 2012 г. 22:04
  • Note that DoCmd.RunSQL allows the use of the syntax  FORMS!formName!ControlName embedded in the SQL statement, while with the CurrentDb.Execute, you need to add concatenations, the right delimiter, use the US format for date, if required, and to handle the possible nulls, in most cases, things that are NOT required using FORMS!formName!ControlName.
    24 апреля 2012 г. 22:53