Getting SQL0182N (invalid datetime value or duration) when computing week start

[jackg_tor]

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

[PRIT AMRIT]

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])) )

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

[jackg_tor]

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

[PRIT AMRIT]

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

[jackg_tor]

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.