CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Camden
Camden

Reputation: 3

SQL Server Time Data Type Error

I have a table with two time columns. I'm trying to compare these times and generate a specific output if they match.

So far I am unable to perform an equality comparison on these values, they all result in the following error:

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

Here is an example of my table and the different ways I've attempted to perform an equality comparison.

CREATE TABLE [dbo].[Time_Table](
[RowID] [int] NOT NULL,
[OpenTime] [time](7) NOT NULL,
[CloseTime] [time](7) NOT NULL)
GO

insert into Time_Table values (1,'01:00:00', '01:00:00')

CASE WHEN OpenTime = CloseTime THEN 'MATCH' ELSE OpenTime END
CASE WHEN CAST(OpenTime AS DATETIME) = CAST(CloseTime AS DATETIME) THEN 'MATCH' ELSE OpenTime END
CASE WHEN DATEDIFF(MINUTE,CAST(OpenTime AS DATETIME),CAST(CloseTime AS DATETIME)) = 0 THEN 'MATCH' ELSE OpenTime END

All of these statements result in the error I pasted above. Does SQL Server inherently store this data type as character data? As you can see in my DDL I am using the 'Time' Data type and not a character data type.

I'm wondering if this is due to the collation setting on this server? (Latin1_General_BIN)

Any help or suggestions on performing a time comparison / equality check with the 'Time' Data type would be greatly appreciated.

Upvotes: 0

Views: 113

Answers (1)

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

You have to cast time value into varchar first. Use Cast(OpenTime as varchar(8)) in all of your case statement

CASE WHEN OpenTime = CloseTime THEN 'MATCH' ELSE Cast(OpenTime as varchar(8)) END

The reason is that in a case construct,

select case Conditional expression THEN whatever this else that end

this and that have to be the same datatype.

Upvotes: 3

Related Questions