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?
 
SharathConnect With a Mentor Data 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
 
deightonConnect With a Mentor progCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
ambidextrousConnect With a Mentor Commented:
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
 
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
 
sventhanConnect With a Mentor Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.