SQLTeam.com | Weblogs | Forums

Records from 19 hours to 07 hours from a datetime field

Hi,
I am trying to get the records based on the DateTime column (which has both date and time) for only the records that were added during some hours like

  1. from 17hours to 06 hours ( which spans two days)
  2. 06 hours to 17 hours ( same day which is easier).
    I tried the below but having issues. Any suggestions greatly appreciated.
    CAST(DateTime as time)>= '17' or
    CAST(DateTime as time) < '07'
    the error is Conversion failed when converting date and/or time from character string

what data type is DateTime column

datetime

what do you mean by
from 17hours to 06 hours . Is it 17th hour of Monday to 6th hour of Tuesday (military)?
or do you mean military time? or do you mean 5pm Monday to 12 noon Tuesday?

Thanks for the quick response.
its earlier one 17th on monday to 6th of tuesday military

Best is not to convert to char, which is rather slow.

CASE WHEN DATEPART(HOUR, DateTime) >= 17 OR DATEPART(HOUR, DateTime) < 7

But you could also do this:

CAST(DateTime as time)>= '17:00' or
CAST(DateTime as time) < '07:00'

Thank you i used the Cast method but forgot to put :00


use DBAdmin
go

create table #sreenu9f(_DateTime  DateTime )

insert into #sreenu9f
select distinct dateadd(hour, column_id, getdate()) as _DateTime 
  From sys.all_columns

select *, datepart(hour, _DateTime) as _hours_later, CAST(_DateTime as time) _time
  From #sreenu9f
 where datepart(hour, _DateTime) between 6 and 17

  order by 1 desc

drop table #sreenu9f

hi

I TRIED TO DO THIS ..
PLEASE SEE IF ITS WHAT YOU WANT !!!!

whats the point .. you want to group them ... !!!!

please click arrow to the left for drop create sample data
DROP TABLE #DATA 
GO 

CREATE TABLE #DATA
(
RECORD_ENTRY DATETIME 
)
GO 

INSERT INTO #DATA SELECT '2020-01-26 22:38:54.317'
INSERT INTO #DATA SELECT '2020-01-26 10:19:55.310'
INSERT INTO #DATA SELECT '2019-12-21 19:10:35.317'
INSERT INTO #DATA SELECT '2019-06-13 07:19:55.310'
GO 

SELECT 'SAMPLE DATA',* FROM #DATA

image

SELECT 
       'SQL OUTPUT ',
       RECORD_ENTRY,
	   CONVERT(TIME, RECORD_ENTRY) 
FROM 
   #DATA
WHERE    CONVERT(TIME, RECORD_ENTRY) BETWEEN CONVERT( TIME, '5:00:00 PM' )  AND CONVERT( TIME, '11:59:59 PM' )
      OR CONVERT(TIME, RECORD_ENTRY) BETWEEN CONVERT( TIME, '12:00:00 AM' ) AND CONVERT( TIME, '05:59:59 AM' )
	  OR CONVERT(TIME, RECORD_ENTRY) BETWEEN CONVERT( TIME, '6:00:00 AM' )  AND CONVERT( TIME, '4:59:59 PM' )
GO 

If these ranges were accurate, they would never fail: since they cover every hour of the day, every row would always be included.

However, since you left off fractional seconds, you will complete skip some rows.

I'm not sure why you felt compelled to get specific on the ending time, that's just far too risky, an approach guaranteed to cause an error at some point.