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.

Custody

Interesting,might be a bug. – Priory

Thank you, You are right! The problem is, now I can't modify the SQL Server code, already it was in an application. Now the thing is, that I need to write the code which needs to give the equivalent result of SQL server code. If I need to use "DAY" means, both the application I have to change it. – Custody

If you need to reply to a user, they are only notified if you comment on their post or you use an @ sign with their user name, like so: @Tanner. Because you commented on your question, rather than my answer, I don't get a notification. I only came back as my answer was voted on so wanted to check for any updates. – Guadalcanal

@Custody you can use the DAYOFWEEK() method to detect the first/last days and add some logic to add 1 to the week count if the days cross a weekend without a complete week. – Guadalcanal

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 |

Guadalcanal