none
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

    Saturday, February 02, 2013 3:48 AM

Answers

  • 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

    • Marked as answer by Jeffs1977 Saturday, February 02, 2013 5:05 AM
    Saturday, February 02, 2013 4:54 AM

All replies

  • 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

    Saturday, February 02, 2013 4:03 AM
  • 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

    Saturday, February 02, 2013 4:10 AM
  • 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

    Saturday, February 02, 2013 4:14 AM
  • Not sure where its hanging, however I am still getting the same error.
    Saturday, February 02, 2013 4:20 AM
  • Can I know how you define this column

    pvp.DateOfServiceFrom

    in your table?


    Many Thanks & Best Regards, Hua Min

    Saturday, February 02, 2013 4:24 AM
  • DateOfServiceFrom =   datetime     
    Saturday, February 02, 2013 4:30 AM
  • 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

    Saturday, February 02, 2013 4:47 AM
  • 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.

    Saturday, February 02, 2013 4:51 AM
  • 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

    • Marked as answer by Jeffs1977 Saturday, February 02, 2013 5:05 AM
    Saturday, February 02, 2013 4:54 AM
  • MY HERO! Thank you, Thank You!!!
    Saturday, February 02, 2013 5:01 AM