Click here to Skip to main content
14,774,467 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Iam USing sql server 2005 as my back end

iam using query with date condition.

select * from Purchase v 
where cast( v.DocDate as datetime) >= cast('Sep 26 2014 12:00AM'as datetime ) 
and cast( v.DocDate as datetime) < cast('Aug 27 2014 12:00AM' as datetime)

when the above query is executed i cant get any results

but records are there.

Can u tell me what mistake iam making


Nirmala Sarvanan
Updated 26-Oct-14 21:04pm
Laiju k 27-Oct-14 1:40am
you have to check the date format in DB.I think it will be year-month-date.You cant get data until the both format matches.
DamithSL 27-Oct-14 1:45am
what is the column type of DocDate ?
Nirmala Saravanan 27-Oct-14 1:59am

Wait, you're selecting documents that are simultaneously stamped after September 26th and before August 27th? Only if they do that quantum time travel.

UPDATE: Sorry, I guess that was more of a snarky comment than solution. Here is code that should get you your results (swapped your operators):

select * from Purchase v 
where cast( v.DocDate as datetime) <= cast('2014-09-26 12:00:00.000'as datetime ) 
and cast( v.DocDate as datetime) > cast('2014-08-10 12:00:00.000' as datetime)
Nirmala Saravanan 27-Oct-14 2:26am
THANKS,Actually i was working full night and i couldn't see anything straight.
I know that there is mistake in condition with operators and i couldn't see it
try this

select * from Purchase v
where convert(varchar,cast( v.DocDate as datetime),100) <= convert(varchar,cast('Sep 26 2014 12:00AM' as datetime),100)
and convert(varchar,cast( v.DocDate as datetime),100) >= convert(varchar,cast('Aug 27 2014 12:00AM' as datetime),100)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900