This query acts weird...

nemonoman

On my localhost MySQL db (Windows XP):

SELECT YEARWEEK( '20030928110044' )

200339

On my webserver MySQL db (Linux):

SELECT YEARWEEK( '20030928110044' )

200340

Am I missing something?

All XP yearweeks are= Linux yearweeks-1

bad76

Hi,
looking into manual (here ), i've found:

WEEK(date,start)
With a single argument, returns the week for date, in the range 0 to 53 (yes, there may be the beginnings of a week 53), for locations where Sunday is the first day of the week. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range 0-53 or 1-52. The following table demonstrates how the start argument works: Value Meaning

0 Week starts on Sunday; return value range is 0 to 53

1 Week starts on Monday; return value range is 0 to 53

2 Week starts on Sunday; return value range is 1 to 53

3 Week starts on Monday; return value range is 1 to 53 (ISO 8601)

The start value of 3 can be used as of MySQL 4.0.5.

and

YEARWEEK(date,start)
Returns year and week for a date. The start argument works exactly like the start argument to WEEK().

So if you write
SELECT YEARWEEK( '20030928110044',0)
you will get the same result on XP and *NIX too.

see you

nemonoman

Well, I'm humbled by your answer, since I missed that part about the second parameter.

But I just tried it and...

On my XP machine:

SELECT YEARWEEK( '20030928110044', 0 )

.YEARWEEK( '20030928110044', 0 )
200339

On my host's Linux Server:

SELECT YEARWEEK( '20030928110044', 0 )

.YEARWEEK( '20030928110044', 0 )
200340

Out of curiousity, I tried this...

SELECT NOW()
My home XP (in California):

2003-11-06 07:40:23
Host (in Texas, I think):

2003-11-06 09:44:43

bad76

Now me, I'm humbled!

I've used my XP with apache/mysql and I got:
(mysql 3.23.49)
SELECT YEARWEEK( '20030928110044', 0 ) -> 200340
SELECT YEARWEEK( '20030928110044', 1 ) -> 200339

Then I've loaded my own Knoppix 3.2 and I got this:
(mysql 4.0.12)
SELECT YEARWEEK( '20030928110044', 0 ) -> 200339
SELECT YEARWEEK( '20030928110044', 1 ) -> 200339

Now I think linux or mysql4.0 return always 200339.
And you can emulate this on XP using "...,1)"

Else give you a +/- 1 before upload to server...

bye bye

nemonoman

Thanks, Bad. I think you have identified the problem: the MySQL version, not the operating system:

SELECT version( ),
YEARWEEK( '20030928110044',
0 ) ,
YEARWEEK( '20030928110044',
1 )

Linux server 1:
3.23.56 200340 200339

Linux server 2
4.0.15-max-log 200339 200339

XP server:
4.0.4-beta-max-nt 200339 200339

After searching, I found this in an OBSCURE MySQL document:
D.3.13 Changes in release 4.0.5 (13 Nov 2002)

Changed handling of last argument in WEEK() so that one can get week number according to the ISO 8601 specification. (Old code should still work).

Thanks for your help, Bad!