Msg 241, Level 16, State 1, Procedure dbo.AttendanceRecords, Line 118 [Batch Start Line 2] Conversion failed when converting date and/or time from character string.

Question

USE [AttendanceManagementSystem]

GO

/****** Object: StoredProcedure [dbo].[usp_GenerateAttendanceRecord] Script Date: 19-06-2025 09:50:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[usp_GenerateAttendanceRecord]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @StartDate DATE = DATEFROMPARTS(YEAR(GETDATE()), 5, 1);

DECLARE @EndDate DATE = EOMONTH(@StartDate);

DECLARE @CurrentDate DATE = @StartDate;

WHILE @CurrentDate <= @EndDate

BEGIN

	EXEC [dbo].[AttendanceRecords] @CurrentDate;

	SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);

END

END;

---

ALTER PROCEDURE [dbo].[AttendanceRecords]

@AttendanceDate DATE = NULL

AS

BEGIN

SET NOCOUNT ON;

------------------------------------------------------------------

-- 1. Define the attendance date (yesterday) and related values

------------------------------------------------------------------

IF @AttendanceDate IS NULL

BEGIN

    SET @AttendanceDate = DATEADD(DAY, -1, CAST(GETDATE() AS DATE));

END

DECLARE @LeaveDate DATE = @AttendanceDate;

-- Temporary table to store all transactions for break calculations

CREATE TABLE #AllTransactions (

    StaffId INT,

    TrOpName VARCHAR(10),

	TrIpAddress VARCHAR(50),

    TrTime DATETIME

);

------------------------------------------------------------------

-- 5. Collect all staff transactions for the day to use later for break calculations

------------------------------------------------------------------

INSERT INTO #AllTransactions (StaffId, TrOpName, TrIpAddress, TrTime)

SELECT 

    s.Id,

    st.Tr_OpName,

	st.Tr_IPAddress,

    TRY_CONVERT(DATETIME, CONVERT(VARCHAR, st.Tr_Date, 23) + ' ' + CONVERT(VARCHAR, st.Tr_Time, 108))

FROM StaffCreation s

JOIN ATRAK.DBO.SmaxTransaction st ON s.StaffId = st.Tr_ChId COLLATE SQL_Latin1_General_CP1_CI_AS

WHERE CONVERT(DATE, st.Tr_Date) = @AttendanceDate AND s.IsActive = 1

ORDER BY s.Id, TRY_CONVERT(DATETIME, CONVERT(VARCHAR, st.Tr_Date, 23) + ' ' + CONVERT(VARCHAR, st.Tr_Time, 108));

Using this stored procedure in where condition WHERE CONVERT(DATE, st.Tr_Date) = @AttendanceDate i am getting below error.

Msg 241, Level 16, State 1, Procedure dbo.AttendanceRecords, Line 118 [Batch Start Line 2] Conversion failed when converting date and/or time from character string.

Answer 1

Conversion failed when converting date and/or time from character string.

Then you have string data, which is an invalid date and so not convertable. Use TRY_CONVERT instead.

Answer 2

SELECT *
FROM  ATRAK.DBO.SmaxTransaction
WHERE Tr_Date IS NOT NULL
  AND try_convert(date, Tr_Date) IS NULL

will reveal all rows with dates that SQL Server is not able to convert. Once you have seen some samples, you can start thinking of the next step. If it just pure garbage like Kilroy was here, you can change the procedure to use TRY_CONVERT. But it may also be that there are legit dates, but which SQL Server is not able to interpret with your current dateformat settings.