Results 1 to 15 of 15

Thread: SELECT a date = problem [MS SQL 2000]

  1. #1
    Join Date
    Jul 2004
    Posts
    106

    SELECT a date = problem [MS SQL 2000]

    I am runing this VIEW :

    SELECT users.id FROM users WHERE CONVERT(DATETIME, users.Inscription, 111) = CONVERT(DATETIME, '2006/02/14 12:00:00', 111)

    the datestyle 111 = yy/mm/dd

    I want to compare the dates without the times

    the view returns nothing when they are > 10 rows in the database for 2006/02/14

    how can i search on universal date yy/mm/dd ? or yyyy/mm/dd

    in the datestyle there is nothing for yyyy/mm/dd thats crazy !

    thank you for helping


    date styles : http://www.sqljunkies.com/Article/66...C7FD826E5.scuk

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Try this:

    SELECT users.id FROM users WHERE CONVERT(char(10), users.Inscription, 111) = CONVERT(char(10), '2006/02/14 12:00:00', 111)

  3. #3
    Join Date
    Jul 2004
    Posts
    106
    thank you but iin that way it doesnt work for dates
    if you try > or <= it doesnt work
    anyway even for = or <> it doesnt work

    but it must be a 100% normal way to do that ?
    how can you searh on dates without time converting values yyyy/mm/dd ?

    thank you for helping

  4. #4
    Join Date
    Dec 2004
    Posts
    502
    You'll get better performance (and hopefully better accuracy) if you do this instead:

    SELECT users.id FROM users
    WHERE users.Inscription >= CONVERT(DATETIME, '2006/02/14 12:00:00')
    AND users.Inscription < CONVERT(DATETIME, '2006/02/15 12:00:00')

    Notice that the 'AND' condition uses "less than" instead of "less than or equal to".

    This will give you today's data. It may seem like more of a hassle because of the longer statement, but you'll get better performance if you avoid applying a function (like "CONVERT") directly onto a column.

  5. #5
    Join Date
    Jul 2004
    Posts
    106
    yes of course but really with such a database ... no beeing able to search uin a very simple way on dates

    thank you

  6. #6
    Join Date
    Jul 2004
    Posts
    106
    and using BETWEEN ?


    SELECT users.id FROM users
    WHERE users.Inscription BETWEEN (CONVERT(DATETIME, '2006/02/14 12:00:00')
    AND (CONVERT(DATETIME, '2006/02/15 12:00:00'))


    it will be more simple because i am using vb net to generate the Query

  7. #7
    Join Date
    Jul 2004
    Posts
    106
    no nothing work
    if I try


    SELECT users.id FROM users
    WHERE users.Inscription < CONVERT(DATETIME, '2006/02/14 12:00:00')


    i get nothing

  8. #8
    Join Date
    Jul 2004
    Posts
    106
    it works in that way


    SELECT users.id
    FROM users
    WHERE (CONVERT(DATETIME, users.Inscription, 111) >= CONVERT(DATETIME, '2006/02/14 00:00:00', 111)) AND (CONVERT(DATETIME, users.Inscription, 111)
    <= CONVERT(DATETIME, '2006/02/15 00:00:00', 111))

    it doesnt work with BETWEEN it will be so praticle

  9. #9
    Join Date
    Dec 2004
    Posts
    502
    I was assuming that the column users.Inscription was of datetime datatype, but it appears that is not the case. What is the datatype? If it's varchar, is it mm/dd/yyyy? yyyy/mm/dd?

  10. #10
    Join Date
    Jul 2004
    Posts
    106
    of course users.Inscription is datetime !
    but it seems crazy to search only on dates (yyyy/mm/dd) no time !
    with currents operators
    >
    <
    =
    <>
    =<
    <=

  11. #11
    Join Date
    Dec 2004
    Posts
    502
    Instead of whining and complaining, how about posting some sample data and showing what you want your results to be, if you aren't getting the results you want. Help us to help you. Don't be so arrogant to think that programmers know more about using queries than experienced DBAs.

  12. #12
    Join Date
    Jul 2004
    Posts
    106
    nosepiker thanks a lot for helping, i have a lot of work to do and have spent allready so much time with thoses dates ! sorry

    I want a normal result : to find any row in my database where a date
    >
    <
    =
    <>
    =<
    <=
    to another date but on the date not on the datetime (yyyy-MM-dd)

    --------------------

    but it seems that i get it with

    SELECT id
    FROM users
    WHERE inscription
    NOT BETWEEN
    CONVERT(DATETIME, '2006/02/14 00:00:00', 120)
    AND
    CONVERT(DATETIME, '2006/02/15 00:00:00', 120)



    thanks a ton
    Last edited by qwer; 02-15-2006 at 02:11 AM.

  13. #13
    Join Date
    Jul 2004
    Posts
    106
    i get it with

    SELECT users.id FROM users WHERE CONVERT(char(10), users.Inscription, 120) = CONVERT(char(10), myDate, 120)

    too


    thank you

  14. #14
    Join Date
    Dec 2004
    Posts
    502
    Are you by any chance using a dateformat different than the U.S. default of 'MDY'?

  15. #15
    Join Date
    Jul 2004
    Posts
    106
    my database is french but i am using
    yyyy-MM-dd hh:mm:ss

    then i try always with 120 to convert a wrong date

    and MDY is really a strang way to store the time :-)

Posting Permissions

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