CODEWITHSUNDEEP
mysqldatetimedate
Fahim Parkar
Fahim Parkar

Reputation: 31637

displaying week as per date

Below is what I have

 date
+++++++++++++
 2012-02-01
 2012-02-03
 2012-02-05
 2012-02-07
 2012-02-08
 2012-02-10
 2012-02-14
 2012-02-15
 2012-02-17
 2012-02-17
 2012-03-01
 2012-03-03
 2012-03-05
 2012-03-07

What I want is as below

week| date
++++++++++++++++++
  1 | 2012-02-01
  1 | 2012-02-03
  1 | 2012-02-05
  1 | 2012-02-07
  2 | 2012-02-08
  2 | 2012-02-10
  2 | 2012-02-14
  3 | 2012-02-15
  3 | 2012-02-17
  3 | 2012-02-17
  4 | 2012-03-01
  4 | 2012-03-03
  4 | 2012-03-05
  4 | 2012-03-07

I tried

SELECT WEEKDAY(date) AS week, date
FROM myTable
WHERE date BETWEEN start_date AND end_date
ORDER BY week

And below is what I get

week| date
++++++++++++++++++
  1 | 2012-02-01
  1 | 2012-02-03
  1 | 2012-02-05
  1 | 2012-02-07
  1 | 2012-03-01
  1 | 2012-03-03
  1 | 2012-03-05
  1 | 2012-03-07
  2 | 2012-02-08
  2 | 2012-02-10
  2 | 2012-02-14
  3 | 2012-02-15
  3 | 2012-02-17
  3 | 2012-02-17

which is incorrect as 2012-03-01 onwards shows as 1 which should be 4... Let me know where I am wrong...

Thanks in advance!!!

This question is related to this

Upvotes: 0

Views: 137

Answers (3)

Tim
Tim

Reputation: 9489

Insteak of using WEEKDAY use WEEKOFYEAR

SELECT WEEKOFYEAR(date) AS week, date
FROM myTable
WHERE date BETWEEN start_date AND end_date
ORDER BY week

UPDATE This question is the source

SELECT  (WEEK(date, 5) -
        WEEK(DATE_SUB(date, INTERVAL DAYOFMONTH(date) - 1 DAY), 5) + 1) as week, date
    FROM myTable
    WHERE date BETWEEN start_date AND end_date
    ORDER BY week

UPDATE 2

SELECT  (WEEK(date, 1) -
        WEEK(DATE_SUB(date, INTERVAL DAYOFMONTH(date) - 1 DAY), 1) + 1) as week, date
    FROM myTable
    WHERE date BETWEEN start_date AND end_date
    ORDER BY week

Upvotes: 2

Fahim Parkar
Fahim Parkar

Reputation: 31637

I found solution...

Below will give me what I want...

SELECT (SELECT COUNT(distinct u2.myDate) FROM myTable u2
WHERE 
u2.myDate > u1.myDate) + 1 AS week, myDate FROM myTable u1
WHERE myDate between startDate AND endDate
ordeR BY week

Update 1:

Below is what I have

myDate
+++++++++++++++++++++
2012-02-01 12:12:12
2012-02-01 12:12:12
2012-02-01 12:12:12
2012-02-03 12:12:12
2012-02-05 12:12:12
2012-02-07 12:12:12
2012-02-08 12:12:12
2012-02-08 12:12:12
2012-02-10 12:12:12
2012-02-14 12:12:12
2012-02-15 12:12:12
2012-02-17 12:12:12
2012-02-17 12:12:12
2012-03-01 12:12:12
2012-03-03 12:12:12
2012-03-05 12:12:12
2012-03-07 12:12:12

Using above query, below is what I get..

week |myDate
+++++++++++++++++++++++++++
  1  |2012-03-07 12:12:12
  2  |2012-03-05 12:12:12
  3  |2012-03-03 12:12:12
  4  |2012-03-01 12:12:12
  5  |2012-02-17 12:12:12
  5  |2012-02-17 12:12:12
  6  |2012-02-15 12:12:12
  7  |2012-02-14 12:12:12
  8  |2012-02-10 12:12:12
  9  |2012-02-08 12:12:12
  9  |2012-02-08 12:12:12
  10 |2012-02-07 12:12:12
  11 |2012-02-05 12:12:12
  12 |2012-02-03 12:12:12
  13 |2012-02-01 12:12:12
  13 |2012-02-01 12:12:12
  13 |2012-02-01 12:12:12

Upvotes: 0

yogi46
yogi46

Reputation: 53

If I run this query i got this result. in this first two days are same but i getting different week number:(

This is my result:

WEIGHT| WEEK| CREATED_ON
60 | 2 | 2012-02-08 16:16:32
200| 3 | 2012-02-08 16:16:30
63 | 4 | 2012-02-01 16:16:32
70 | 5 | 2012-01-25 16:16:32
75 | 6 | 2012-01-17 16:16:32
60 | 7 | 2012-01-08 16:16:32
63 | 8 | 2012-01-01 16:16:32
70 | 9 | 2011-12-25 16:16:32
75 | 10 | 2011-12-17 16:16:32
60 | 11 | 2011-12-08 16:16:32
63 | 12 | 2011-12-01 16:16:32
60 | 12 | 2011-12-01 16:16:32
70 | 14 | 2011-11-23 16:16:32
75 | 15 | 2011-11-17 16:16:32
63 | 16 | 2011-11-01 16:16:32
Sorry! I am new to stack over flow and mysql sorry for inconvenient stackOverFlow syntax.

Upvotes: 0

Related Questions