converting a script from mysql to mssql - selecting dates

[Poddy]

Hello all, I'm having a problem with converting a script from MySQL to MSSQL

.. the problem is within this query:

 

select * from data  where  name LIKE '$name' AND spot LIKE '$line' AND piret LIKE '$piret' [b]AND timestamp >= '$before' AND timestamp <= '$after' ORDER BY timestamp DESC[/b]

 

the statement works until the bold part...

please note that all the variables except $before and $after are inserted with a '%'.

and before and after are built like this: 08/01/2008

i need it to select from dates between lets say 08/01/2008 and 08/03/2008 including those dates so records from the 1st, the 2nd and the 3rd will appear.. however i cannot get it to work

is there another way of making this statement?

 

the query does not fail, but does not return results.

 

also i have another issue:

 

SELECT * FROM data WHERE timestamp >= DATE_SUB(curdate(), INTERVAL 5 DAY) AND timestamp <= DATE_ADD(curdate(), INTERVAL 1 day) AND name LIKE '$name' AND spot LIKE '$line' AND piret LIKE '$piret' ORDER BY timestamp DESC "

 

i have this old line of code, but i cant find a function that works for me for current time, and subtract and add days that belong to mssql..

the function of that script is PHP recognizing the day of the week and the script chooses records of that week only.

 

Any help appriciated

Thanks in advance ???

[Poddy]

well i solved the first issue... using the BETWEEN statement and correcting the date format which for some reason i remembered it as a diffrent format.. my mistake.

 

However i still can't figure out how to SUB a date and ADD a date with days

i have tried this in the mssql console yet still not working

 

SELECT * FROM data WHERE timestamp BETWEEN dateadd(day, -6, '2008-01-08') AND '2008-03-08' AND name LIKE '$name' AND spot LIKE '$line' AND piret LIKE '$piret' ORDER BY timestamp DESC