How to calculate 'weeknumber' from Informix date

[petejigsaw]

Not sure if this is the right forum for this, but here goes:

I am trying to write and SQL expression to do a server-side calculation to establish the week number from a given date in the database.

In SQL, it'd be easy:

{fn WEEK("peranal"."pa_date")}

but I'm working against an Informix database!

Does anyone know how I can create an expression to do this against Informix, as it'd speed up the reporting significantly?

Thanks

 

[lbass]

So is week not in the function list for SQL expressions in Informix? Did you try just plain: week()? Or weekofyear()?

-LB

 

[petejigsaw]

Hi lbass

Unfortunately not - week() and weeknumber() are not Informix referenced SQL terms.

According to the performance stats for the report without using a week() type expression, the whole report (one main report for daily sales, plus WTD and MTD subreports) takes about 34 secs to run, which is a vast improvement on the 5-6 minutes pre use of expressions.

Getting an expression equivalent to week() would be good, but isn't essential.

 

[lbass]

Here's a link to something that might help (I'm not sure):

http://stackoverflow.com/questions/274861/how-do-i-calculate-the-week-number-given-a-date

Otherwise, I would just use the week function in CR instead of trying to make it into a SQL expression.

-LB