CODEWITHSUNDEEP
sqlsql-server
Derek Bushiko
Derek Bushiko

Reputation: 3

How to get SQL results for specific time (not date)

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.

Upvotes: 0

Views: 89

Answers (2)

GuidoG
GuidoG

Reputation: 12014

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

COLUMN1 COLUMN2 COLUMN3 COLUMN4
11/03/2022 11:26:01 11:26:01.1233333 11:26:01.0000000 11:26:01

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

COLUMN1
2
3

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.

Upvotes: 1

Derek Bushiko
Derek Bushiko

Reputation: 3

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

DataGrip vs SqlServer

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

Upvotes: 0

Related Questions