Hi,
I'm dealing with a problem that's driving me nuts and I'm hoping that someone can help.
I'm trying to compute the start-of-week date of a timestamp value using the expression
_add_days(date([CREATED]), 1 - dayofweek(date([CREATED])) )
(as well as countless variations of that) and I keep getting the DB2 error
A general exception has occurred during the operation "prepare".[IBM][CLI Driver][DB2/NT] SQL0182N An expression with a datetime value or a labeled duration is not valid. SQLSTATE=42816
I can't see the SQL that it's passing to DB2 because that's not visible when the prepare fails. I'm currently working around the problem by using a 7-choice CASE statement but I'd rather avoid that if possible.
Anyone have any ideas on what's wrong?
Thanks,
Jack Goldstein
QuoteI'm trying to compute the start-of-week date of a timestamp value using the expression
_add_days(date([CREATED]), 1 - dayofweek(date([CREATED])) )
Can you try like this,
extract(day,(_add_days(date([CREATED]),1)))-_day_of_week(date([CREATED]),1) -- This will give you the 1st day for that week.
Thanks
Hi PRIT, thanks for the reply.
That expression does work without errors but it does not return the right data. I want the week start date but that returns an integer between -5 and 31 without any handling of month or year boundaries. Any idea how to modify that to get what I want?
Thanks,
Jack Goldstein
Yes. As per the expression it would return only 'Day' part. Name this data item as [First Day of Week]
Now all you can do is, get Year & Month from the date([CREATED]) field and again concatenate something like below and then map it with your actual date field.
E.g.
cast(Extract(month,date([CREATED])),char(2))+'-'+[First Day of Week]+'-'+cast(Extract(year,date([CREATED])),char(4))
EDIT: Since your DB is DB2, you might have to convert int to char with db2 syntaxes.
Thanks
I'm afraid that doesn't work because it assumes that the month and year of the first day of the week are the same as for the current day. Similarly, the [First Day of Week] expression can be negative. Of course, I could write an expression to handle all that but then I might as well stick with my simpler case statement.