RE: Week-Function in SQL ?

> -----Original Message----- > From: Christian Brauer [mailto:Christian.Brauer@Dresdner-Bank.com] > Sent: Thursday, December 02, 1999 13:06 > To: informix-list@iiug.org > Subject: Re: Week-Function in SQL ? > > > > 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 The formula was cut and pasted properly. It depends upon your interpretation of a week. My calculations considers Sun -> Sat to be a week. Any portion of that becomes week 1 in a given year. Thus 1/1/1999 and 1/2/1999 are in week 1. Today (12/2/1999) is part of week number 49. December 26-31 are week 53 of 1999 and 1/1/2000 is week 1 within year 2000. If you are counting week numbers differently, you can adjust the formula accordingly. Below is output from our view: Date Week Day of Year 01/01/1999 1 1 01/02/1999 1 2 01/03/1999 2 3 01/04/1999 2 4 01/05/1999 2 5 01/06/1999 2 6 01/07/1999 2 7 01/08/1999 2 8 01/09/1999 2 9 01/10/1999 3 10 01/11/1999 3 11 01/12/1999 3 12 01/13/1999 3 13 01/14/1999 3 14 01/15/1999 3 15 01/16/1999 3 16 01/17/1999 4 17 01/18/1999 4 18 01/19/1999 4 19 01/20/1999 4 20 01/21/1999 4 21 01/22/1999 4 22 01/23/1999 4 23 01/24/1999 5 24 01/25/1999 5 25 01/26/1999 5 26 01/27/1999 5 27 01/28/1999 5 28 01/29/1999 5 29 01/30/1999 5 30 01/31/1999 6 31 (clipped) 11/28/1999 49 332 11/29/1999 49 333 11/30/1999 49 334 12/01/1999 49 335 12/02/1999 49 336 12/03/1999 49 337 12/04/1999 49 338 12/05/1999 50 339 12/06/1999 50 340 12/07/1999 50 341 12/08/1999 50 342 12/09/1999 50 343 12/10/1999 50 344 12/11/1999 50 345 12/12/1999 51 346 12/13/1999 51 347 12/14/1999 51 348 12/15/1999 51 349 12/16/1999 51 350 12/17/1999 51 351 12/18/1999 51 352 12/19/1999 52 353 12/20/1999 52 354 12/21/1999 52 355 12/22/1999 52 356 12/23/1999 52 357 12/24/1999 52 358 12/25/1999 52 359 12/26/1999 53 360 12/27/1999 53 361 12/28/1999 53 362 12/29/1999 53 363 12/30/1999 53 364 12/31/1999 53 365