[RESOLVED] SQL SERVER + DATEADD Function

[obrienkev]

Hi all,

I want to return all values in tbl where class_start is one day ahead of today's date.

class_start data is 25/07/2006 13:00:00

SELECT * FROM class WHERE class_start = DATEADD(day,-1,getdate())

Think the issue is the time. How do I solve this?

Also tried...
SELECT * FROM class WHERE class_start = DATEADD(d,DATEDIFF(d,0,GETDATE()),-1)

Thanks,

Kevin.

[bogu]

Format your date so it exclude your time:

DATE_FORMAT(class_start,"%Y-%m-%d") = DATEADD(day,-1,getdate())

[Installer]

• It's "DATE_ADD()", not "DATEADD()".
• It's "CURDATE()", not "GETDATE()".
• "DATE_ADD()" takes two arguments, not three.
• "DATE_ADD()" uses the keyword "INTERVAL".

  ...WHERE class_start = DATE_ADD(CURDATE(), INTERVAL -1 DAY)

  Here is a good place to learn something about MySQL date and time functions.

Edit: To incorporate bogyou's suggestion:

$query = "SELECT * FROM class " .
         "WHERE DATE_FORMAT(class_start,'%Y-%m-%d') = DATE_ADD(CURDATE(), INTERVAL -1 DAY)";

[obrienkev]

Hi guys,

Using MS SQL SERVER 2003 so not MySQL date functions

[Installer]

:o Oops. Never mind.

[obrienkev]

This works...

SELECT * FROM class WHERE class_start >= DATEADD(d,DATEDIFF(d,0,GETDATE()),0) AND class_start < DATEADD(d,DATEDIFF(d,0,GETDATE()),1)