DateTime fields, methods over such fields,
DateTime depended SQL where-clause and SQL-functions.
Ivan Smahin
IvanSmahin at public.kherson.ua
Thu Dec 9 20:27:50 CST 2004
Hello Claudius,
Thursday, December 9, 2004, 6:59:09 PM, you wrote:
CS> HI Ivan,
CS> Am 09. Dez 2004 um 11:58 Uhr schrieb Ivan Smahin:
>> CS> Hi,
>>
>> CS> Am 09. Dez 2004 um 01:15 Uhr schrieb Ivan Smahin:
>>
>>>> Strict usage of such keywords must be only in methods'
>>>> predicates based on
>>>> datetime-literals.
>>>>
>>>>
>>>> 3. There is powerful set of datetime SQL-functions in Valentina
>>>> 2.
>>>> I would say this is a most powerful set in DBMS world.
>>
>> CS> is it also planed to install a function/method weekoftheyear?
>>
>> It is the same to WEEK function.
>> Week( date, [firstWeekDay] )
>> Returns the week of year in the range of 0 - 53
>> firstWeekDay == 0 - is for Sunday
>> 1 is for Monday and so on... up to 6 (Sat)
CS> Thanks, but this is my problem. IMHO do we have several calculation
CS> methods. The US-Method says 1.1.YYYY ist week 1 using DIN week 1 ist
CS> where the first thursday is in. So you can have 01.01.XXXX in week 52
CS> or week 53 by DIN but week 1 in US Method. The information when is a
CS> new week starting is a good thing, because US-week starts on sunday and
CS> by DIN on Monday.
CS> But after that, what is calculationMethod in Valentina?
CS> Thanks for help
CS> Claudius
I see. So for making week function more flexible we add third optional param
to this sql-function - minimalDaysInFirstWeek.
So, now it seems like:
Week( date, [firstWeekDay], [minimalDaysInFirstWeek] )
where
minimalDaysInFirstWeek must be in range 1-7
(1 by default - "US calendar" - XXXX-01-01 is always first week)
Some examples:
SELECT week('2005-01-01')
SELECT week('2005-01-01', 0)
SELECT week('2005-01-01', 1)
SELECT week('2005-01-01', 2)
SELECT week('2005-01-01', 3)
SELECT week('2005-01-01', 4)
SELECT week('2005-01-01', 5)
SELECT week('2005-01-01', 6)
SELECT week('2005-01-01', 2, 3)
SELECT week('2005-01-01', 3, 3)
SELECT week('2005-01-01', 4, 3)
SELECT week('2005-01-01', 5, 3)
SELECT week('2005-01-01', 6, 3)
All these queries produce 1 as result.
In other words, you get first week for 01.01,XXXX.
----------------
SELECT week('2005-01-01', 0, 3)
SELECT week('2005-01-01', 1, 3)
But this queries produce 53 as result.
Is it ok for you?
--
Best regards,
Ivan mailto:IvanSmahin at public.kherson.ua
More information about the Valentina-beta
mailing list