Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Datepart and Convert DATETIME functions

Posted on 2009-04-08
9
Medium Priority
?
772 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
[X]
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
9 Comments
 
LVL 12

Expert Comment

by:Nathan Riley
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

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 12

Expert Comment

by:Nathan Riley
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 2000 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

704 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