[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

MySQL WEEK vs DATE_FORMAT

Posted on 2014-02-03
2
Medium Priority
?
526 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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

656 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