Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010

    Smile Unanswered: how to find the week number from the specified month.

    Hi guys,
    i have a doubt. I want to know how to get the week number of the month. For example, if the date is 6th of any month then value for week should be 1, if the date is 19th then value should be 3.


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    so either write a function in the front end
    or consider using the modulo operator (%) on the DAY of the specified date

    SELECT my, column, list, (DAY(adatecolumn)+6)%7 AS weekno FROM mytable
    you may need to tinker with the expression to make certain it meets your requirements

    mind you to me its an odd definition of the week number, but its your app
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2010
    Make a calendar table and populate it with as many dates you want. To get data corresponding to the week number just do a left join from the table where you have data with its own date column.

    the basic syntax would be :
    select coalesce(CEILING(dayofmonth(calendar.datefield)/7),0)as WeekNumber from calendar;

    Enjoy !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts