Results 1 to 3 of 3
  1. #1
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313

    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 offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    EddieN1 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2011
    Posts
    313
    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, 11:01 PM
  2. Getting Just the Date part of Date/Time field
    By GaryElwood in forum Reports
    Replies: 7
    Last Post: 09-28-2011, 09:58 AM
  3. Replies: 1
    Last Post: 02-06-2011, 06:36 PM
  4. Set date field on subform to date variable
    By laavista in forum Access
    Replies: 4
    Last Post: 06-30-2010, 06:32 PM
  5. set date field on form to date variable
    By laavista in forum Access
    Replies: 3
    Last Post: 06-28-2010, 03: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
  •  
Other Forums: Microsoft Office Forums