Click here to Skip to main content
14,668,470 members
Rate this:
Please Sign up or sign in to vote.
See more:
How to get record between two two dates in sqlserver table ??

this query is running fine --
select Jobtitle ,CONVERT(varchar(10),CreatedDate,103) from JobForm where CreatedDate between GETDATE()-30 and GETDATE()

and returning appropriate result

but this is NOT whats the error --

when i am running this query --

select JobTitle, CONVERT(VARCHAR(11),CreatedDate,103)  from JobForm
    where CreatedDate between CONVERT(VARCHAR(11),'06/03/2013',103) and  CONVERT(VARCHAR(11),'09/02/2013',103)



I am getting no record ,just blank result
Posted

Rate this:
Please Sign up or sign in to vote.

Solution 1

Swap the date around: BETWEEN expects the first one to be the start date, and the second to be the end date.
Since your end is before your start, no records are returned.
   
Comments
sr_24 16-Mar-13 10:40am
   
i have swaped them , but not working ---

select JobTitle, CONVERT(VARCHAR(11),CreatedDate,103) from JobForm
where CreatedDate between CONVERT(VARCHAR(11),'09/02/2013',103) and CONVERT(VARCHAR(11),'06/03/2013',103)
Rate this:
Please Sign up or sign in to vote.

Solution 2

Hello,

For date comparison all values must be of date type. Your query will return no records as you are trying string comparison, BETWEEN will not work as expected.
CONVERT(VARCHAR(11), CreateDate, 103)
I suggest you change your SQL to
SELECT JobTitle, CONVERT(VARCHAR(11),CreatedDate,103)  FROM JobForm
    WHERE CreatedDate BETWEEN  CONVERT(DATE,'09/02/2013',103) AND  CONVERT(DATE,'06/03/2013',103)


Regards,
   
v2
Comments
sr_24 16-Mar-13 10:45am
   
not working sir !
i tried this as well --

SELECT JobTitle, CONVERT(VARCHAR(11),CreatedDate,103) FROM JobForm
WHERE CONVERT(DATE,CreatedDate,103) BETWEEN CONVERT(DATE,'06/03/2013',103) AND CONVERT(DATE,'09/02/2013',103)
Prasad Khandekar 16-Mar-13 10:53am
   
Try swapping dates as suggested by OriginalGriff.
sr_24 16-Mar-13 10:56am
   
tried it but not worked
Prasad Khandekar 16-Mar-13 11:19am
   
Solution 3 will work definitely. The earlier solution 2 will work only if the time component of the CreatedDate is 0.

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

Solution 3

I suggest to also add the same date conversion on the CreateDate column as well before comparing:

SELECT JobTitle, CONVERT(VARCHAR(11),CreatedDate,103) FROM JobForm
WHERE CONVERT(DATE,CreatedDate,103) BETWEEN CONVERT(DATE,'09/02/2013',103) AND CONVERT(DATE,'06/03/2013',103)
   

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