• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

select * from dbo.anytable where date = ' any date' does not give any data

Hello all,

I am trying to delete data from the table for one date I have date field with date and time both. I am not able to filter the date properly I have tried using all the formats of the date but none of them is not working.

I am not getting any error but no data is being displayed.

Please tell me what mistake I am doing.

Thank you in advance.
Regards,
0
dualtech
Asked:
dualtech
  • 3
  • 3
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
two methods

where datefield >='20091011' and datefield< dateadd(dd,1,  '20091011' )

or

where convert(varchar, datecolumn, 112) = '20091011'
0
 
chapmandewCommented:
>>where convert(varchar, datecolumn, 112) = '20091011'

don't do that...very slow.

where datefield >=cast('20091011' as datetime) and datefield< dateadd(dd,1,  cast('20091011' as datetime))
0
 
dualtechAuthor Commented:
I am using sql server 2000 will it work in that or not?
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
chapmandewCommented:
Yes, it should.  here is an even better way:

declare @x datetime
set @x = cast('20091011' as datetime)

select * from tablename
where datefield >= @x and
datefield < dateadd(dd, 1, @x)
0
 
dualtechAuthor Commented:
No it is not working for me I tried using your query chapmandew but it gives me error. and in what format is 20091011 is written. I am not able to get it.
0
 
chapmandewCommented:
try this:

declare @x datetime
set @x = cast('10/11/2009' as datetime)

select * from tablename
where datefield >= @x and
datefield < dateadd(dd, 1, @x)
0
 
dualtechAuthor Commented:
Yes this solution worked for me.. thank you
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
I think thats the same i posted :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now