[cakephp] Trying to get week number out of date field

Question

Hi,

For a given date field (formatted in sql as: yyyy-mm-dd), I would like to output it as week number.

I tried to work with date() but that didn't really work out.

SQL field: YYYY-MM-DD

Desired output: Week 12 - 2010

Does anyone know how to do this? thanks in advance!!

Answer 1

If you want to just do it in MySQL, you can use the WEEK() function:

SELECT WEEK(date) as week, YEAR(date) as year FROM mytable

Note, you may want to specify a mode number for WEEK() to tell MySQL which day of the week to start on. E.g. some servers may be configured to start weeks on Mondays, whereas other may start on Sundays.

SELECT WEEK(date, 0) as week ... /* explicitly start weeks on Sundays */

There are also other week number functions for MySQL that you might want to check out, such as YEARWEEK(), which returns the year + week number (e.g. 201012):

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_week

Answer 2

Try this:

$bits = explode("-", "2010-08-28");
echo date("W", mktime(0, 0, 0, $bits[1], $bits[2], $bits[0]));

Answer 3

In PHP you might try this:

echo date("\Week W - Y", date_parse($sql_date_from_db))

Answer 4

Use strtotime and date :

<?php
$sqldate = "2010/08/28";

echo date("\W\e\e\k W - Y", strtotime($sqldate));
?>