Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    40

    Unanswered: SQL working with Date field

    After recently converting my Acess DB to SQL Server 2000, I am having and issue with an SQL statement that works fine in Access but no long works when trying to execute against the SQL Server 2000 db.

    The SQL Statement in Access was:

    strSQL = "SELECT * FROM tbl_Events where StartDate >= date() Order By StartDate"

    Basically I want to show all events with a StartDate after todays date. So when an event has already happened it doesn't show up on the events page.

    If I plug in a regular date like 2/6/2004 it will run but am unable to get the correct function for todays date.

    Any help would be greatly appreciated.

    Thanks,

  2. #2
    Join Date
    Nov 2003
    Posts
    76
    you can pass curdate like this

    strSQL = "SELECT * FROM tbl_Events where StartDate >= " & date() & " Order By StartDate"

    or let sqlserver to get the curdate like this

    strSQL = "SELECT * FROM tbl_Events where StartDate >= curdate() Order By StartDate"

    HTH

  3. #3
    Join Date
    Jan 2004
    Posts
    40
    With this:

    strSQL = "SELECT * FROM tbl_Events where StartDate >= " & date() & " Order By StartDate"

    The query returned all events in the table and not just the ones going forward.

    With this:

    strSQL = "SELECT * FROM tbl_Events where StartDate >= curdate() Order By StartDate"

    I got his error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
    [Microsoft][ODBC SQL Server Driver][SQL Server]'curdate' is not a recognized function name.

    ../../Charity/Events/Events_Test.asp, line 116


    Any ideas?

  4. #4
    Join Date
    Nov 2003
    Posts
    76
    sorry my mistake on the 2nd one.


    strSQL = "SELECT * FROM tbl_Events where StartDate >= getdate() Order By StartDate"

  5. #5
    Join Date
    Jan 2004
    Posts
    40

    Talking

    Thanks so much, looks like that one worked..

  6. #6
    Join Date
    Dec 2003
    Posts
    454
    GETDATE ( ) returns the current system date and time in the MS SQL Serve standard internal format for datetime values.

    That means SELECT GETDATE ( ) -- returns 2004-02-06 22:04:16.670

    Based on your post, you only need today's date without time. So you may use this:

    CAST(GETDATE() AS Char(11)) or CONVERT(Char(11), GETDATE())

    which returns today's date.

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    Be careful with datetime function manipulations. Using cast/convert functions are not necessary in this example since you are doing a comparison - unless you want to display the datetime in a particular format which you would place in your select clause and not the where clause. If you do, then use the style parameter of the convert function. Be as abstract as possible.
    If you do not have SQL Server Books Online (BOL) installed - please do so. The majority of questions asked in the SQL Server forum could be eliminated if people had access to bol. BOL

Posting Permissions

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