specifing date without time 2

[mimi2]

Hello,
The date in my database is: 12/4/2002 11:45:54 AM

if i do a search this way , i don't get any result:
select * from table1 where mydate = '12/04/2002'

i succeed with this command: select * from table1 where mydate > '12/04/2002'

how can i ignore time and fetch the correct date ?
Thanks

 

[webmigit]

select * from table1 where mydate like '12/04/2002%'

The percent sign is a wildcard for the like operator. ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[mimi2]

thanks for the answer, but still it does not work !!

 

[mikeshort]

You need to use a function to convert the stored date value into something that you can compare. The 'like' doesn't work, because your date is not stored as a string.

In Oracle, the SQL would look something like this:

select * from table1 
where to_char(mydate, 'mm/dd/yyyy') = '12/04/2002'

I'm sure your database has a similar function that would do the conversion for you.

 

[mimi2]

thanks a lot.
i found this answer from another forum:
select * from table1
where datediff(dd, mydate, '12/04/2002') = 0

 

[NoWayIsThisInUse]

I had a similar problem last week and posted the answer to my own question...
Basically, I was searching for records between 2 dates (from a form). On the form.startdate I did a DateAdd() function adding hours "00" and minutes 'n' "00 and hours "23" and minutes "59 to the enddate and it works great.