On the assumption that your named these two columns DaysSince1970
and HoursSinceMidnight
...
Then you can build an inferred datetime
(not timestamp
) using the expression
dateadd(hh,HoursSinceMidnight,dateadd(dd,DaysSince1970,'1-jan-1970'))
However, this a calculated column and is going to be terrible for indexed searches, so you will need some logic like.
DECLARE @DaysSince1970 int
, @HoursSinceMidnight int
, @SearchDateTime datetime
SELECT @DaysSince1970 = datediff(dd,'1-Jan-1970',@SearchDateTime)
, @HoursSinceMidnight = datepart(hh,@SearchDateTime)
-- search your table
SELECT *
FROM tablename
WHERE DaysSince1970 = @DaysSince1970
AND HoursSinceMidnight = @HoursSinceMidnight
Ideally your table will need and index on DaysSince1970
, HoursSinceMidnight
.
Finding rows in a range will be a simple extension of this, but remember that once you are checking across multiple days, don't check a range of 'raw' hours, just check the day range and then use the first method that I gave you to check the date/time range.