Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

And for my Final MSSQL2K Question (date comparison)

Options
  • 08-04-2004 3:17pm
    #1
    McGintyMcGoo
    Closed Accounts Posts: 333 ✭✭


    I am working on an application that allows a user to enter 2 dates in dd/mm/yyyy format. These dates will be used to retrieve records that were created within those dates. It was easy enough to do in MS Access by creating an SQL string to ...

    SELECT * FROM TableName WHERE (creation_date >= #4/2/2004# AND creation_date <= #8/2/2004#)

    The datatype used in MSSQL2K is datetime and here is a sample record...
    2004-04-08 15:01:14.727

    How do I compare the users dd/mm/yyyy date to the above sample?

    Cheers (again!?)


Comments

  • Options
    #2
    McGintyMcGoo
    Closed Accounts Posts: 333 ✭✭McGintyMcGoo


    I have tried code like...

    SELECT * FROM TableName
    WHERE creation_date BETWEEN 04/02/2004 AND 08/02/2004

    as per this post but it does not work.

    Any ideas????????


  • Options
    #3
    geezup
    Closed Accounts Posts: 71 ✭✭geezup


    have u tried...

    SELECT * FROM TableName WHERE (creation_date >= '4/2/2004' AND creation_date <= '8/2/2004')


    lewt me know if it works... cant test it right now, but from my good old programmer days i guess it was like that :)


  • Options
    #4
    amen
    Registered Users Posts: 2,781 ✭✭✭amen


    you have to be carefull with dates in MS SQL.
    if you compare between 04/02/004 and 08/02/2004
    what you are really comparing is
    04/02/2004 12:00:00am and 08/02/2004 12:00:00am
    i.e the 08/02/2004 day is not included (some would say its better to think
    of it as 07/02/2004 11:59:59PM)

    geezups sql looks fine

    you should check what the default language/date is on your sql server box
    passing down the date in dd/mm/yyyy with sql running in mm/dd/yyyy means you may have to use the convert statement to convert your input dates to the correct format
    have a look at Convert in SQL help


Advertisement