• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

SQL Datepart and Convert DATETIME functions

I am trying to get a query to work.  I have a table that includes a column REQDATE that is a datetime type.  I need to return all records for a span of time REQDATE between '03/30/2009' and '04/06/2009'.  With the records returned I need to return all the records where the time is between 17:00:00 hours and 08:00:00 hours.  I am having trouble with this part.  The datetime is written like 08:00:00 AM, not in 24-hour time.
SELECT     *
FROM         TASKS
WHERE     (DATEPART(hh, REQDATE) BETWEEN '17:00:00' AND '08:00:00') AND (REQDATE BETWEEN CONVERT(DATETIME, '2009-03-30 00:00:00', 102) 
                      AND CONVERT(DATETIME, '2009-04-06 00:00:00', 102))

Open in new window

0
jeremymjackson
Asked:
jeremymjackson
1 Solution
 
Nathan RileyFounder/CTOCommented:

SELECT     *
FROM         TASKS
WHERE  Datepart > '17:00:00'
and Datepart < '08:00:00'
and ReqDate > CONVERT(DATETIME,'2009-03-30 00:00:00',102)
and ReqDate < CONVERT(DATETIME, '2009-04-06 00:00:00',102)

Open in new window

0
 
RiteshShahCommented:
wont something like this work?

WHERE
(datepart(hh,REQDATE)+':'+datepart(ss,REQDATE)+':'+datepart(ss,REQDATE)) BETWEEN '17:00:00' AND '08:00:00'
and
REQDATE BETWEEN CONVERT(DATETIME, '2009-03-30 00:00:00', 102)
                      AND CONVERT(DATETIME, '2009-04-06 00:00:00', 102))
0
 
jeremymjacksonAuthor Commented:
SELECT     *  FROM         TASKS WHERE  Datepart > '17:00:00' and Datepart < '08:00:00' and ReqDate > CONVERT(DATETIME,'2009-03-30 00:00:00',102) and ReqDate < CONVERT(DATETIME, '2009-04-06 00:00:00',102)

Returns:  Msg 207, Level 16, State 1, Line 1
Invalid column name 'Datepart'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Datepart'.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jeremymjacksonAuthor Commented:
select * from tasks WHERE  (datepart(hh,REQDATE)+':'+datepart(ss,REQDATE)+':'+datepart(ss,REQDATE)) BETWEEN '17:00:00' AND '08:00:00' and REQDATE BETWEEN CONVERT(DATETIME, '2009-03-30 00:00:00', 102)                        AND CONVERT(DATETIME, '2009-04-06 00:00:00', 102))

returns:  Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
0
 
Nathan RileyFounder/CTOCommented:
Sorry, re read your question here you go:
SELECT     *
FROM         TASKS
WHERE ReqDate > CONVERT(DATETIME,'2009-03-30 17:00:00',102)
and ReqDate < CONVERT(DATETIME, '2009-04-06 08:00:00',102)

Open in new window

0
 
BrandonGalderisiCommented:
You want the hour > 17 or hour < 8.  It can't be both.

SELECT     *
FROM         TASKS
WHERE     REQDATE >= CONVERT(DATETIME, '2009-03-30 00:00:00', 102)
and reqdate <= CONVERT(DATETIME, '2009-04-06 00:00:00', 102))
and (DATEPART(hh, REQDATE) <8 or DATEPART(hh, REQDATE) > 17)
0
 
jeremymjacksonAuthor Commented:
I want to return rows where the hour is between 17 and 8.
0
 
jeremymjacksonAuthor Commented:
BrandonGalderisi, Your query worked great.  Thanks.
0
 
LLMTSCommented:
by any chance is this related to Track It?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now