Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Unanswered: SQL vs Access Date Query Format

    I am new to SQL server, and am learning as I move an Access db to MSDE2000A. With Access db I run several different queries from a VB6 application in the basic format:

    SELECT testdb.*
    FROM testdb
    WHERE testdb.datefield = #1/01/2004#

    When working with the MDSE version of the db, problem is with the "#" delimiter of dates. MSDE is giving a bad query error. If I change the format to: datefield = '1/01/2004' , the query works on MSDE

    However, using SQL builder in VB Design Environment I can run the query in either format and get a result.

    Any suggests what I am missing? Thanks.

  2. #2
    Join Date
    May 2002
    Location
    Sydney
    Posts
    45

    Re: SQL vs Access Date Query Format

    JD576,

    Dates are VERY different between Access and SQL Server.
    The # symbol won't work in Sql server, so forget that.

    The datetime field in SQL Server captures time as well as date information too, so with your queries watch out for this, as the query below will only match those dates which are '1/01/2004 00:00:00'


    SELECT testdb.*
    FROM testdb
    WHERE testdb.datefield = '1/01/2004'


    Its easiest to use a date range using the function BEWTEEN or you can be clever and convert to integers using floor function as described in several help docs, but keeping it simple is usually the best way.

    Jim

  3. #3
    Join Date
    Sep 2003
    Posts
    12
    Thanks. I am starting to see the light.

    I finally found a good article on the subject, too (which I will cite for anyone else needing similar help):

    How to Search for Date and Time Values Using Microsoft SQL
    http://www.devx.com/vb2themax/Article/19902/0/page/2

    Reading all day, I figured out the 'date' vs #date# difference (and a bit more, too), but could not figure out why my test sql queries run from the VB data environment using the Access syntax were returning valid data sets from the sql server, while the vb code was not (I quess the VB data environment must be doing a sql query translation when it calls jet vs sql.)

Posting Permissions

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