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
- from 17hours to 06 hours ( which spans two days)
- 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
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
create table #sreenu9f(_DateTime DateTime )
insert into #sreenu9f
select distinct dateadd(hour, column_id, getdate()) as _DateTime
select *, datepart(hour, _DateTime) as _hours_later, CAST(_DateTime as time) _time
where datepart(hour, _DateTime) between 6 and 17
order by 1 desc
drop table #sreenu9f
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
CREATE TABLE #DATA
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'
SELECT 'SAMPLE DATA',* FROM #DATA
'SQL OUTPUT ',
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' )
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.