Easy SQL Query Question

[mlocurci]

I need to only pull records from 6:30AM till 10:30PM and on a certain date. How do I modify thia where clause to do that???

WHERE (((r.date_time) Between #9/1/2002# And #9/30/2002 22:30:0#) AND ((r.ct_id)=1))

Thanks!

-Michael

 

[unseth]

WHERE (r.date_time BETWEEN '9/1/2002 06:30' AND '9/30/2002 20:30) AND r.ct_id = 1

 

[unseth]

oops i forgot, after 20:30 and before the ) there should be another '

 

[geokor]

I think I'd prefer to seperate the date and the time.
WHERE DATE = .... and time between A and B;
In MySQL, for example,
20020905132800 and 020905132800 are interpreted as '2002-09-05 13:28:00' so you could easily seperate the date and time portions using string splitting functions (depending on your implementation) such as LTRIM or whatever.
But my solution of choice would be to use regular experessions (if you dbm supports them) as in
WHERE datetime REGEXP "^20020905"; or WHERE datatime REGEXP "132800$"; These would pull datetimes starting with 20020905 or ending in 132800.

Hope this points you toward a solution. George K

...the foolishness of men! When they are learned they think they are wise.

 

[geokor]

Sorry, forgot what forum I was In!
In SQL Server you could try

WHERE DATEPART(d, incoming date) = target date day and DATEPART(m, incoming date) = target date month and DATEPART(yyyy, incoming date) = target date year and DATEPART(hh, incoming date) between 6 and 10 ...

hour hh
minute mi, n
second ss, s
millisecond ms

You get the idea.

Again, I hope this helps. George K

...the foolishness of men! When they are learned they think they are wise.

 

[mlocurci]

Thanks everyone! Worked like a champ!

 

[mlocurci]

Opps I didn't understand my own requirments.

I need to pull between 9/1/02 and 9/15/02 from 7AM to 11PM daily. I am assuming i need 2 statements, one to pull the date, then scrub for time. But I am worried about performance.

What does everyone thing????

 

[SQLBill]

Have you tried this (it's unseth's suggestion but with a modification)?

WHERE r.date_time >= '9/1/2002 07:00:00.000'
AND r.date <= '9/30/2002 23:00:00.00')

-SQLBill

 

[r937]

i think you're right, you need two conditions, one for the date range and one for the daily time range

rudy

http://rudy.ca/

 

[skicamel]

try this...

select * from table where date between '9/1/02' and '9/15/02 23:59:29'
and datepart(hh,date) between 7 and 23

 

[mlocurci]

Can you clearify what you mean by 'datepart'. the field contains a DateTimeStamp.

thanks

 

[xloop]

Why on earth would you need datepart?

This is simple;

WHERE r.date_time Between CONVERT(datetime,'9/1/2002',103) AND CONVERT(datetime,'9/30/2002 22:30:00',103) AND r.ct_id =1)

 

[xloop]

change the dates as required;

CONVERT(datetime, '9/1/2002 06:30:00',103) etc...

 

[mlocurci]

whats the 103 mean?

 

[xloop]

103 is the format in which you would like the date.

 

[mlocurci]

I am using MS Access SQL, and it says convert is not allowed:

WHERE (((r.date_time) Between CONVERT(date_time,'9/1/2002 06:00:00',103) AND CONVERT(date_time,'9/30/2002 22:30:00',103) AND ((r.ct_id)=1)))

 

[xloop]

No convert isn't a function of Access!

I sort of figured that you were in a SQL forum thefore using SQL Server.

in that case it would be;

WHERE r.date_time between '9/1/2002 06:30:00' AND '9/1/2002 22:30:00' AND r.ct_id=1

 

[mlocurci]

I was hoping the 2 wouldn't be that different.

My problem is I want the where condition to choose a whole month and that timeframe, everyday

Monday Tuesday Etc....
6-11 6-11

 

[xloop]

skicamel (Programmer) Sep 25, 2002
try this...

select * from table where date between '9/1/02' and '9/15/02 23:59:29'
and datepart(hh,date) between 7 and 23

 

[skicamel]

That's why on earth I needed datepart, xloop.