Hi
i am trying to compare date and show the message, but when i execute below sql query i am getting an error.
Msg 50000, Level 16, State 3, Procedure dbo.uspLinkExpirycheck, Line 40 [Batch Start Line 9] The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Completion time: 2022-04-25T10:31:21.8754227+05:30
start date value=2022-04-05 08:45:03.437
endate date value ==getutcdate() 2022-04-25 07:49:17.633
USE [PatientManagementDB]
GO
/****** Object: StoredProcedure [dbo].[uspLinkExpirycheck] Script Date: 25-04-2022 10:27:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec [dbo].[uspLinkExpirycheck] '1BB26072-E551-4BC9-885C-9E337978AA5E'
ALTER PROCEDURE [dbo].[uspLinkExpirycheck] (
@MedicalInfoId VARCHAR(50)
--,@otp NVARCHAR(10)
)
AS
BEGIN
DECLARE @Status BIT = 0;
DECLARE @Msg VARCHAR(500);
DECLARE @StartDate datetime, @EndDate datetime,@Datedif DATETIME
SET NOCOUNT ON;
BEGIN TRY
IF (EXISTS (SELECT 1 FROM [dbo].[tblSendMedicalInfo] WITH(NOLOCK) WHERE MedicalInfoId = @MedicalInfoId))
BEGIN
set @startDate=( select [CreatedOn] from [tblSendMedicalInfo] where MedicalInfoId=@MedicalInfoId )
set @EndDate= Getutcdate();
SET @Datedif=(select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss])
IF(@Datedif< '22:59:59')
SELECT 1 AS STATUS ,'LINK VERIFIED!'
ELSE
SELECT 0 AS STATUS ,'Your Link has Expired Please check with Patient Name for the New Link!'
END
ELSE
BEGIN
SELECT 0 AS STATUS ,'Details Not Found'
END
END TRY
BEGIN CATCH
SET @Msg = Error_message();
DECLARE @ErrorSeverity INT = Error_severity();
DECLARE @ErrorState INT = Error_state();
RAISERROR (
@Msg
,@ErrorSeverity
,@ErrorState
);
END CATCH
END