The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Question

In my report, (which is close to 400 lines ) I use this SQL function:

dbo.cusfn_GlobalDates(pv.PatientVisitId, pp.PatientProfileId, pv.Visit) AS GlobalData

I am getting the following SQL Error:

Msg 242, Level 16, State 3, Line 256
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

My SQL Function:

USE [demo]
GO
/****** Object:  UserDefinedFunction [dbo].[cusfn_GlobalDates]    Script Date: 02/01/2013 20:47:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[cusfn_GlobalDates]
    (
      @PatientVisitId INT ,
      @PatientProfileId INT ,
      @ApptStart DATETIME 
    )
RETURNS VARCHAR(8000)
AS 
    BEGIN 
 
        DECLARE
            @GlobalStartDate DATETIME ,
            @GlobalEndDate DATETIME ,
            @GlobalLength INT ,
            @GlobalCPTCode VARCHAR(10) ,
            @Result VARCHAR(8000); 
  
        SET @Result = '';
        DECLARE MyCursor CURSOR LOCAL FAST_FORWARD
        FOR
            SELECT
                pvp.DateOfServiceFrom ,
                DATEADD(day , pvp.GPDays , pvp.DateOfServiceFrom) ,
                pvp.GPDays ,
                ISNULL(pvp.CPTCode , pvp.Code)
            FROM
                PatientVisitProcs pvp
                JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
            WHERE
                pv.PatientVisitID = @PatientVisitId
                AND ISNULL(GPDays , 0) > 0
                AND pv.PatientProfileId = @PatientProfileId
                AND DATEADD(day , pvp.GPDays , pvp.DateOfServiceFrom) > @ApptStart
                AND ISNULL(pvp.Voided , 0) = 0
            ORDER BY
                DATEADD(day , pvp.GPDays , pvp.DateOfServiceFrom)  DESC;
        OPEN MyCursor  
  
        FETCH NEXT FROM MyCursor
  INTO @GlobalStartDate , @GlobalEndDate , @GlobalLength , @GlobalCPTCode ; 
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                SET @Result = @Result + @GlobalStartDate + ' - ' + @GlobalEndDate + ' : ' + @GlobalLength + ' / ' + @GlobalCPTCode + CHAR(13) + CHAR(10);
                FETCH NEXT FROM MyCursor
    INTO @GlobalStartDate , @GlobalEndDate , @GlobalLength , @GlobalCPTCode ; 
            END;
        CLOSE MyCursor;
        DEALLOCATE MyCursor;
        RETURN @Result;
    END 
GO

Answer 1

Please try to adjust the 1st 2 columns in the cursor to be

cast(pvp.DateOfServiceFrom as datetime) DateOfServiceFrom,
DATEADD(day , pvp.GPDays , cast(pvp.DateOfServiceFrom as datetime)) ,

---

Many Thanks & Best Regards, Hua Min

Answer 2

I changed my function per your recommendation and unfortunately, I am getting the same pesky error.

USE [demo]
GO
/****** Object:  UserDefinedFunction [dbo].[cusfn_GlobalDates]    Script Date: 02/01/2013 20:47:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[cusfn_GlobalDates]
    (
      @PatientVisitId INT ,
      @PatientProfileId INT ,
      @ApptStart DATETIME 
    )
RETURNS VARCHAR(8000)
AS 
    BEGIN 
 
        DECLARE
            @GlobalStartDate DATETIME ,
            @GlobalEndDate DATETIME ,
            @GlobalLength INT ,
            @GlobalCPTCode VARCHAR(10) ,
            @Result VARCHAR(8000); 
  
        SET @Result = '';
        DECLARE MyCursor CURSOR LOCAL FAST_FORWARD
        FOR
            SELECT
                CAST(pvp.DateOfServiceFrom AS DATETIME) ,
                DATEADD(day , pvp.GPDays , CAST(pvp.DateOfServiceFrom AS DATETIME)) ,
                pvp.GPDays ,
                ISNULL(pvp.CPTCode , pvp.Code)
            FROM
                PatientVisitProcs pvp
                JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
            WHERE
                pv.PatientVisitID = @PatientVisitId
                AND ISNULL(GPDays , 0) > 0
                AND pv.PatientProfileId = @PatientProfileId
                AND DATEADD(day , pvp.GPDays , pvp.DateOfServiceFrom) > @ApptStart
                AND ISNULL(pvp.Voided , 0) = 0
            ORDER BY
                DATEADD(day , pvp.GPDays , pvp.DateOfServiceFrom) DESC;
        OPEN MyCursor  
  
        FETCH NEXT FROM MyCursor
  INTO @GlobalStartDate , @GlobalEndDate , @GlobalLength , @GlobalCPTCode; 
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                SET @Result = @Result + @GlobalStartDate + ' - ' + @GlobalEndDate + ' : ' + @GlobalLength + ' / ' + @GlobalCPTCode + CHAR(13) + CHAR(10);
                FETCH NEXT FROM MyCursor
    INTO @GlobalStartDate , @GlobalEndDate , @GlobalLength , @GlobalCPTCode; 
            END;
        CLOSE MyCursor;
        DEALLOCATE MyCursor;
        RETURN @Result;
    END 
GO

Answer 3

Try to change this cursor part to be
        DECLARE MyCursor CURSOR LOCAL FAST_FORWARD
        FOR
            SELECT
                CAST(pvp.DateOfServiceFrom AS DATETIME) ,
                DATEADD(day , pvp.GPDays , CAST(pvp.DateOfServiceFrom AS DATETIME)) ,
                pvp.GPDays ,
                ISNULL(pvp.CPTCode , pvp.Code)
            FROM
                PatientVisitProcs pvp
                JOIN PatientVisit pv ON pvp.PatientVisitId = pv.PatientVisitId
            WHERE
                pv.PatientVisitID = @PatientVisitId
                AND ISNULL(GPDays , 0) > 0
                AND pv.PatientProfileId = @PatientProfileId
                AND DATEADD(day , pvp.GPDays , CAST(pvp.DateOfServiceFrom AS DATETIME)) > @ApptStart
                AND ISNULL(pvp.Voided , 0) = 0
            ORDER BY
                DATEADD(day , pvp.GPDays , CAST(pvp.DateOfServiceFrom AS DATETIME)) DESC;
        OPEN MyCursor 

---

Many Thanks & Best Regards, Hua Min

Answer 4

Not sure where its hanging, however I am still getting the same error.

Answer 5

Can I know how you define this column

pvp.DateOfServiceFrom

in your table?

---

Many Thanks & Best Regards, Hua Min

Answer 6

DateOfServiceFrom =   datetime     

Answer 7

Please restore the previous 2 changes suggested by me and only change this line to be
SET @Result = @Result + convert(varchar,@GlobalStartDate,107) + ' - ' + convert(varchar,@GlobalEndDate,107) + ' : ' + @GlobalLength + ' / ' + @GlobalCPTCode + CHAR(13) + CHAR(10);

---

Many Thanks & Best Regards, Hua Min

Answer 8

Getting closer -

my query throws this now:

Msg 245, Level 16, State 1, Line 256

Conversion failed when converting the varchar value 'Jun 19, 2006 - Sep 17, 2006 : ' to data type int.

Answer 9

Then use this
SET @Result = @Result + convert(varchar,@GlobalStartDate,107) + ' - ' + convert(varchar,@GlobalEndDate,107) + ' : ' + ltrim(rtrim(cast(@GlobalLength as varchar))) + ' / ' + @GlobalCPTCode + CHAR(13) + CHAR(10);

---

Many Thanks & Best Regards, Hua Min

Answer 10

MY HERO! Thank you, Thank You!!!