Selecting records between two dates

Question

Ive column CREATEDDATE ( DATETIME ) I need to find all rows between two dates $startDate $endDate format ('d/m/Y') ?

SELECT * FROM TABLE 
 WHERE CREATEDDATE >= CONVERT(DATETIME, $startDate, 103)
 AND CREATEDDATE <= CONVERT(DATETIME, $endDate, 103)

http://sqlfiddle.com/#!3/b0478/5 where is the problem ?!

Sharing your research helps everyone. Tell us what you've tried and why it didn’t meet your needs. This demonstrates that you’ve taken the time to try to help yourself, it saves us from reiterating obvious answers, and most of all it helps you get a more specific and relevant answer! — juergen d, Mar 11 '13 at 13:21
I understant first close votes, but not the last, when question was updated and sqlfiddle was provided. — dani herrera, Mar 11 '13 at 15:32

Sachin · Answer 1 · 2013-03-11T14:40:08.450

2

This is simple and could be find by googling..
You should google first before asking question..

Select * From Tbl where CREATEDDATE between $date1 and  $date2

if $date1 is varchar then you have to use CONVERT(DATETIME, $date1, 103) (for 'd/m/y')
For datetime convertion in sql you can refer this link..

Here is the Solution :

updated Fiddle

edited Mar 11 '13 at 14:40

answered Mar 11 '13 at 13:22

Sachin

2,152
1
21
43

sorry but it doesnt work .. – Myaw Mar 11 '13 at 13:35
There is a rather lengthy comment to [this answer](http://stackoverflow.com/a/14153977/569436) about the pitfalls of using between. – Mikael Eriksson Mar 11 '13 at 13:36
@Myaw "sorry but it doesnt work" is not very informative. Update your question with what is not working. BTW, dates does not have a format. Strings do but surely you are not having dates in a string data type? – Mikael Eriksson Mar 11 '13 at 13:38
I ve tried that too, still not working ... SELECT * FROM TABLE WHERE CREATEDDATE BETWEEN CONVERT(DATETIME, $startDate, 103) AND CONVERT(DATETIME, $endDate, 103) – Myaw Mar 11 '13 at 13:38
what is the $startdate (in which format like dd/MM/yyyy or any other ... )??? – Sachin Mar 11 '13 at 13:42
More info on why you should never use BETWEEN for date range queries: https://sqlblog.org/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx – Aaron Bertrand Mar 11 '13 at 13:45
$startDate/$endDate format d/m/Y – Myaw Mar 11 '13 at 13:46
Also with SELECT * FROM TABLE WHERE CREATEDDATE >= CONVERT(DATETIME, $startDate, 103) AND CREATEDDATE <= CONVERT(DATETIME, $endDate, 103) still not working – Myaw Mar 11 '13 at 13:47
WHAT DOES "not working" MEAN? – Aaron Bertrand Mar 11 '13 at 13:50
CREATEDDATE DATETIME Im using convert(datetime,,103) when storing date in the CREATEDDATE column – Myaw Mar 11 '13 at 13:50
SO, The query retour no data but in my table I've data stored between the both date – Myaw Mar 11 '13 at 13:52
please create a new SQL Fiddle (sqlfiddle.com) with test data and query.. some time there may be issue with time (hour, minute, sec,...) – Sachin Mar 11 '13 at 13:52
Hum, First time using sqlfiddle so I don't know how it work http://sqlfiddle.com/#!3/2a80d/2 – Myaw Mar 11 '13 at 14:04
@Myaw somthing like [this SQL Fiddle](http://sqlfiddle.com/#!3/d22a4/1)? You are using datetime. No need to add format when saving. If you want a specific format when presenting the dates you should do that in the client. – Mikael Eriksson Mar 11 '13 at 14:08
Yep Mekael, you have used maybe iso format but I'm using format dd/mm/YY what the client have choosed as date – Myaw Mar 11 '13 at 14:14
@ Sac , still no data retourned :s – Myaw Mar 11 '13 at 14:17
http://sqlfiddle.com/#!3/b0478/9 why this query retourn no data? – Myaw Mar 11 '13 at 14:25
try this http://sqlfiddle.com/#!3/d22a4/6 – Sachin Mar 11 '13 at 14:26
@Myaw because you are converting a datetime to a string that is then converted back to a datetime again. Convert from string to datetime when assigning your variables. http://sqlfiddle.com/#!3/b0478/6 – Mikael Eriksson Mar 11 '13 at 14:26
its working know thanks a lot guys, But why the @toDate is not Included ? http://sqlfiddle.com/#!3/b0478/11 – Myaw Mar 11 '13 at 14:39
just using DATEADD(d,1,@toDate) and now its work... – Myaw Mar 11 '13 at 14:50
@Myaw, Explained [here](http://stackoverflow.com/a/15342169/842935) why returns no data. – dani herrera Mar 11 '13 at 15:28

dani herrera · Answer 2 · 2013-03-11T15:27:08.053

The real issue in sqlfiddle is that you are casting string to date in variable declaration but sql sentence:

declare @FromDate datetime = '11/03/2013'  --here a type casting!!
declare @ToDate datetime = '12/03/2013'

Then this has no effect:

xCreatedDate < convert(datetime,@ToDate,103)  --@toDate is yet a date!!

To fix, change datetime to date in variable declaration:

declare @FromDate varchar(10) = '11/03/2013'  --here without casting!!
declare @ToDate varchar(10) = '12/03/2013'

score 0 · Accepted Answer · edited Mar 11 '13 at 13:51

0

select CREATEDDATE from table where CREATEDDATE BETWEEN $date1 AND $date2

or you can use

select CREATEDDATE from table where CREATEDDATE > $date1 AND CREATEDDATE < $date2

edited Mar 11 '13 at 13:51

Aaron Bertrand

272,866
37
466
490

answered Mar 11 '13 at 13:24

Gijo

249
1
9

Selecting records between two dates

3 Answers3