I am attempting to adjust a query that updates a date field. I am receiving an error on this part of the code
convert(varchar(10),dateadd(dd,-1,dateadd(mm,1,dateadd(yy+2,date('20'+left(t.event_name
,2)+'-05-01')))),121)
SQL Code -154 Error Message: SQLSTATE = 37000 [SAP][ODBC Driver][SQL Anywhere]Wrong number of parameters to function 'dateadd' Any help is greatly appreciated
The first thing I noticed is the yy+2 for the datepart argument of the inner-most DateAdd() call. That doesn't look right. Probably the + should be a comma.
Looking again, this is for SQL Anywhere, an offshoot of SQL Server, rather than SQL Server itself. So while the general principles in the portion of the answer below remain correct (avoid mixing strings and dates, use exclusive half-open date ranges), the exact functions to achieve those principles may be different.
But beyond this, if I'm following it correctly, we have a string where the first two characters represent years since 2000, and we use it to building a date for the day before a month after May 1, two years later.
If I wanted to build that date, I would do it like this:
EOMonth(DateFromParts(2000 + Cast(Left(t.event_name,2) as int) + 2, 5, 1))
As a general rule, if you're ever constructing a date from a string in SQL, you're doing something very wrong.
This performs better, with less code that is easier to read and maintain, and will not be vulnerable to misinterpretation if it's ever run in an environment with an unexpected default date format.
The simple +2 for the year addition is safe as long as you're not anywhere near a leap day, leap second, or daylight savings adjustment, none of which are near May 1... but you could add a DateAdd() call for the years if you want. The important thing is using DateFromParts() rather than constructing the value from a string, and using EOMonth() to combine adding a month and subtracting a day into a single simpler call.
Of course, this assumes you really need to calculate this for some reason and can't just do this:
DateFromParts(2000 + Cast(Left(t.event_name,2) as int) + 2, 5, 31)
But I'll give you the benefit of the doubt the 05-01 came from outside this query and could change for other months... in which case you would use EOMonth() and always just want the month number instead.
Oh, and there's a convert() call here, too, if you really need it:
CONVERT(varchar(10), EOMonth(DateFromParts(2000 + Cast(Left(t.event_name,2) as int) + 2, 5, 1)), 121)
But again: thanks to cultural/internationalization issues, going back and forth between strings and dates is NOT the fast, simple, or straightforward process you might expect. Rather, it's a slow and error prone conversion, something to avoid until absolutely necessary.
You are almost always better off returning a date value from your query and leaving the final conversion to string for your client code or reporting tool.
But I probably wouldn't build this exact date in the first place
This looks like a date construction that might be later used for the end of a range, while you'll have a BETWEEN expression like this:
WHERE SomeDate BETWEEN ... AND DateFromParts(2000 + Cast(Left(t.event_name,2) as int) + 2, 5, 31)
Or list the end of the range this as it's own expression:
... AND SomeDate <= EOMonth(DateFromParts(2000 + Cast(Left(t.event_name,2) as int) + 2, 5, 1))
Constructing the last inclusive value for the end of a date range is almost always a mistake.
That is, instead of building queries that run from that start of the range through the end of the range, you should build them to run from start of the range until (but not including) the period after the end of the range (or the beginning of the next range).
So instead of the BETWEEN example earlier, you want your code to use a half-open range (with an exclusive upper bound) that looks more like this:
WHERE SomeDate >= ... AND SomeDate < DateFromParts(2000 + Cast(Left(t.event_name,2) as int) + 2, 5+1, 1)
And note the <= changed to a < and the 5 advanced one month.