Solved

TSQL ERROR- is not a recognized dateadd option.

Posted on 2010-11-23
6
807 Views
Last Modified: 2012-05-10
Hi Experts! How do I correct this error? I want to join the PlanJoin to my table however I am getting the is not a recognized dateadd option error and I am not sure why. I am not attempting to use the dateadd option, just using the month to create a relationship.
SELECT     vReportdataCurrentPrior.extendedStandardCost, vReportdataCurrentPrior.businessEntity, vReportdataCurrentPrior.product, 
                      vReportdataCurrentPrior.site, vReportdataCurrentPrior.subProduct, SUM(vReportdataCurrentPrior.OrderAmount_LastYear) AS [Prior Bookings], 
                      SUM(vReportdataCurrentPrior.InvoiceAmount_LastYear) AS [Prior Sales]]], SUM(vReportdataCurrentPrior.OrderAmount_CurrentYear) 
                      AS [Current Bookings], SUM(vReportdataCurrentPrior.InvoiceAmount_CurrentYear) AS [Current Sales], ExchangeRates.ExchangeRate, 
                      EnergySites.EnergyOrg, vReportdataCurrentPrior.qty, EnergySites.SiteNumber, (DATEDIFF(DD, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, 
                      GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME), CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, DATEADD(PlanJoin.Month, 1, 
                      GETDATE())), DATEADD(PlanJoin.Month, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + 1) - (DATEDIFF(DD, DATEADD(DD, 
                      6 - (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME)) 
                      + @@DATEFIRST - 2) % 7, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME)), 
                      DATEADD(DD, - ((DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, DATEADD(PlanJoin.Month, 1, GETDATE())), 
                      DATEADD(PlanJoin.Month, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7), CAST(FLOOR(CAST(DATEADD(DD, 
                      - DATEPART(DD, DATEADD(PlanJoin.Month, 1, GETDATE())), DATEADD(PlanJoin.Month, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME))) - 1) 
                      / 7 * 2 - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME)) 
                      + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, 
                      - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) 
                      % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS MonthWorkingDays, (DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD, 
                      6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1) 
                      / 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end) 
                      + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, vReportdataCurrentPrior.transyear
FROM         vReportdataCurrentPrior INNER JOIN
                      ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND 
                      vReportdataCurrentPrior.transyear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN
                      EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber INNER JOIN
                      PlanJoin ON vReportdataCurrentPrior.subProduct = PlanJoin.SubProduct
WHERE     (EnergySites.EnergyOrg = @energyorg)
GROUP BY vReportdataCurrentPrior.extendedStandardCost, vReportdataCurrentPrior.businessEntity, vReportdataCurrentPrior.product, 
                      vReportdataCurrentPrior.site, vReportdataCurrentPrior.subProduct, vReportdataCurrentPrior.transyear, ExchangeRates.ExchangeRate, 
                      EnergySites.EnergyOrg, vReportdataCurrentPrior.qty, EnergySites.SiteNumber, vReportdataCurrentPrior.transDate
HAVING      (vReportdataCurrentPrior.transDate BETWEEN @start AND @end OR
                      vReportdataCurrentPrior.transDate BETWEEN DATEADD(yy, - 1, @start) AND DATEADD(yy, - 1, @end)) AND 
                      (vReportdataCurrentPrior.businessEntity = 'energy')
ORDER BY vReportdataCurrentPrior.transDate DESC

Open in new window

Error.doc
0
Comment
Question by:SPLady
  • 3
  • 3
6 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34200398
You have
DATEADD(PlanJoin.Month
about 4 times in there.
The first part of DateAdd only allows things like month, m, mm, year, y,yy etc, not a column
0
 
LVL 1

Author Comment

by:SPLady
ID: 34200462
Thank you @ cyberkiwi


Umm am I missing something......I havn't used dateadd(PlanJoin.Month)..  I dont want to use dateadd for planjoin, I just want to use that column to develop a relationship.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34200569
The query is horrible to parse, so I rewrote and reformatted it..
I also moved your HAVING clause to WHERE since it will perform better.
The HAVING clause is used for AGGREGATES, you are testing against the fields in the table, which fits in plain WHERE clause.
--declare @start datetime
--declare @end datetime
--declare @energyorg int

SELECT vreportdatacurrentprior.extendedstandardcost,
       vreportdatacurrentprior.businessentity,
       vreportdatacurrentprior.product,
       vreportdatacurrentprior.site,
       vreportdatacurrentprior.subproduct,
       SUM(vreportdatacurrentprior.orderamount_lastyear)      AS [Prior Bookings],
       SUM(vreportdatacurrentprior.invoiceamount_lastyear)    AS [Prior Sales] ,
       SUM(vreportdatacurrentprior.orderamount_currentyear)   AS [Current Bookings],
       SUM(vreportdatacurrentprior.invoiceamount_currentyear) AS [Current Sales],
       exchangerates.exchangerate,
       energysites.energyorg,
       vreportdatacurrentprior.qty,
       energysites.sitenumber,
       MWD.Days AS monthworkingdays,
       WD.Days AS workingdays,
       vreportdatacurrentprior.transyear
FROM   vreportdatacurrentprior
CROSS JOIN (select count(*) days
                       from master..spt_values
                       where type='P'  -- series from 0-1024
                        and number between 0 and 60  -- max of 30 days from start
                        and month(number+GETDATE()-30) = month(GETDATE())  -- within start month
                        and (datepart(dw,GETDATE()-30 + number)+@@datefirst-2)%7+1 < 6) MWD
CROSS JOIN (select count(*) days
                       from master..spt_values
                       where type='P'  -- series from 0-1024
                        and number between 0 and @end-@start  -- range from start to end
                        and (datepart(dw,@start + number)+@@datefirst-2)%7+1 < 6) WD
INNER JOIN exchangerates ON MONTH(vreportdatacurrentprior.transdate) = exchangerates.xratemonth
    AND vreportdatacurrentprior.transyear = exchangerates.xrateyear
    AND vreportdatacurrentprior.currencycode = exchangerates.currency
INNER JOIN energysites ON vreportdatacurrentprior.site = energysites.sitenumber
INNER JOIN planjoin ON vreportdatacurrentprior.subproduct = planjoin.subproduct
WHERE  ( energysites.energyorg = @energyorg )
AND ( vreportdatacurrentprior.transdate BETWEEN @start AND @end
          OR vreportdatacurrentprior.transdate BETWEEN
             Dateadd(yy, -1, @start) AND Dateadd(yy, -1, @end) )
       AND ( vreportdatacurrentprior.businessentity = 'energy' )
GROUP  BY vreportdatacurrentprior.extendedstandardcost,
          vreportdatacurrentprior.businessentity,
          vreportdatacurrentprior.product,
          vreportdatacurrentprior.site,
          vreportdatacurrentprior.subproduct,
          vreportdatacurrentprior.transyear,
          exchangerates.exchangerate,
          energysites.energyorg,
          vreportdatacurrentprior.qty,
          energysites.sitenumber,
          vreportdatacurrentprior.transdate
ORDER  BY vreportdatacurrentprior.transdate DESC

Open in new window

0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34200577
Well if you look at your query in the question, you have 6 cases of dateadd(planjoin.month ...
I think you mean this relationship

INNER JOIN planjoin ON vreportdatacurrentprior.subproduct = planjoin.subproduct

How does planjoin.month fit in there?
You will see that the new query is easier to understand and work with.
0
 
LVL 1

Author Comment

by:SPLady
ID: 34200702
Thank you soo much @cyberkiwi how did those change lol.... I guess when I added the table... thats crazy --6 cases of dateadd(planjoin.month ...
0
 
LVL 1

Author Closing Comment

by:SPLady
ID: 34200792
Wow that is BEAUTIFUL code !
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql query 12 72
Query to return total 6 17
Gettg error - Please help Msg 252, Level 16, State 1, Line 1 3 28
Need help how to find where my error is in UFD 6 25
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now