EXTRACT(YEAR_FOR_WEEK from '2012-01-01')
----------------------------------------
Key: CORE-3715
URL: http://tracker.firebirdsql.org/browse/CORE-3715
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Affects Versions: 2.1.4
Environment: All known
Reporter: Pierre Yager
I run into a problem last week week because we are now in 2012. The first week
of a year is, by convention, the first week that contains the first tueday of
the year. It means that if january starts on friday, saturday or sunday, these
days of january are still in the week 52 of 2011.
It was the case this year : 2012-01-01 was still part of the week 52 of the
year 2011.
Consider this table :
CREATE TABLE WORK_TIME(
ID Integer NOT NULL,
USER Integer NOT NULL,
ARRIVAL TIMESTAMP,
DEPART TIMESTAMP,
DURATION COMPUTED BY (DateDiff(MINUTE from ARRIVAL to DEPART)),
MONTH COMPUTED BY (Extract(MONTH from ARRIVAL)),
WEEK COMPUTED BY (Extract(WEEK from ARRIVAL)),
YEAR COMPUTED BY (Extract(YEAR from ARRIVAL)),
CONSTRAINT WORK_TIME_PK PRIMARY KEY (ID)
);
For users that worked on 2012 Jan. 1st, the "YEAR" column is not semantically
correct for my application because the variable start-of_week was 2011-12-26.
SQL.Text := 'select ID, USER, ARRIVAL, DEPART, DURATION ' +
'from WORK_TIME ' +
'where (USER< 999) and (YEAR=?) and (WEEK=?) ' +
'order by USER, ARRIVAL';
Params.AsInteger[0] := YearOf(Start_Of_Week);
Params.AsInteger[1] := WeekOf(Start_Of_Week);
I miss an EXTRACT(YEAR_FOR_WEEK from ARRIVAL) function in order to make this
code work. In this case, YEAR_FOR_WEEK('2012-01-01') = 2011
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
