Azure SQL: Error converting data type nvarchar to bigint

Question

A failing piece of code extracted from the procedure:

DECLARE @row UNIQUEIDENTIFIER
DECLARE @count BIGINT
DECLARE @deleteToDate BIGINT
DECLARE @deleteToDateYesterday BIGINT
DECLARE @DaysMiliseconds BIGINT
DECLARE @MilisecondsYesterday BIGINT
DECLARE @Time DATETIME

SET @Time = SYSDATETIME()
SET @count = 0

-- The number 365 is included here because it is a procedure parameter - you can ignore it. If I don't use CAST, I get "Arithmetic overflow error converting expression to data type int."
SET @DaysMiliseconds = CAST(365 AS BIGINT) * 24 * 60 * 60 * 1000
SET @MilisecondsYesterday = 1.3 * 24 * 60 * 60 * 1000

SET @deleteToDate = (SELECT DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', SYSDATETIME()) - @DaysMiliseconds Milliseconds)
SET @deleteToDateYesterday = (SELECT DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', SYSDATETIME()) - @MilisecondsYesterday Milliseconds)

SELECT id 
FROM [x-dev].master 
WHERE (validUntil < @deleteToDateYesterday OR validUntil IS NULL) 
  AND updatedAt < @deleteToDate;

The result is the following error

Msg 8114, Level 16, State 5, Line 18
Error converting data type nvarchar to bigint.

SQL version:

Microsoft SQL Azure (RTM) - 12.0.2000.8   Nov  2 2023 01:40:17   Copyright (C) 2022 Microsoft Corporation 

I would be very grateful for any help.

Answer 1

Try using TRY_CAST:

SELECT id 
FROM [x-dev].master 
WHERE (TRY_CAST(validUntil as BIGINT) < @deleteToDateYesterday OR validUntil IS NULL) 
  AND TRY_CAST(updatedAt as BIGINT) < @deleteToDate;