New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
EXTRACT(WEEK FROM DATE) [CORE663] #1029
Comments
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: Alice F. Bird (firebirds) Date: 2005-07-24 17:10 EXTRACT is a standard function and SQL99 doesn't declare a |
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. :-) ORACLE: TO_CHAR(d,'IW') 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: PAscal (legrand_legrand_63) And UDF emulating Oracle TO_CHAR with formats YYYY,YY,MON,MM,DDD,IW,DAY,D,HH24,HH,MI,SS is already available in fn_to_chard at http://perso.orange.fr/Udf4ORA/ |
Commented by: madaleno (madaleno) Is there any news about this topic? Thanks, Luis |
Commented by: lacak (lacak) implementation of weekofyear UDF |
Modified by: lacak (lacak)Attachment: fbudf.zip [ 10510 ] |
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). |
Modified by: @dyemanovassignee: Adriano dos Santos Fernandes [ asfernandes ] |
Modified by: @asfernandesstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 2.1 Beta 2 [ 10190 ] |
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, Jeroen Sparenberg. |
Modified by: @pcisarWorkflow: jira [ 10687 ] => Firebird [ 15067 ] |
Commented by: @pmakowski Q/A tested |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: madaleno (madaleno)
Attachments:
fbudf.zip
Votes: 1
SFID: 1225686#
Submitted By: madaleno
Hi,
Is it possible to have support for WEEK numbers in FB
built-in functions?
It would be nice to improve the EXTRACT() function to
include support for WEEK. like EXTRACT(WEEK FROM DATE)
This is very useful for financial data analysis.
I also can't find support for this in any UDF library.
Regards,
Luis Madaleno
Commits: b5a23c1
The text was updated successfully, but these errors were encountered: