Argument data type datetime is invalid for argument 1 of substring function

  • Hi,

    I have this query that works fine.

    ;WITH cte AS

    (

    select * from (SELECT

    distinct pehPErcontrol

    ,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2)

    when '01' then 'January'

    when '02' then 'February'

    when '03' then 'March'

    when '04' then 'April'

    when '05' then 'May'

    when '06' then 'June'

    when '07' then 'July'

    when '08' then 'August'

    when '09' then 'September'

    when '10' then 'October'

    when '11' then 'November'

    when '12' then 'December'

    end as [UltiMonth],

    rtrim(eepNameLast) +

    ', ' + rtrim(eepNameFirst) +

    ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name,

    eepNameLast AS [Last Name],

    IsNull(eepNameSuffix,'') AS [Suffix],

    eepNameFirst AS [First Name],

    IsNull(eepNameMiddle,'') AS [Middle Name],

    pehCurAmt AS [Current Amount],

    pehCurHrs AS [Current Hours],

    pehCoID AS [Company ID],

    pehEEID AS [EE ID],

    pehEmpNo AS [Emp No],

    pehLocation AS [Location],

    pehJobCode AS [Job Code],

    pehOrgLvl1 AS [Org Level 1],

    pehOrgLvl2 AS [Org Level 2],

    pehOrgLvl3 AS [Org Level 3],

    pehOrgLvl4 AS [Org Level 4],

    pehPayGroup AS [Pay Group],

    pehProject AS [Project],

    pehShfShiftAmt AS [Shift Amount],

    pehearncode AS [Earn Code],

    pehIsVoided AS [IS Voided],

    pehIsVoidingRecord AS [Voiding Record],

    pehIsOvertime AS [Is Overtime]

    FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID

    join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013]

    ON [orglevel] = pehOrgLvl2) t

    right outer join

    WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob

    on t.[UltiMonth] = ob.[month] and orglevel = [org level 2]

    where pehPerControl > '201301011'

    AND [EARN CODE] = '0002'

    AND [IS Voided] <> 'Y'

    AND [Voiding Record] <> 'Y'

    AND [Is Overtime] = 'Y'

    AND [org level 2] like '%ZSW'

    --AND [ULTIMONTH] = 'FEBRUARY'

    --ORDER BY pehPerControl

    )

    SELECT *

    ,SUM([Current Amount])

    OVER (PARTITION BY [Emp No],[UltiMonth]) AS [Monthly Amount]

    FROM cte

    Turns out I used the wrong field (pehPerControl) which was formatted like - 201301111

    I need to now use 'pehPaydate' (because there can be more than one) which is formatted like - 2013-01-13 00:00:00.000

    So, If I change the all columns in the above query to use 'pehPaydate' now I get an error:

    Argument data type datetime is invalid for argument 1 of substring function.

    Pretty sure I need to use a cast or convert in my substring - but can't get the syntax right.

  • Since 'pehPaydate' has a datatype of datetime, you can use the DATENAME function to return the month name - i.e. you won't need the case statement anymore. So the case statement become DateName (Month, pehPaydate).

    e.g. SELECT DateName (Month, getdate()) returns the string "August" (the date today is 22nd August 2013).

  • I have changed the query as per my understanding of the probleem...

    Please let me know if you faced any issue -

    ;WITH cte AS

    (

    select * from (SELECT

    distinct pehPaydate

    ,case Datepart(mm,pehPaydate)

    --eft(substring(pehPErcontrol,5,len(pehPErcontrol)),2)

    when '01' then 'January'

    when '02' then 'February'

    when '03' then 'March'

    when '04' then 'April'

    when '05' then 'May'

    when '06' then 'June'

    when '07' then 'July'

    when '08' then 'August'

    when '09' then 'September'

    when '10' then 'October'

    when '11' then 'November'

    when '12' then 'December'

    end as [UltiMonth],

    rtrim(eepNameLast) +

    ', ' + rtrim(eepNameFirst) +

    ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name,

    eepNameLast AS [Last Name],

    IsNull(eepNameSuffix,'') AS [Suffix],

    eepNameFirst AS [First Name],

    IsNull(eepNameMiddle,'') AS [Middle Name],

    pehCurAmt AS [Current Amount],

    pehCurHrs AS [Current Hours],

    pehCoID AS [Company ID],

    pehEEID AS [EE ID],

    pehEmpNo AS [Emp No],

    pehLocation AS [Location],

    pehJobCode AS [Job Code],

    pehOrgLvl1 AS [Org Level 1],

    pehOrgLvl2 AS [Org Level 2],

    pehOrgLvl3 AS [Org Level 3],

    pehOrgLvl4 AS [Org Level 4],

    pehPayGroup AS [Pay Group],

    pehProject AS [Project],

    pehShfShiftAmt AS [Shift Amount],

    pehearncode AS [Earn Code],

    pehIsVoided AS [IS Voided],

    pehIsVoidingRecord AS [Voiding Record],

    pehIsOvertime AS [Is Overtime]

    FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID

    join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013]

    ON [orglevel] = pehOrgLvl2) t

    right outer join

    WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob

    on t.[UltiMonth] = ob.[month] and orglevel = [org level 2]

    where pehPaydate > '201301011'

    AND [EARN CODE] = '0002'

    AND [IS Voided] <> 'Y'

    AND [Voiding Record] <> 'Y'

    AND [Is Overtime] = 'Y'

    AND [org level 2] like '%ZSW'

    --AND [ULTIMONTH] = 'FEBRUARY'

    --ORDER BY pehPerControl

    )

    SELECT *

    ,SUM([Current Amount])

    OVER (PARTITION BY [Emp No],[UltiMonth]) AS [Monthly Amount]

    FROM cte

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Why not use DateName function instead of using case conditions !!, as the datatype of column you are checking is datetime.

  • thanks kapil_kk

  • Great idea Bhaskar.Shetty

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply