Is timestampdiff() in MySQL equivalent to datediff() in SQL Server?

Question

I am working on migrating functions from SQL Server 2000 to MySQL.

The following statement executed in SQL Server 2000, gives the output as 109.

SELECT DATEDIFF(wk,'2012-09-01','2014-10-01') AS NoOfWeekends1

The equivalent query of in mysql uses timestampdiff() instead of datediff and gives the output as 108.

SELECT TIMESTAMPDIFF(WEEK, '2012-09-01', '2014-10-01') AS NoOfWeekends1

I need the output to match when executed in MySQL, so it returns 109.

Answer 1

I think this could be caused by one of 2 things:

• What is classified as the first day of the week between your SQL Server and MySQL instances.
• How weeks are counted between SQL Server and MySQL

Your given date 2012-09-01 falls on a Saturday, which seems to rule out the start day of the week, which is usually Sunday or Monday.

MySQL has a default start day of: 0 (Sunday)

To find out your SQL Server start of the week you can use @@DATEFIRST by running this:

select @@DATEFIRST -- default US English = 7 (Sunday)

You could change your calculation to work on days rather than weeks and dividing by 7 to get a more accurate value, which you can round as you please:

MySQL: SQL Fiddle Demo

SELECT TIMESTAMPDIFF(DAY, '2012-09-01', '2014-10-01')/7 AS NoOfWeekends1


| NOOFWEEKENDS1 |
|---------------|
|      108.5714 |

SQL Server: SQL Fiddle Demo:

SELECT DATEDIFF(d,'2012-09-01','2014-10-01')/7.0 AS NoOfWeekends1

| NOOFWEEKENDS1 |
|---------------|
|    108.571428 |

You could round that up or down depending on if you want to match your previous result or count it as an extra weekend.

SQL Server seems to count the number of Sundays (if that's the start of the week) between 2 dates as shown with this example fiddle where I've changed the date range to be 2 days, a Saturday and a Sunday:

SELECT DATEDIFF(wk,'2012-09-01','2012-09-02') AS NoOfWeekends1

| NOOFWEEKENDS1 |
|---------------|
|             1 |

Where as the same values in MySQL seems to only count a full 7 days as a week as shown in this demo fiddle:

SELECT TIMESTAMPDIFF(WEEK, '2012-09-01', '2012-09-02') AS NoOfWeekends1

| NOOFWEEKENDS1 |
|---------------|
|             0 |

It's only when a full 7 days pass you get the result of 1 as you can see in this demo fiddle:

SELECT TIMESTAMPDIFF(WEEK, '2012-09-01', '2012-09-08') AS NoOfWeekends1

| NOOFWEEKENDS1 |
|---------------|
|             1 |