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

    Another SQL question (2k)

    We recently upsized an Access database to SQL Server. It was not as bad as I thought it would be, and all the forms and reports and queries, etc. work. However, there is one issue that remains unresolved and I respectfully request guidance on how to address it. In access we stored the date and time in separate fields. When we upsized we tried to do the same. We are using ASP to get the data into the SQL tables, but have not been able to store a time. It keeps stating that the data is in the wrong format. We have tried 24 hour format (13:00) as well as AM/PM (1:00 PM), with no luck. Is there an easy solution. What am I overlooking?

    Thanks!

    Ken

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Another SQL question (2k)

    SQL Server stores dates in a different internal format than Access, and as a result you run into these kind of issues. You should be able to store dates without a time - it assumes you mean midnight (if you put the data into a date/time column in SQL Server). The time however is a challenge. Is there a specific reason you need to separate the time from the date?
    Wendell

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

    Re: Another SQL question (2k)

    If the time itself is stored in Access without a date, and you use a datetime field, you're really storing the time on the default date. When we need a standalone time, say for the start of a shift, we store it in HH:NN format as a string. Then some simple date math can be used to add the time to a particular day when we need a full date time for calculations. We use that in both Access and SQL Server.
    Charlotte

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

    Re: Another SQL question (2k)

    Wendell,

    Thanks for the followup. The reason for storing the date and time separately is the customer has become conditioned (or accustomed) to them being separate. They really think in terms of a single field storing both date and time. Some of the time fields do not necessarily relate to a specific date and therefore would have a time but no date. Does that make sense? For now we are looking at switching the time fields from datetime to strings.

    SQL has more challenges and caveats then I had expected.

    Thanks for ideas.

    Ken

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

    Re: Another SQL question (2k)

    Charlotte,

    That sounds like a workable plan! Thanks for all your help.

    Ken

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Another SQL question (2k)

    Have you by chance made the time field a timestamp type? That would cause trouble no doubt - it's a special kind of field for providing time synchronization and not really a time field at all. It's been some time since I looked at the way SQL Server handles dates, but I believe it shifts the position of the implicit decimal point one hex digit to the right, so that time is stored with a bit less precision than in Access and thus SQL Server can handle a few more years than Access. So it seems to me you should be able to upsize time fields stored in Access date/time format to a SQL Server table with a date/time field, and unless you need precision to the millisecond level, it should work.

    FYI, Access will accept valid dates from January 1, 100 AD to December 31, 9999 AD. SQL Server will accept valid dates from January 1, 1753 AD to December 31, 9999 AD. Thus you can run into problems upsizing date/time fields to SQL because of that issue.
    Wendell

Posting Permissions

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