CODEWITHSUNDEEP
mysqlsql
oursgris
oursgris

Reputation: 2882

End of the year with Mysql

I'd like to have the week number and year, for a given date, from MySQL with the following rules:

I've read the week function in MySQL but I can't get the result I want.

Date and Time Functions: WEEK(date[,mode])

I am on the french calendar, so I have to begin the week on Monday and week 1 is the first week with more than 3 days this year.

Therefore I can only use options 1 and 3.

When I write the following queries:

When I test on the 1st Jan 2016:

Option 1 can't be used, because I can't detect that 2012-12-31 is in the next year.

Option 3 can be used, but I have the add two pieces of logic: if weeknumber = 1 and month = 12, year + 1 and if weekumber = 53 and month = 1 then year - 1

Does someone have a better solution?

Regards

Upvotes: 5

Views: 1100

Answers (1)

deadly
deadly

Reputation: 1192

Ok, I think I get what you're trying to do now.

As the documentation says:

We decided to return 0 instead because we want the function to return “the week number in the given year.”

If you want the week number for the year that the week is in, they suggest using the YEARWEEK() function, which takes the same mode arguments as WEEK():

If you would prefer the result to be evaluated with respect to the year that contains the first day of the week for the given date... use the YEARWEEK() function:

 mysql> SELECT YEARWEEK('2000-01-01');
         -> 199952
 mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
         -> '52'

So some examples of what you'd use:

mysql> SELECT MID(YEARWEEK('2012-1-1',3),5,2)
         -> '52'
mysql> SELECT MID(YEARWEEK('2012-12-31',3),5,2)
         -> '01'

Upvotes: 2

Related Questions