MHarkess MHarkess - 1 year ago 89
SQL Question

Run-time error: Invalid parameter 1 specified for datepart - VBA - SQL

I'm running some code in Excel VBA which queries a database then brings the data into Excel for formatting.

It worked fine the last time I ran it (that old chestnut) but today I've come to run the monthly report and it is throwing up a datepart error as


Invalid parameter 1 specified for datepart


Here is the code:

StrQuery = "SELECT dbo_MANUFACTURER.COMPANY_NAME AS BRAND, dbo_COMPANY.COMPANY_NAME, dbo_AGENTS.SHORT_DESC AS AGENT, dbo_STOCK_SUB_TYPE.SHORT_DESC AS [STOCK TYPE], Replace(Replace(dbo_COMPANY.CURRENCY_ID,'110','EURO'),'1','GBP') AS [CURRENCY]," & _
"Sum(([Unit_Net]*[QTY_SOLD])-[DELIVERY_TOTAL]) AS [NET TOTAL], dbo_SORDER.DATE_CREATED, dbo_SORDER.SORDER_CODE FROM (((((dbo_COMPANY INNER JOIN dbo_SORDER ON dbo_COMPANY.COMPANY_ID = dbo_SORDER.COMPANY_ID) INNER JOIN " & _
"dbo_SORDER_ITEM ON dbo_SORDER.SORDER_ID = dbo_SORDER_ITEM.SORDER_ID) INNER JOIN dbo_STOCK ON dbo_SORDER_ITEM.STOCK_ID = dbo_STOCK.STOCK_ID) INNER JOIN dbo_STOCK_SUB_TYPE ON dbo_STOCK.SSTYPE_ID = dbo_STOCK_SUB_TYPE.SSTYPE_ID) INNER JOIN " & _
"dbo_AGENTS ON dbo_COMPANY.AGENT_ID = dbo_AGENTS.AGENT_ID) INNER JOIN dbo_MANUFACTURER ON dbo_STOCK.MANUF_ID = dbo_MANUFACTURER.MANUF_ID GROUP BY dbo_MANUFACTURER.COMPANY_NAME, dbo_COMPANY.COMPANY_NAME, dbo_AGENTS.SHORT_DESC, " & _
"dbo_STOCK_SUB_TYPE.SHORT_DESC, dbo_SORDER.DATE_CREATED, dbo_SORDER.SORDER_CODE, dbo_COMPANY.CURRENCY_ID, dbo_STOCK.MANUF_ID HAVING (((dbo_STOCK_SUB_TYPE.SHORT_DESC) <> " & "'EMBROIDERY'" & ") And ((Year([DATE_CREATED]) * 12 + " & _
"DatePart(" & "'m'" & ", [DATE_CREATED])) = Year(Date) * 12 + DatePart(" & "'m'" & ", Date) - 1)) ORDER BY dbo_COMPANY.COMPANY_NAME, dbo_SORDER.DATE_CREATED;"


Apologies for the mass of text on long lines.

As mentioned the code throws up the error: Invalid parameter 1 specified for date part. I've tried converting the dates as someone mentioned on a forum post but that hasn't been successful.

If someone has any ideas that would be amazing!

Answer Source

Simply try with the below query

SELECT DATEPART('m', GETDATE())

returns the

Invalid parameter 1 specified for datepart.

As per DATEPART description from MSDN, the datepart abbreviation can be m or mm for MONTH, no need of the single quote around the 'm'.

Remove the single quote will solve your problem.


So your working query will be:

SELECT dbo_MANUFACTURER.COMPANY_NAME AS BRAND, 
      dbo_COMPANY.COMPANY_NAME, dbo_AGENTS.SHORT_DESC AS AGENT, 
      dbo_STOCK_SUB_TYPE.SHORT_DESC AS [STOCK TYPE], 
      Replace(Replace(dbo_COMPANY.CURRENCY_ID,'110','EURO'),'1','GBP') AS [CURRENCY],
      Sum(([Unit_Net]*[QTY_SOLD])-[DELIVERY_TOTAL]) AS [NET TOTAL], 
      dbo_SORDER.DATE_CREATED, 
      dbo_SORDER.SORDER_CODE

FROM ((((
(dbo_COMPANY INNER JOIN dbo_SORDER ON dbo_COMPANY.COMPANY_ID = dbo_SORDER.COMPANY_ID) 
INNER JOIN dbo_SORDER_ITEM ON dbo_SORDER.SORDER_ID = dbo_SORDER_ITEM.SORDER_ID) 
INNER JOIN dbo_STOCK ON dbo_SORDER_ITEM.STOCK_ID = dbo_STOCK.STOCK_ID) 
INNER JOIN dbo_STOCK_SUB_TYPE ON dbo_STOCK.SSTYPE_ID = dbo_STOCK_SUB_TYPE.SSTYPE_ID) 
INNER JOIN dbo_AGENTS ON dbo_COMPANY.AGENT_ID = dbo_AGENTS.AGENT_ID) 
INNER JOIN dbo_MANUFACTURER ON dbo_STOCK.MANUF_ID = dbo_MANUFACTURER.MANUF_ID 

GROUP BY dbo_MANUFACTURER.COMPANY_NAME, 
         dbo_COMPANY.COMPANY_NAME, dbo_AGENTS.SHORT_DESC, 
         dbo_STOCK_SUB_TYPE.SHORT_DESC, 
         dbo_SORDER.DATE_CREATED, 
         dbo_SORDER.SORDER_CODE, 
         dbo_COMPANY.CURRENCY_ID, 
         dbo_STOCK.MANUF_ID 

HAVING (((dbo_STOCK_SUB_TYPE.SHORT_DESC) <> 'EMBROIDERY') 
        And ((Year([DATE_CREATED]) * 12 
              + DatePart(m, [DATE_CREATED])) = Year(Date) * 12 
              + DatePart(m, Date) - 1)) 

ORDER BY dbo_COMPANY.COMPANY_NAME, 
         dbo_SORDER.DATE_CREATED;