Convert dates into weeks

Question

How to convert the continuous date into weeks , saturday should be the week ending Eample:

2/27/2015       3/6/2015          3/13/2015      3/20/2015

here I am giving more info on my requirement so that you can understand easily.

I have a datetime field in my db lets say Date2. I need to group this date into weeks in mm/ dd/ yyyy. lets take an example my date2 start like 1/1/2015 to 20/1/2015.

1/1/2015-3/1/2015 this range displayed as   3/1/2015,
4/1/2015-10/1/2015 ----------------------- 10/1/2015,
11/1/2015-17/1/2015 ---------------------- 17/1/2015,
18/1/2015-24/1/2015 ---------------------  24/1/2015

Answer 1

It's not completely clear what you are asking, so I will guess. Please forgive if I guess wrong.

Let us say you have an arbitrary DATETIME column named Date2 , and you wish to produce the DATE of the Sunday immediately preceding that value. In other words, you wish to figure out the week in which the DATETIME occurs.

You can do that like this:

 FROM_DAYS(TO_DAYS(Date2) -MOD(TO_DAYS(Date2) -1, 7))

So, if you have a Sales table with customer_id, Date2, and sale, you can summarize by week like this:

SELECT FROM_DAYS(TO_DAYS(Date2) -MOD(TO_DAYS(Date) -1, 7)) week_beginning,
       COUNT(*) num_transactions,
       COUNT(DISTINCT customer_id) num_customers,
       SUM(sale) total_sales 
  FROM sales
 GROUP BY FROM_DAYS(TO_DAYS(Date2) -MOD(TO_DAYS(Date) -1, 7))

This will show the date of the Sunday that begins each calendar week. If you want the Saturday that ends each calendar week, use this instead.

SELECT FROM_DAYS(TO_DAYS(Date2) -MOD(TO_DAYS(Date) -1, 7))
          + INTERVAL 6 DAY week_ending,
       COUNT(*) num_transactions, ...

I have written this up in detail here: http://www.plumislandmedia.net/mysql/sql-reporting-time-intervals/

You can