CODEWITHSUNDEEP
mysqlsql-serverpdo
mauzilla
mauzilla

Reputation: 3592

Returning the closest to a date in a table using PDO Mysql/MSSQL

I have 2 PDO database connections. I am doing a search within a MS SQL table for a row that closest matches a date (mysql datetime) row.

I have mysql.table1.date passed to mssql.table and I am looking for the closest date accordingt to the mssql.table.date. It is also defined as a datetime field. I only need 1 row returned, the closest to the time, so in essence:

 SELECT * FROM table ORDER BY CLOSEST(mysqldate = mssql.table.date) LIMIT 1;

I know the syntax above is incorrect but that basically outputs what I need, but I really do not know how to do this with mssql.

Any help?

Upvotes: 1

Views: 270

Answers (2)

mauzilla
mauzilla

Reputation: 3592

A simple solution which worked for me was to do the following:

 SELECT * FROM `table` WHERE `date` < `startDate` ORDER BY `date` LIMIT 1;

This returns 1 row matching the closest time to the time I am passing :)

Upvotes: 1

praveen
praveen

Reputation: 12271

Basically u can find the difference of the mysql date with all the dates in mssql.Table.Date column .Then u need to select the least difference value from the above query .Hopefully the below query might help u 

 ;with CTE as 
 (
 Select mssql.table.date,row_number() 
 over (order by    abs(datediff(day,mysqlDate,mssql.table.date))) rowNumber
 from mssql.Table)
 select mssql.table.date from CTE where rowNumber=1

Upvotes: 1

Related Questions