Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Datetime field in SQL Server (XP/03)

    Does anyone have experience upsizing Access to SQL Server? The datetime fields in Access get an overflow error when trying to append them to the SQL Server table. The dates in the Access table are mm/dd/yyyy, no time is included in the field. SQL seems to want a time included. Is there a simple way to get the date from Access to SQL Server 2005?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Datetime field in SQL Server (XP/03)

    If the dates in Access are truly date fields, then they also include a time by default. You may format them as mm/dd/yyyy but there is still a time component, which equates to midnight. SQL Server uses a different kind of datestamp field but you should be able to append dates to it from Access. How are you moving the data from Access to SQL Server?
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Datetime field in SQL Server (XP/03)

    I have tried using an append query, but get an overflow error. So I appended all fields except the date field and tried using an update query with the same results.

    I also tried changing the datetime field in SQL Server table to smalldate, and that too resulted in the same error.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Datetime field in SQL Server (XP/03)

    Smalldate is the equivalent of the Access date field, but what do you mean by an append query? Are you linking to the SQL database from Access and trying to append, using DTS, or what? Are you sure it's the data field that's giving you the overflow, and are you sure you don't have any nulls in the Access date field?
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Datetime field in SQL Server (XP/03)

    Charlotte,

    Thanks for following up. I tried several ways of getting the data into the SQL table. I tried append query, with and without the date field. Without the second date field it works with no errors. After appending all fields except the date field, I tried using an update query to get the date into the field. That also fails with an overflow error. There are two date fields in the Access table and the equivalent date field in the SQL table. Attached is a spreadsheet showing a comparison of the two tables and the two date fields (SQL version and Access version). I should mention that the first date field was successfully appended/updated but not the second (DateShipped).

    A sample record for the first date field in Access is "2/10/2005 8:30:15 PM" and is populated automatically with the NOW() function. A sample record for the second date field in Access is "10/5/2006" and is populated by a date entered by the user via a form.

    BTW - I even tried an update query for the second field using "Format([tblTransactionDates]![DateShipped],"mm/dd/yyyy hh:nn:ss ampm")" for only records where the "DateShipped" field is a valid date as the field/source, with same results.

    Does this help?

    Thanks!!

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Datetime field in SQL Server (XP/03)

    Since both fields are date/time in Access, there must be a problem with the data in the second field. SQL Server sometimes objects to nulls, so what you may need to do is filter the update to only attempt to update fields where the DateShipped is not null in the Access table. If that works, you will have located the source of the trouble and you'll know how to work around it in the future.
    Charlotte

  7. #7
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Datetime field in SQL Server (XP/03)

    Charlotte,

    Thanks. That is what I was trying to say in previous post. I "filtered" the data with only records that contained valid dates in the DateShipped field in the Access table. The only difference between the data in the two date fields in Access is that the DateShipped field records look like "10/10/2006" and the TimeStamp field in Access looks like "11/2/2005 7:35:20 AM". Notice that the second data set stores the time along with the date. The former only includes the date.

    Am I missing something?

    Thanks!

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Datetime field in SQL Server (XP/03)

    The upsize may make your Access datetime fields as smalldatetime in SQL Server. You need to change these to datetime fields. It seems to me the problem was whether/not there was a time component. If there was, the smalldatetime caused the error you found.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Datetime field in SQL Server (XP/03)

    Mark,

    The upsizing wizard did in fact set the fields as datetime. After a several attempts, I changed the type to smalldatetime. That too resulted in the same "overflow" error. I used the Access documenter to create the spreadsheet comparing the two fields in the two tables. I can't see any difference between the four fields (2 fields each in two tables).

    Does that make any sense, or did I just muddy the water more?

    For your convenience, I have attached an mdb with the table.

    thanks

  10. #10
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Datetime field in SQL Server (XP/03)

    Mark,

    I have attached an mdb with the "offending" Access table.

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Datetime field in SQL Server (XP/03)

    TransactionID 6298 has invalid date data that is probably causing the problem. The date is 10/1/112.

    TransactionID 6298

    TimeStamp 10/17/2006 10:46:56 AM

    DateShipped 10/1/112
    Regards,

    Gary
    (It's been a while!)

  12. #12
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Datetime field in SQL Server (XP/03)

    WOW! THANKS! I was making it a lot harder than it needed to be. I sorted the data ascending and descending before I ran the query just so I could see at a glance the dates were valid. Obviously my glance needs corrective lens!

    Thanks Gary!

Posting Permissions

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