Query to select data between two dates

Question

I have a start_date and end_date. I want to get the list of data in between these two dates. Can anyone help me pointing the mistake in my query.

select * from [dbo].[User] 
where Date between'2014-8-01' AND '2014-8-30'

There is no error but also the query doesn't return any records.

Well I'd start by adding a space after between, and using full yyyy-MM-dd format, i.e. 08 instead of 8 for the month. I realize it's unlikely to make a different, but give that a try to start with. What's the type of the Date field? — Jon Skeet
– Jon Skeet, Commented Oct 11, 2014 at 7:48

Dgan · Accepted Answer · 2014-10-11 07:48:16Z

2

Proper casting is necessary

select * from [dbo].[User] 
    where CAST([Date] as DATE) between '2014-08-01' AND '2014-08-30'

Format for Date Passing as Parameter

'yyyy-MM-dd'

answered Oct 11, 2014 at 7:48

Dgan

10.3k1 gold badge33 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Presumably only if Date isn't the right type already... I've asked the OP to clarify.

sqluser · Accepted Answer · 2014-10-13 03:26:14Z

1

Better to use >= and < and use time in your where clause instead of using CAST in where. Better performance.

SELECT * FROM [dbo].[User] 
    WHERE [Date] >= '2014-8-01 00:00:00'
    AND [Date] < '2014-8-31 00:00:00'

answered Oct 13, 2014 at 3:26

sqluser

5,7127 gold badges44 silver badges52 bronze badges