EXTRACT(WEEK FROM DATE) [CORE663] #1029
Submitted by: madaleno (madaleno)
Is it possible to have support for WEEK numbers in FB
It would be nice to improve the EXTRACT() function to
This is very useful for financial data analysis.
I also can't find support for this in any UDF library.
The text was updated successfully, but these errors were encountered:
Commented by: Alice F. Bird (firebirds)
Date: 2005-07-29 13:55
Well I guess FireBird already has some SQL features that are
Commented by: @dyemanov
I'm afraid we have a problem here.AFAIK, there are different algorithms of counting week numbers in various countries, so our solution will never satisfy everybody. The only related standard I'm aware of is ISO-8601, but I doubt it's practically useful, as it may count Jan-01 as either week 1 of the new year or week 52 of the previous year, depending on week days alignment.
So my position is that everybody interested should implement the week number UDF they're comfortable with. Provided with enough pressure, I could agree to add either the absolute week number counting or the ISO implementation to the standard UDF library, but I'm still against extention of EXTRACT.
Commented by: madaleno (madaleno)
My opinion is that FB should implement the ISO standard like other DBMS have.
If going the UDF way, the should also be UDFs for YEAR and MONTH to keep things uniform. It would be very confusing for users having to write diferent code for similar things.
Commented by: Aki Nieminen (whome)
I have seen systems where both ISO compliant and "US" compliant week functions are implemented:
I always prefer embedded functions instead of separate UDF installations to maintain consistent look&feel across the community.
Commented by: lacak (lacak)
I would like vote for this function too. :-)
MS SQL : DATEPART(week,datum)
MySQL : WEEK(date[,mode]), WEEKOFYEAR()=WEEK(date,3) -> ISO8601 compliant
PostgreSQL: EXTRACT(WEEK FROM datum) -> ISO8601 compliant
So I guess, that FB can follow PostgreSQL and MySQL
Commented by: @dyemanov
It seems that the majority wants to see this function returning an ISO-based week number and implemented as the built-in function. Adriano, if you don't have objections yourself, please take care of it for v2.1 Beta 2.
And, BTW, if we're about extending EXTRACT, the Yaffil's MILLISECONDS clause could also be added at the same time (PostgreSQL also supports this in EXTRACT).
Commented by: Jeroen Sparenberg (jsparenberg)
There is an alternative for calculating the weeknumbers. But it only works for within one year. The sql select line looks like this:
"SELECT CAST(((DATEFIELD - cast(:PARAM_STARTOFYEAR as integer) - cast('1900-01-01' as date)) / 7) + 0.5 as INTEGER) as weeknumber"
Within the above line, the parameter PARAM_STARTOFYEAR needs to be calculated outside of your query. For 2006 it should be 2006-01-02 according to our calender. The best part of this setup is that no standard is required.
Now I will try to explain the mySQL line as the weeknumber is calculated:
With kind regards,