dualtech
asked on
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,
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,
>>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))
don't do that...very slow.
where datefield >=cast('20091011' as datetime) and datefield< dateadd(dd,1, cast('20091011' as datetime))
ASKER
I am using sql server 2000 will it work in that or not?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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)
declare @x datetime
set @x = cast('10/11/2009' as datetime)
select * from tablename
where datefield >= @x and
datefield < dateadd(dd, 1, @x)
ASKER
Yes this solution worked for me.. thank you
I think thats the same i posted :)
where datefield >='20091011' and datefield< dateadd(dd,1, '20091011' )
or
where convert(varchar, datecolumn, 112) = '20091011'