Click here to Skip to main content
14,336,226 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi,

When I'm using the following query the result is displaying when I'm passing @StartDate='1/1/2011' and @EndDate='1/5/2011'

But it's not displaying when I'm passing @StartDate='12/25/2010' or any date of previous year
select * from PS_MSI_LETTER_BATCH BATCH 
where( convert(varchar(10),CREATED_DATETIME,101) >=convert(varchar(10),@StartDate,101) and convert(varchar(10),CREATED_DATETIME,101) <=convert(varchar(10),@EndDate,101))

But when I'm using the following query it's displaying in both the cases i.e. if I pass the previous year date or this year date.
select * from PS_MSI_LETTER_BATCH
WHERE CREATED_DATETIME BETWEEN '2009-12-31 03:31:25.760' AND '2011-01-05 03:31:25.760'

Could anyone help on this?

Thanks,
Prasant
Posted
Updated 4-Jan-11 2:37am
v2
Comments
JF2015 4-Jan-11 8:37am
   
Added code formatting.
Manfred Rudolf Bihy 4-Jan-11 9:49am
   
Hi Prasant, I made a modification of my answer that should yield a working select statement. Have a look.
Rate this:
Please Sign up or sign in to vote.

Solution 1

No wonder in your first SQL statement you convert to varchar so you're comparing strings which is not the same as datetime comparison.
You have to look into your table to give us an example of the datetime values in your database after they are converted.

Modify your SQL statment like this to let us see how SQL Server converted the values:
SELECT *, convert(varchar(10),CREATED_DATETIME,101) AS convdatetovarchar FROM PS_MSI_LETTER_BATCH BATCH
WHERE (convert(varchar(10),CREATED_DATETIME,101) >=convert(varchar(10),@StartDate,101) and convert(varchar(10),CREATED_DATETIME,101) <=convert(varchar(10),@EndDate,101))


Lookig forward to your response.

Modification:
Try this modification to make it work:
SELECT * FROM PS_MSI_LETTER_BATCH BATCH
WHER CREATED_DATETIME BETWEEN CONVERT(DATETIME, @StartDate, 101) AND CONVERT(DATETIME, @EndDate, 101)

This works given that CREATED_DATETIME is of type DATETIME and the parameters you are passing are strings in US-american (101) format.
End modification


Best Regards,
Manferd
   
v7
Rate this:
Please Sign up or sign in to vote.

Solution 2

You may also try as follows (depending on the version of Sql Server):

SELECT * from PS_MSI_LETTER_BATCH
WHERE
convert(date, CREATED_DATETIME) BETWEEN @StartDate AND @EndDate


or

select * from PS_MSI_LETTER_BATCH
WHERE 
datediff(day, @StartDate, convert(varchar(10), CREATED_DATETIME, 101)) >= 0 and
datediff(day, @EndDate, convert(varchar(10), CREATED_DATETIME, 101)) <= 0


I hope this help.
   
v3
Comments
prasant Jinaga 4-Jan-11 10:17am
   
Thanks Sir It's working...:)
Manfred Rudolf Bihy 4-Jan-11 10:57am
   
Spam link has been removed and reported.

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100