Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    8

    Unanswered: Stored Procedure Error ( conversion of a char data type)

    Hi all,
    im new to sql and im getting this error :The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. on my stored procedure.

    Much appreciated if someone could give me a hand

    USE [PMP]
    GO
    /****** Object: StoredProcedure [dbo].[PMPP_Employee_Key_Exception_Report] Script Date: 03/06/2014 12:03:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO


    -- EXECUTE [PMPP_Employee_Key_Exception_Report] 'PRIME'
    -- EXECUTE [PMPP_Employee_Key_Exception_Report] 'RECRUIT'
    -- EXECUTE [PMPP_Employee_Key_Exception_Report] 'PREMIER'

    ALTER Procedure [dbo].[PMPP_Employee_Key_Exception_Report]
    (
    @Company VARCHAR(10)
    )
    AS
    BEGIN

    DECLARE @Week_End_date DATETIME

    SELECT
    @Week_End_date = MAX(Week_End_date)
    FROM
    PMP.dbo.PMPT_rpm_tands WITH(NOLOCK)
    WHERE
    Company = @Company
    AND WEEK_END_DATE < GETDATE()

    PRINT @Week_End_date

    SELECT
    UPPER(C.COMPANY) AS COMPANY,
    C.BRANCH AS BRANCH_CODE,
    ISNULL(LEFT(B.sdesc,10), X.sldata) as BRANCH,
    C.EMPLOYEE_NO,
    C.FORENAMES,
    C.SURNAME,
    CONVERT(VARCHAR(11), CONVERT(DATETIME, C.DOB), 106) AS DOB,
    CONVERT(VARCHAR(11), CONVERT(DATETIME, C.DOE), 106) AS DOE,
    --C.DOE,
    C.NI_NO,
    PMP.dbo.fn_GetAge(C.DOB, GETDATE()) AS Age,
    CASE
    WHEN dbo.fn_GetAge(C.DOB, GETDATE()) < 16 OR dbo.fn_GetAge(C.DOB, GETDATE()) > 75 THEN 'N'
    ELSE 'Y'
    END AS DOB_OK,
    CASE
    WHEN ISNULL(LTRIM(RTRIM(C.DOE)), '') = '' OR ISNULL(LTRIM(RTRIM(C.DOE)), '') > GETDATE() THEN 'N'
    ELSE 'Y'
    END AS DOE_OK,
    CASE
    WHEN ISNULL(LTRIM(RTRIM(C.NI_NO)), '') = '' OR ISNULL(LTRIM(RTRIM(C.NI_NO)), '') LIKE 'TN%' OR ISNULL(LTRIM(RTRIM(C.NI_NO)), '') LIKE 'NONE%' THEN 'N'
    ELSE 'Y'
    END AS NI_NO_OK,
    '' AS Comment
    FROM
    PMP.dbo.PMPT_RPM_CANDIDATE C WITH(NOLOCK)
    LEFT JOIN CAS_Data.dbo.CAST_Cost_Code_nccodes B WITH(NOLOCK)
    ON B.scoy = C.COMPANY AND B.sscode = C.BRANCH AND B.sposn = 'C'
    LEFT OUTER JOIN cas_data..cast_cost_code_Lookup_xplinkfl X WITH(NOLOCK)
    ON X.slBrand = 'PRIME' AND X.slkey = LEFT(C.BRANCH, 3)
    WHERE
    C.COMPANY = @Company
    AND C.EMPLOYEE_NO IN (SELECT EMPLOYEE_NO FROM PMP.dbo.pmpt_rpm_timesheet WITH(NOLOCK) WHERE company = @Company AND week_end_Date = @Week_End_date)
    AND (
    dbo.fn_GetAge(C.DOB, GETDATE()) < 16 -- Age less than 16
    OR dbo.fn_GetAge(C.DOB, GETDATE()) > 75 -- Age less than 75
    OR ISNULL(LTRIM(RTRIM(C.DOE)), '') = '' -- DOE is blank or null
    OR ISNULL(LTRIM(RTRIM(C.DOE)), '') > GETDATE() -- DOE is in future
    OR ISNULL(LTRIM(RTRIM(C.NI_NO)), '') = '' -- NI_NO is blank or null
    OR ISNULL(LTRIM(RTRIM(C.NI_NO)), '') LIKE 'TN%' -- NI_NO starts with TN
    OR ISNULL(LTRIM(RTRIM(C.NI_NO)), '') LIKE 'NONE%' -- NI_NO starts with NONE
    )
    ORDER BY
    DOB_OK, DOE_OK, NI_NO_OK, BRANCH,Age, C.DOE, C.NI_NO, C.EMPLOYEE_NO
    END

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without either the complete error message or your schema (CREATE TABLE statements), all we can do is guess and give you suggestions.

    Check the places where you assign a character column or expression to a DATETIME. If that doesn't turn up the problem, check for the places where you compare a character column or expression to a DATTIME.

    If neither of those turn up the problem, please post the CREATE TABLE statements and some sample data that will produce the problem. It doesn't need to be real data, but it does need to produce the problem you'd like us to help you solve.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2014
    Posts
    8

    Full Error Message

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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Any rows that are returned by running:
    Code:
    SELECT DOB, DOE, *
       FROM PMP.dbo.PMPT_RPM_CANDIDATE
       WHERE 0 = IsDate(DOB)
          OR 0 = IsDate(DOE)
    ...will cause problems for your posted procedure.

    There may be other problems, but this should be a start.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •