Convert Query for SQL Server

[obrienkev]

Hi all,

How do I convert the below MySQL query to work in SQL Server?

INSERT INTO archive_excel
                      (name, file_date)
SELECT     name, DATE_SUB(file_date, INTERVAL 3 DAY)
FROM         excel
WHERE     CURDATE() >= DATE_ADD(file_date, INTERVAL 1 DAY)

Thanks.

[jheitz]

Hi there,

Not an expert un MySQL, but I know quite a bit of SQL Server. Try something like this:

INSERT INTO archive_excel
                      (name, file_date)
SELECT     name, file_date - 3
FROM         excel
WHERE     getdate() >= file_date + 1

obrienkev wrote:

Hi all,

How do I convert the below MySQL query to work in SQL Server?

INSERT INTO archive_excel
                      (name, file_date)
SELECT     name, DATE_SUB(file_date, INTERVAL 3 DAY)
FROM         excel
WHERE     CURDATE() >= DATE_ADD(file_date, INTERVAL 1 DAY)

Thanks.

[obrienkev]

Hi,

Here's my code now...

$query = "INSERT INTO archive_excel(name, file_date) ".
             "SELECT name, file_date ".
             "WHERE GETDATE() >= DATEADD(day, 1, file_date)";

Tested it on SQL Server - gives no errors but doesn't move the data to the archive_excel table.
The file_date value in excel table is: 15/05/2006 16:04:00

I think the problem is with the time. How can I remove reference to the time?

Thanks.

[jheitz]

SQL Server will compare two dates easily, so all you need really is:

"WHERE file_date >= GETDATE()"

or

"WHERE file_date >= GETDATE() + 1" (today's date & time + 24 hours)

You ALWAYS want to add/remove days from the Getdate() function and NEVER from your database column as it is much less efficient