MySQL WEEK vs DATE_FORMAT

I am going to write a report based on week number of the year.

DATE Example: 20161225

When I run this date using mysql I get 2 different values for the WEEK Number.

My select statement:

WEEK(`charters`.`start_date`, '%u') AS 'ISO_WEEK'

This returns "52" for the week number

DATE_FORMAT(`charters`.`start_date`, %v') AS 'ISO_WEEK2'

This returns "51" for the week number

Witch one should I use?
LVL 7
Robert SaylorSenior DeveloperAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
for WEEK, the 2nd argument will specify what the result value will be, and %u seems like incorrect, it must be a value from 0 to 7 instead.
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

and in date_format:
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
if you used %v, this would match 3 or 7 (see the exact rules)
if you used %u, this would have 1 or 5 (see the exact rules)

so, the answer is: it depends on what you want to use as rules/output
0
 
Robert SaylorSenior DeveloperAuthor Commented:
Thank you! I was not passing the mode correctly in WEEK. Looks good now and I have something to report on when the WEEK number starts.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.