Problem with Date Query - sql server 2008

Question

i have this date's & Time's:

date1: 10/04/2010 - Time: 08:09

date2: 11/04/2010 - Time: 08:14

i need to show all the date's between 10/04/2010 time 06:00 and 11/04/2010 time 6:00

i write this: select * from MyTbl where ((Tdate BETWEEN '20100410' AND '20100411') and (Ttime BETWEEN '06:00' and '06:00'))

but i get empty table

thank's in advance

what data type is Tdate and Ttime?

gbn
– gbn

2010-04-12 06:55:51 +00:00
Commented Apr 12, 2010 at 6:55 — gbn
– gbn, Commented Apr 12, 2010 at 6:55
Tdate and Ttime are datetime type

Gold
– Gold

2010-04-12 07:03:01 +00:00
Commented Apr 12, 2010 at 7:03 — Gold
– Gold, Commented Apr 12, 2010 at 7:03

gbn · Accepted Answer · 2010-04-12 07:13:48Z

3

If both columns are datetime, simply add them so you can do a "proper" range query

where Tdate +Ttime BETWEEN '2010-04-10 06:00:00' AND '2010-04-11 06:00:00'

answered Apr 12, 2010 at 7:13

gbn

434k84 gold badges602 silver badges690 bronze badges

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

1 Comment

Ashish Gupta Over a year ago

+1 for Tdate +Ttime and then using the "BETWEEN". Saves atleast 2 more lines. :-)

Faruz · Accepted Answer · 2010-04-12 06:42:26Z

1

That's not correct.

What your resultset returns is all the records that are valid between 10/04/2010 06:00 - 10/04/2010 06:00 or 11/04/2010 06:00 - 11/04/2010 06:00.

(Which means there are exactly 2 valid minutes for records to be retrieved).

You need to filter according to the full date including time.

answered Apr 12, 2010 at 6:42

Faruz

9,94910 gold badges51 silver badges66 bronze badges

Comments

Jens Piegsa · Accepted Answer · 2013-02-19 11:26:42Z

1

Find total number of orders that were placed in each month of the year 1987.

SELECT COUNT(OrdId) AS TotalNumberOfOrder FROM OrderABC WHERE OrdDate > '1987-01-01' AND OrdDate < '1987-12-31';

or

SELECT COUNT(OrdId) AS TotalNumberOfOrder FROM OrderABC WHERE OrdDate BETWEEN '1987-01-01' AND '1987-12-31';

edited Feb 19, 2013 at 11:26

Jens Piegsa

7,5456 gold badges64 silver badges110 bronze badges

answered Feb 19, 2013 at 11:02

pritesh

112 bronze badges

Comments

Nitin Midha · Accepted Answer · 2010-04-12 06:44:36Z

0

I think you are looking for this:

select * 
from MyTbl 
where (Tdate = '20100410' AND Time >= '06:00') 
   OR (TDATE > '20100410' AND TDATE < '20100410')
   OR (Tdate = '20100411' AND Time <= '06:00')

answered Apr 12, 2010 at 6:44

Nitin Midha

2,27820 silver badges22 bronze badges

2 Comments

Amsakanna Over a year ago

do you think that will really work? You can use 'AND' instead.

Amsakanna Over a year ago

select * from MyTbl where (Tdate = '20100410' AND Time >= '06:00') AND (Tdate > '20100410' AND TDATE < '20100410') AND (Tdate = '20100411' AND Time <= '06:00')

Collectives™ on Stack Overflow

Problem with Date Query - sql server 2008

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related