MS SQL Between Hours/Date

Hi,

I would like to run a query that gives me the data from March 1st to March 20th but only between the hour of 11:00 pm to 12:30 AM

table: tbl1
field: fieldAB which is datetime   ie 2011-01-07 10:03:27.000    2011-01-07 00:23:20.000


so something like this

select * from tbl1 where fieldAB between '2011-03-01' and '2011-03-20'
and fieldAB '11:00' and '00:30'

Thanks

ezzadinAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

deightonprogCommented:
use a between on the dates, then use DatePart to check the hours and minutes

you need to figure out suitable logic where the midnight boundary is crossed, in your example you need that hour>=11 OR hour = 0 AND minutes<=30
0
sventhanCommented:

try something like this...


select convert(varchar,fieldab,110) yourdate
 from tbl1 where fieldAB between '2011-03-01' and '2011-03-20'
and dateadd(hh,-1,DATEADD(dd, DATEDIFF(dd,0,fieldAB), 0)), dateadd(mi,-30,DATEADD(dd, DATEDIFF(dd,0,fieldAB ), 0))
group by convert(varchar,fieldab,110), col1
0
ambidextrousCommented:
A different approach...

--Format Time Padded: HHMMSSMMM

If Object_ID('TempDB..#tbl1') Is Not Null
  Drop Table #tbl1
Create Table #tbl1 (
  pKey Int IDENTITY(1,1)
 ,fieldAB DateTime
 ,Primary Key Clustered (
    pKey
  )
)

Insert Into #tbl1
(fieldAB)
      SELECT '2011-03-14 00:34:28.00' --No
UNION SELECT '2011-03-01 10:30:26.00' --No
UNION SELECT '2011-03-02 23:01:28.00' --Yes
UNION SELECT '2011-03-06 12:30:32.00' --No
UNION SELECT '2011-03-07 00:20:00.00' --Yes
UNION SELECT '2011-03-16 14:11:21.00' --No
UNION SELECT '2011-03-22 23:23:23.00' --No
UNION SELECT '2011-03-14 23:23:23.00' --Yes
UNION SELECT '2011-03-20 00:30:00.00' --Yes
UNION SELECT '2011-03-12 11:06:13.00' --No
UNION SELECT '2011-03-14 12:43:25.00' --No
UNION SELECT '2011-03-17 00:00:00.00' --Yes
GO

SELECT 
  fieldAB
 ,Replace(
          Str(DatePart(hh,fieldAB),2) + --24 Hour Format
          Str(DatePart(mi,fieldAB),2) + --Minutes
          Str(DatePart(ss,fieldAB),2) + --Seconds
          Str(DatePart(ms,fieldAB),3)   --Milliseconds
          ,' ','0') as Formatted
FROM #tbl1
GO

--RESULTS
SELECT * 
FROM #tbl1
WHERE 
    fieldAB BETWEEN '2011-03-01 00:00:00' AND '2011-03-20 00:30:00' --3/1 @ Midnight and 3/20 @ 12:30AM
AND
 (
  Cast(
       Replace(
               Str(DatePart(hh,fieldAB),2) + --24 Hour Format
               Str(DatePart(mi,fieldAB),2) + --Minutes
               Str(DatePart(ss,fieldAB),2) + --Seconds
               Str(DatePart(ms,fieldAB),3)   --Milliseconds
               ,' ','0') 
       as Int) <= 003000000 --12:30AM
  OR
  Cast(
       Replace(
               Str(DatePart(hh,fieldAB),2) + --24 Hour Format
               Str(DatePart(mi,fieldAB),2) + --Minutes
               Str(DatePart(ss,fieldAB),2) + --Seconds
               Str(DatePart(ms,fieldAB),3)   --Milliseconds
               ,' ','0') 
       as Int) >= 230000000 --11:00PM
  )

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sventhanCommented:
Sorry, I missed the between

select convert(varchar,fieldab,110) yourdate
 from tbl1 where fieldAB between '2011-03-01' and '2011-03-20'
and fieldab between  dateadd(hh,-1,DATEADD(dd, DATEDIFF(dd,0,fieldAB), 0)) and dateadd(mi,-30,DATEADD(dd, DATEDIFF(dd,0,fieldAB ), 0)) -- added between statement...
group by convert(varchar,fieldab,110), col1
0
ezzadinAuthor Commented:
Thanks All.

Sventhan, I'm trying your solution but can't get any result. what do you mean by Between statement?

I actually moved all the data between March 1st and March 20 to a separate table. now I just need to run a query that gives me data only between 11 pm and 12:30 am

this doesnt return any result: please note that FieldAB is formatted as datetime  

select * from tbl1
where fieldAB between  dateadd(hh,-1,DATEADD(dd, DATEDIFF(dd,0,calldatetime), 0)) and dateadd(mi,-30,DATEADD(dd, DATEDIFF(dd,0,calldatetime ), 0))

0
sventhanCommented:
select * from tbl1
where fieldAB between  dateadd(hh,-1,DATEADD(dd, DATEDIFF(dd,0,calldatetime), 0)) and dateadd(mi,+30,DATEADD(dd, DATEDIFF(dd,0,calldatetime ), 0)) -- should be +30
0
sventhanCommented:
The idea here is get the midnight of the particular day (fieldAB) and look back/forward -1hr/+30minutes...

This DATEADD(dd, DATEDIFF(dd,0,calldatetime), 0  will get you the midnight. 12:00 PM.
0
SharathData EngineerCommented:
try like this.
SELECT * 
  FROM your_table 
 WHERE DATEADD(dd,0,DATEDIFF(DD,0,your_col)) BETWEEN '2011-03-01' AND '2011-03-20' 
       AND (CONVERT(VARCHAR,your_col,108) <= '00:30:00' 
             OR CONVERT(VARCHAR,your_col,108) >= '23:00:00')

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sventhanCommented:
@Sharath

Are you sure about this logic question (Question key is not working in my keyboard)

      AND (CONVERT(VARCHAR,your_col,108) <= '00:30:00'
             OR CONVERT(VARCHAR,your_col,108) >= '23:00:00')

I guess we talking about 2 different dates... If works I'm fine.
0
sventhanCommented:
@Sharath

Never mind.
0
ezzadinAuthor Commented:
@Sharath

your solution worked on to me and I got the data that I was looking for.

@sventhan

I tried your query but never got any data back.. thanks for the help though


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.