Solved

MySQL WEEK vs DATE_FORMAT

Posted on 2014-02-03
2
508 Views
Last Modified: 2014-02-03
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?
0
Comment
Question by:Robert Saylor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39829219
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
 
LVL 7

Author Closing Comment

by:Robert Saylor
ID: 39829299
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

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question