Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

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

  • Saturday, February 02, 2013 3:48 AM
     
      Has Code
    Sign In to Vote
    0

    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

     

All Replies

  • Saturday, February 02, 2013 4:03 AM
     
     
    Sign In to Vote
    0

    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:10 AM
     
      Has Code
    Sign In to Vote
    0

    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:14 AM
     
     
    Sign In to Vote
    0
    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:20 AM
     
     
    Sign In to Vote
    0
    Not sure where its hanging, however I am still getting the same error.
     
  • Saturday, February 02, 2013 4:24 AM
     
     
    Sign In to Vote
    0

    Can I know how you define this column

    pvp.DateOfServiceFrom

    in your table?

    Many Thanks & Best Regards, Hua Min

     
  • Saturday, February 02, 2013 4:30 AM
     
     
    Sign In to Vote
    0
    DateOfServiceFrom =   datetime     
     
  • Saturday, February 02, 2013 4:47 AM
     
     
    Sign In to Vote
    0
    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:51 AM
     
     
    Sign In to Vote
    0

    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:54 AM
     
     Answered
    Sign In to Vote
    1
    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 5:01 AM
     
     
    Sign In to Vote
    0
    MY HERO! Thank you, Thank You!!!