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

Question

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

The following statement, executed in SQL Server 2000, produces a result of 109.

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

The equivalent query in mysql uses timestampdiff()instead datediffand gives the result as 108.

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

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

Answer 1

I think this can be caused by one of two things:

• Which is classified as the first day of the week between instances of SQL Server and MySQL.
• How weeks are calculated between SQL Server and MySQL

2012-09-01 , , , , .

MySQL : 0 (Sunday)

SQL Server, @@DATEFIRST, :

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

, , , 7, , :

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 |

, .

SQL Server, , ( ) , , 2 , :

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

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

, MySQL, , 7 , :

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

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

7 , 1, :

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

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