Solved

MySQL WEEK vs DATE_FORMAT

Posted on 2014-02-03
2
501 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
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

679 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