-
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
Last edited by qwer; 02-15-2006 at 02:11 AM.
-
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 :-)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|