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

Try this:

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

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

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.

yes of course but really with such a database ... no beeing able to search uin a very simple way on dates

thank you

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

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

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

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?

of course users.Inscription is datetime !
but it seems crazy to search only on dates (yyyy/mm/dd) no time !
with currents operators
>
<
=
<>
=<
<=

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.

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

i get it with

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

too


thank you

Are you by any chance using a dateformat different than the U.S. default of 'MDY'?

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