asked on

help with SQL statement

The attached statement basically returns all dates in a datetime field. I would like it to exclude today and yesterday.

SELECT CONVERT(CHAR(10),ExportTime, 101) AS Expr1, COUNT(*) AS DumpCount FROM ServerData GROUP BY CONVERT(CHAR(10),ExportTime, 101) ORDER BY Expr1 ASC

edit: attached code looked weird, included here as well.

SELECT CONVERT(CHAR(10),ExportTime, 101) AS Expr1, COUNT(*) AS DumpCount FROM ServerData GROUP BY CONVERT(CHAR(10),ExportTime, 101) ORDER BY Expr1 ASC

Open in new window

Kevin Cross

Do you want all the dates before yesterday and all the dates after today?

Raja Jegan R

This should help you out:

SELECT CONVERT(CHAR(10),ExportTime, 101) AS Expr1, COUNT(*) AS DumpCount 
FROM ServerData 
WHERE ExportTime <= CONVERT(datetime,CONVERT(char(10), ExportTime - 1, 101),101)
GROUP BY CONVERT(CHAR(10),ExportTime, 101) 
ORDER BY Expr1 ASC

Open in new window

Kevin Cross

Try this:

SELECT CONVERT(CHAR(10),ExportTime, 101) AS Expr1, COUNT(*) AS DumpCount 
FROM ServerData
WHERE ExportTime < DATEADD(day, -1, DATEDIFF(day, 0, GETDATE()))
AND ExportTime >= DATEADD(day, 1, DATEDIFF(day, 0, GETDATE()))
GROUP BY CONVERT(CHAR(10),ExportTime, 101) 
ORDER BY Expr1 ASC

Open in new window

Cerixus

ASKER

I want all the dates prior to yesterday, yes. The field will not contain any dates after today (unless something very strange is going on) :)

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Cerixus

ASKER

ExportTime < DATEADD(day, -1, DATEDIFF(day, 0, GETDATE()))

Worked perfectly. Thanks!

Kevin Cross

Cerixus,

Glad that helped!

Happy coding...

Regards,
Kevin