Compare Time value in table with 24 hour format in SQL Server

Last Reply 7 days ago By AjayV

Posted 7 days ago

Thanks for your solution.

How to compare Time(eg. 9:00AM - 9:30AM) value in table with Current Time in SQL

I have implemented this query but still there is a problem.

when i execute the query at 20:00.

it is still gives me output 

TimeSlot

06:30PM - 07:15PM

which is logically incorrect, cause the time is over the last slot was '06:30PM - 07:15PM' in table.

SELECT TimeSlot
FROM #temp1
WHERE CONVERT(TIME, SUBSTRING(TimeSlot,0,CHARINDEX('-',TimeSlot))) >  CONVERT(TIME,getutcdate())
OR CONVERT(TIME, SUBSTRING(TimeSlot,CHARINDEX('-',TimeSlot)+1,LEN(TimeSlot))) >  CONVERT(TIME,getutcdate())
You are viewing reply posted by: AjayV 7 days ago.
Posted 7 days ago

Hi pandeygolu4200,

We checked the query with different samples and query is working fine.

Please refer below sample.

 SQL

CREATE TABLE #temp1
(
     [EventlocationId] VARCHAR(50)                    
    ,[EventId] VARCHAR(50)                  
    ,[locationId] VARCHAR(50)                     
    ,[Timeslot] VARCHAR(50)           
    ,[IsBooked] VARCHAR(50)
    ,[CreatedBy] VARCHAR(50)
    ,[CreatedOn] VARCHAR(50)     
    ,[UpdatedBy] VARCHAR(50)
    ,[UpdatedOn] VARCHAR(50)
    ,[DeletedBy] VARCHAR(50)
    ,[DeletedOn] VARCHAR(50)
)
INSERT INTO #temp1 VALUES('80B46325-4DE7-478A-AEC8-2731159647BC','CCCBAB4C-4FBD-49A4-88B2-7B4DDB62D250','A1779C8C-A97A-458A-BF56-8649208DDF8A','9:00AM - 9:30AM','0','NULL','2023-01-19 07:17:40.327','NULL','NULL','NULL','NULL')
INSERT INTO #temp1 VALUES('963EBCAE-D4E6-40DE-A5EC-500BF39BA982','CCCBAB4C-4FBD-49A4-88B2-7B4DDB62D250','A1779C8C-A97A-458A-BF56-8649208DDF8A','9:30AM - 10:00AM','0','NULL','2023-01-19 07:17:40.327','NULL','NULL','NULL','NULL')
INSERT INTO #temp1 VALUES('4A639E0B-AE87-46A8-81B3-70F6FCC521FB','CCCBAB4C-4FBD-49A4-88B2-7B4DDB62D250','A1779C8C-A97A-458A-BF56-8649208DDF8A','10:00PM- 10:30PM','1','NULL','2023-01-19 07:17:40.327','NULL','2023-01-20  08:27:23.713','NULL','NULL')
INSERT INTO #temp1 VALUES('04F45ED4-0A33-417C-914F-C5055C165225','CCCBAB4C-4FBD-49A4-88B2-7B4DDB62D250','A1779C8C-A97A-458A-BF56-8649208DDF8A','20:00 - 20:30','0','NULL','2023-01-19 07:17:40.327','NULL','NULL','NULL','NULL')
INSERT INTO #temp1 VALUES('6F4F631A-4298-4FF7-BC74-7AEA8D0841B1','CCCBAB4C-4FBD-49A4-88B2-7B4DDB62D250','A1779C8C-A97A-458A-BF56-8649208DDF8A','15:30 - 16:30','0','NULL','2023-01-19 07:17:40.327','NULL','NULL','NULL','NULL')
INSERT INTO #temp1 VALUES('ECEAE410-BF1C-40AB-8985-972E200707EF','CCCBAB4C-4FBD-49A4-88B2-7B4DDB62D250','A1779C8C-A97A-458A-BF56-8649208DDF8A','06:30PM - 07:15PM','0','NULL','2023-01-19 07:17:40.327','NULL','NULL','NULL','NULL')
 
SELECT TimeSlot, getutcdate() CurrentTime
FROM #temp1
WHERE CONVERT(TIME, SUBSTRING(TimeSlot,0,CHARINDEX('-',TimeSlot))) >  CONVERT(TIME,getutcdate())
OR CONVERT(TIME, SUBSTRING(TimeSlot,CHARINDEX('-',TimeSlot)+1,LEN(TimeSlot))) >  CONVERT(TIME,getutcdate())

DROP TABLE #temp1

Output

TimeSlot CurrentTime
10:00PM- 10:30PM 2023-01-24 10:15:55
20:00 - 20:30 2023-01-24 10:15:55
15:30 - 16:30 2023-01-24 10:15:55
06:30PM - 07:15PM 2023-01-24 10:15:55