Fetching Records from the Table Based on specific Time Interval for the Same Date in SQL server

Question

I have a table with column Date and time together (Say Created Date). I have a requirement to fetch records from the Table by passing the time alone(since I retrieve the records at different intervals in same date).

My question is how do I break the time part alone in DateTime field in my Table while Fetching the records using Stored procedure

Further to Quote an example I have to fetch data from the same table for 5 times in a specified date say

9.00 to 10.00
11.00 to 12.00
....
....
13.00 to 14.00 etc

Which dbms are you using? (When it comes to date/time many products are far from ANSI SQL compliant.) — jarlh, Sep 03 '18 at 11:32
[consider this](https://stackoverflow.com/a/353049/1132334), old but still valid — Cee McSharpface, Sep 03 '18 at 13:50

score 1 · Answer 1 · answered Sep 03 '18 at 13:18

1

You can also use the datepart() function to just extract the hour:

select datepart(hour, createdat), . . .
from . . .
where datepart(hour, createdat) between 9 and 13
group by datepart(hour, createdat)
order by datepart(hour, createdat);

answered Sep 03 '18 at 13:18

Gordon Linoff

1,198,228
53
572
709

score 0 · Answer 2 · answered Sep 03 '18 at 13:16

0

You can break Time part from DateTime field Using of below code.

select cast(CreatedDate as time) as TimePart
from your_Table_name

answered Sep 03 '18 at 13:16

nisha

21
2

score 0 · Accepted Answer · answered Sep 04 '18 at 04:23

I think this was very simple and can be done in very optimize and SARGABLE way.

Declare @input datetime='2018-09-04'

declare @starttime varchar(15)='13:00'
declare @endtime varchar(15)='14:00'

declare @StartDate datetime=@input+@starttime

declare @EndDate datetime=@input+@endtime

select * from 
table1 
where createdate>=@StartDate and createdate<=@EndDate

Fetching Records from the Table Based on specific Time Interval for the Same Date in SQL server

3 Answers3