RE: Week-Function in SQL ?

> -----Original Message----- > From: Christian Brauer [mailto:Christian.Brauer@Dresdner-Bank.com] > Sent: Thursday, December 02, 1999 02:59 > To: informix-list@iiug.org > Subject: Re: Week-Function in SQL ? > > > > is there a "week-function" in the Informix-SQL ? Something like > > > > SELECT start_date, week(start_date) FROM ...> > > I didn't find any week(date) function, but here is a > select to simulated that one: > > SELECT > CASE > WHEN (weekday(mdy(1,1,YEAR(TODAY))) = 1) -- Monday > first day of > Year > THEN (((CURRENT YEAR TO DAY) - (MDY(1,1,YEAR(TODAY)))) / 7) > ELSE (((CURRENT YEAR TO DAY + 7 UNITS DAY) - > (MDY(1,1,YEAR(TODAY)))) / 7) > END > FROM any_table... > > > You can easily convert the statement above to an SPL, > > hth, > > > Chris > With IDS 7.3x we use the following expressions in a view to provide various ways of interpreting a date. SELECT dates, TRUNC(month(dates)/4,0)+1, -- quarter MONTH(dates), -- month_nbr DAY(dates), -- day_of_month YEAR(dates), -- fiscal_year TO_CHAR(dates,'%Y-%m'), -- YYYY-MM (as char) EXTEND(dates, YEAR TO MONTH), -- YYYY-MM (as datetime) TRUNC((dates+6-MDY(1,1,YEAR(dates))-WEEKDAY(dates))/7,0)+1, --week_nbr TRUNC(TO_CHAR(dates,'%w'),0)+1, -- day_of_week_nbr TO_CHAR(dates,'%a'), -- day_of_week_short TO_CHAR(dates,'%A'), -- day_of_week_long TO_CHAR(dates,'%b'), -- month_name_short TO_CHAR(dates,'%B'), -- month_name_long dates+1-MDY(1,1,YEAR(dates)) -- day_of_year FROM date_table Perhaps similar conversions will be useful to you.

> SELECT dates, > TRUNC((dates+6-MDY(1,1,YEAR(dates))-WEEKDAY(dates))/7,0)+1, --> week_nbr > Perhaps similar conversions will be useful to you. I really like that hell of a view - useful idea. But ... I checked your week_nbr sequence for today and 1/1/1999 and both results where wrong ! Did you miss something during copy and paste in this posting or is there a mistake in your view ? Bye, Chris