sql query to get records between dates

Question

Id   | Status | Value  | CreatedDateTime
-----+--------+--------+------------------------
1    | Open   | Y      | 2014-12-10 14:03:10.100
2    | Open   | Y      | 2014-12-10 13:03:10.100
3    | Open   | N      | 2014-12-10 13:01:12.100
4    | Open   | Y      | 2014-12-10 05:02:11.100
5    | Open   | N      | 2014-12-09 15:03:10.100
6    | Open   | N      | 2014-12-08 14:03:10.100
7    | Open   | Y      | 2014-12-08 14:03:10.100

I wants to write a SQL query to get the records from 2014-12-08 14:03:10.100 to 2014-12-10 14:03:10.100 and also the Status should be Open with Value = Y.

I tried below sql query-

select * 
from mytable 
where CreatedDateTime between '2014-12-08 14:03:10.100' and '2014-12-10 14:03:10.100' 
  and Status in ('Open') and Value='Y'

I am not sure how to post table data on stackoevrflow site. Someone please correct my table..thanks — Syed, Dec 16 '14 at 10:23
Thanks for using stack overflow, Syed. I recommend you start looking at the "SELECT" keyword and the "WHERE" keyword. — Jonathan, Dec 16 '14 at 10:24
Please show your attempts at solving this and the errors you see as it's pretty basic SQL. You're more likely to learn something if we can point out where you're going wrong rather than just giving you the answer. — Tanner, Dec 16 '14 at 10:24
It's simple. Just google for date between and where clause and you will get the answer. — SMA, Dec 16 '14 at 10:26
Your SQL works fine: **http://sqlfiddle.com/#!3/59dfb/1** although I would change the `IN` clause to `Status = 'Open'` unless you plan to include more values. Please extend your question if something is missed by that example fiddle. — Tanner, Dec 16 '14 at 10:31

score 0 · Answer 1 · edited May 23 '17 at 12:22

0

How about something like this

SELECT Id,Status,Value,CreatedDateTime FROM <table>
WHERE CreatedDateTime BETWEEN '2014-12-08 14:03:10.100' AND '2014-12-10 14:03:10.100'
AND
Status = 'Open'
AND
Value = 'Y'

The BETWEEN operator is inclusive so will include the dates above see here - Link

EDIT: Removed incorrect statement about how the BETWEEN operator works

edited May 23 '17 at 12:22

Community

1
1

answered Dec 16 '14 at 10:26

martpendle

432
4
19

This answer was added before the OP added his SQL. – martpendle Dec 16 '14 at 10:39

score -1 · Answer 2 · answered Dec 16 '14 at 10:53

-1

Because of your Datetime
Try this :

 select * 
    from mytable 
    where convert(date,paid_on,103) between '2014-12-08' and '2014-12-10' 
      and Status in ('Open') and Value='Y'

answered Dec 16 '14 at 10:53

King_Fisher

1,171
8
21
51

score -1 · Answer 3 · answered Dec 16 '14 at 12:07

-1

try this code

SELECT 
     Id
    ,Status
    ,Value
    ,CreatedDateTime 
FROM <table>
WHERE
    CreatedDateTime <= '2014-12-08 14:03:10.100'
    AND CreatedDateTime >= '2014-12-10 14:03:10.100'
    AND Status = 'Open'
    AND Value = 'Y'

answered Dec 16 '14 at 12:07

Mani

1
3

sql query to get records between dates

3 Answers3