CODEWITHSUNDEEP
phpmysqlsql
Condward
Condward

Reputation: 135

SQL WEEK() gives different week number than date('W')

The SQL fuction Select WEEK(mydate) ... is returning a week number different than the PHP function date('W',strtotime($mydate))

Where $mydate is a String in the format (Y-m-d h:i:s) obtained by selecting the mydate variable on the SQL db.

It is just one week number of difference but I can't subtract because of the edge cases of 0 and 52, they get mixed up there.

I know WEEK(mydate,3) will make them equal but I cannot change the SQL Statement, I need to change it on the PHP calculation.

How do I change the function date('W',strtotime($data)) so It gives me a week equal to SQL?

Thanks in advance.

Upvotes: 1

Views: 815

Answers (3)

John Ruddell
John Ruddell

Reputation: 25872

maybe try something like this in php

strftime(%U, strtotime($mydate))

Upvotes: 1

Sammitch
Sammitch

Reputation: 32272

  1. You can't change how PHP calculates week numbers.
  2. You should not attempt to "roll your own" week calculation. Date math is hella complex.

The best solution would be to alter the query to use WEEK(mydate, 3), but failing that you should offload the calculation to the same MySQL server as you're retrieveing the other week data from:

$example_date = '2014-12-16';
$query = sprintf("SELECT WEEK('%s')", $example_date);
if( ! $res = $dbh->query($query) ) { die('fission mailed'); }
$val = $res->fetch();
printf("Week number: %d\n", $val[0]);
// Week number: 50

Upvotes: 2

Steven Don
Steven Don

Reputation: 2431

Check out https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_default_week_format

If you can't change the query itself, you could change that variable. (Although you might not be able to, if you can't change the query).

Upvotes: 0

Related Questions