Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Datepart and Convert DATETIME functions

Posted on 2009-04-08
9
759 Views
Last Modified: 2012-05-06
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
Comment
Question by:jeremymjackson
9 Comments
 
LVL 11

Expert Comment

by:N R
ID: 24098717

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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24098730
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
 

Author Comment

by:jeremymjackson
ID: 24098812
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:jeremymjackson
ID: 24098816
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
 
LVL 11

Expert Comment

by:N R
ID: 24098836
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
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24099517
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
 

Author Comment

by:jeremymjackson
ID: 24099635
I want to return rows where the hour is between 17 and 8.
0
 

Author Closing Comment

by:jeremymjackson
ID: 31568096
BrandonGalderisi, Your query worked great.  Thanks.
0
 

Expert Comment

by:LLMTS
ID: 35336693
by any chance is this related to Track It?
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question