Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
New_at_this
New_at_this
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
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.
happycat59
happycat59
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3566 Visits: 3113
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).



kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2437 Visits: 2763
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/
Bhaskar.Shetty
Bhaskar.Shetty
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 Visits: 509
Why not use DateName function instead of using case conditions !!, as the datatype of column you are checking is datetime.
krypto69
krypto69
SSC Eights!
SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)

Group: General Forum Members
Points: 871 Visits: 2420
thanks kapil_kk



krypto69
krypto69
SSC Eights!
SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)SSC Eights! (871 reputation)

Group: General Forum Members
Points: 871 Visits: 2420
Great idea Bhaskar.Shetty



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search