Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

And for my Final MSSQL2K Question (date comparison)

  • 08-04-2004 03: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

  • #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????????


  • #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 :)


  • #4
    amen
    Registered Users, Registered Users 2 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