Results 1 to 3 of 3

Thread: How to compare an Access date field to a SQL Server date field

  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    225

    How to compare an Access date field to a SQL Server date field

    I'm converting a bunch of tables from Access to SQL Server and am required to run a script to ensure all the converted data converts properly. So, I need to compare a date field in Access to the corresponding date field in SQL Server. Although both are defined as datetime fields, I know that they are stored differently. Therefore, every compare of one to the other is always "not equal". What is an easy way to compare only the mm/dd/yyyy part of the two dates and get an "equal" when this part of the dates match.

    Thanks, Eddie

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    24,372
    Maybe use Format function on both of the fields and compare the formatted value. The Format function results in a string value.

    Format([Access date field],"mm/dd/yyyy") = Format([SQL date field],"mm/dd/yyyy")
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    225
    June7,

    Thanks... that worked.

    Eddie

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Date Field Check against Saved Date?
    By Heatshiver in forum Programming
    Replies: 8
    Last Post: 03-26-2012, 10:01 PM
  2. Getting Just the Date part of Date/Time field
    By GaryElwood in forum Reports
    Replies: 7
    Last Post: 09-28-2011, 08:58 AM
  3. Replies: 1
    Last Post: 02-06-2011, 04:36 PM
  4. Set date field on subform to date variable
    By laavista in forum Access
    Replies: 4
    Last Post: 06-30-2010, 05:32 PM
  5. set date field on form to date variable
    By laavista in forum Access
    Replies: 3
    Last Post: 06-28-2010, 02:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums