Sql Query with Date range

Question

I am trying to fetch records from the table using the below sql query.

SELECT Code,Description FROM Table
WHERE ID= 1 AND FromDate >= '2010-02-14' AND ToDate <= '2012-03-14'

Even though records exists for this date, query returns nothing.

ID HID HCode HDescription FromDate            ToDate 
-------------------------------------------------------------------
1  3   H8    New Year     2012-03-14 12:38:00 2012-03-14 12:38:00

Please give me a suitable solution. Thanks for your time !!

Can you show us the FromDate and ToDate for the record with ID=1? — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Mar 20, 2012 at 7:59
@RoyiNamir - YYYY-MM-DD is safe for date but not for datetime. Have a look here. — Mikael Eriksson
– Mikael Eriksson, Commented Mar 20, 2012 at 8:22
@RoyiNamir - You've misread (as I almost did). It says My SQL Server Wishlist, not My SQL Server Wishlist. — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Mar 20, 2012 at 9:06
@RoyiNamir - the T is what makes that safe. Remove the T and it will fail. — Mikael Eriksson
– Mikael Eriksson, Commented Mar 20, 2012 at 9:12

Royi Namir · Accepted Answer · 2012-03-20 11:15:40Z

2

try this :

declare @dayAfter datetime     --let take 1 day after

set @dayAfter = DateAdd(day,1,'20120314')

SELECT Code,Description FROM Table
WHERE ID= 1 AND 
FromDate >= '20100214' AND 
ToDate <  DateAdd(day, DateDiff(day, 0, @dayAfter ), 0)

p.s :

DateAdd(day, DateDiff(day, 0, @dayAfter ), 0) will reset time to 00:00

so you need desired EndTime < begining of the day after

edited Mar 20, 2012 at 11:15

answered Mar 20, 2012 at 8:11

Royi Namir

149k145 gold badges502 silver badges833 bronze badges

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

1 Comment

Royi Namir Over a year ago

@SyedIbrahim youre welcome. please also read the conversation between me and marc and mikael. there were some important points to notice about. ( and learn [also for me])

user1082916 · Accepted Answer · 2012-03-20 08:49:24Z

1

Try with following query,it will definately solve your problem....

SELECT Code,Description FROM Table
   WHERE ID= 1 AND
         CONVERT(VARCHAR(10), FromDate, 101)>= CONVERT(VARCHAR(10),CAST('2010-02-14' AS DATETIME),101) AND 
         CONVERT(VARCHAR(10), ToDate, 101)<= CONVERT(VARCHAR(10),CAST('2012-03-14' AS DATETIME),101) ;

answered Mar 20, 2012 at 8:49

user1082916

Comments

BelKoD · Accepted Answer · 2012-03-20 08:16:20Z

0

If you select data between FromDate and ToDate then remove ID=1. Аs in this case is selected record with ID = 1 and FromDate and ToDate checked this entry.

answered Mar 20, 2012 at 8:16

BelKoD

3312 silver badges5 bronze badges

Comments

Jānis · Accepted Answer · 2012-03-20 09:25:12Z

0

You are selecting data from ragne

FromDate >= '2010-02-14 00:00:0000' AND ToDate <= '2012-03-14 00:00:0000'

You should do like this:

FromDate >= '2010-02-14' AND ToDate < '2012-03-15'

answered Mar 20, 2012 at 9:25

Jānis

2,2661 gold badge17 silver badges28 bronze badges

Comments

Greg · Accepted Answer · 2016-07-15 02:24:43Z

0

SELECT *
 FROM  table
 WHERE year_date BETWEEN FromDate AND ToDate

answered Jul 15, 2016 at 2:24

Greg

2,6095 gold badges25 silver badges43 bronze badges

Collectives™ on Stack Overflow

Sql Query with Date range

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related