I want to search for a specific time across days.

I have a view where I convert DATETIME to time like this cast(dateRecorded AS TIME) AS TME

select * from myView where TME = '08:44:43' - no results

select * from myView where TME > '08:44:43' - I get results with 08:44:43 time

But I need the exact time here, not everything after that date.

CodePudding user response：

The issue is that DataGrip crops out milliseconds by default, without a warning.

Converting to time like this got my desired result CONVERT(TIME(0), [dateRecorded]) AS TME

CodePudding user response：

The problem will be the miliseconds that are present in datatype DateTime

declare @datetime datetime = '20220311 11:26:01:123'

declare @time_1 time = convert(time, @datetime)
declare @time_2 time = convert(time, convert(datetime2(0), @datetime))
declare @time_3 time(0) = convert(time, @datetime)

now if I select all these variables

select  @datetime, @time_1, @time_2, @time_3

I get this

As you can see, SSMS will not show the miliseconds to you in the first column, but they are still there

So we need use the time variable that was converted from datetime2(0) or from time(0)

select 1 where @time_1 = '11:26:01'
union all 
select 2 where @time_2 = '11:26:01'
union all 
select 3 where @time_3 = '11:26:01'

This will result in

the value in @time_1 is not found, because it still has milliseconds, and thus is not equal to '11:26:01'

If it is possible for you to alter the datetime columns into datetime2(0) then your problem would be fixed without changing your query, otherwise you will have to use the convert like in my examples.