Need to query records based on Date only (Sample Query given)

Question

sSQL = "
SELECT ordID, ordName, ordDate, ordShipping, ordStateTax, 
       ordHandling, ordTotal, ordDiscount, ordPrivateStatus 
FROM orders 
WHERE (ordStatus = 7)
"

I want to display orders that came in yesterday as a daily report script I am building

How would I go about it?

SQL Server 2005

ordDate has this format: 2015-01-28 14:58:49.000

Answer 1

An open-ended range is the safest in terms of granularity and sargability, and being immune to underlying data type changes:

DECLARE @today DATETIME;
SET @today = DATEADD(DAY,DATEDIFF(DAY,'20000101',GETDATE()),'20000101');

SELECT ... WHERE ordDate >= DATEADD(DAY,-1,@today)
             AND ordDate < @today;

You may get suggestions for other creative ways to find the rows from yesterday, but they'll be less safe and/or slower. You will probably be suggested things like this:

WHERE CONVERT(CHAR(10), ordDate, 112) = CONVERT(CHAR(10), GETDATE(), 112);
-- not sargable! Will cause a table/index scan even if there is an index on ordDate

WHERE ordDate BETWEEN <yesterday at mignight> AND <yesterday at 23:59:59.997>
-- unsafe! Will break if variables or underlying columns change data types

Some links:

• What do BETWEEN and the devil have in common?

• Bad habits to kick : mis-handling date / range queries

• What is the most efficient way to trim time from datetime?

On that last one, pay attention to the performance graphs, particularly how well the open-ended range does.

When you move to SQL Server 2008 or better, you can also consider the easier CONVERT(DATE approach, since this is one of the very few isolated scenarios where applying a function to a column is still sargable. However, you should also read this:

• Cast to date is sargable but is it a good idea?

To create a procedure for this, in SQL Server:

CREATE PROCEDURE dbo.myreport
  @ReportDate DATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;

  -- report on yesterday if parameter is NULL:
  SET @ReportDate = COALESCE(@ReportDate, DATEADD(DAY, -1, GETDATE()));

  -- remove time from datetime, in case time is passed:
  SET @ReportDate = DATEADD(DAY, DATEDIFF(DAY, '20000101', @ReportDate), '20000101');

  SELECT ordID, ordName, ordDate, ordShipping, ordStateTax, 
    ordHandling, ordTotal, ordDiscount, ordPrivateStatus 
  FROM dbo.orders 
  WHERE (ordStatus = 7)
    AND orderDate >= @ReportDate 
    AND orderDate < DATEADD(DAY, 1, @ReportDate);
END
GO

Now in your ASP code, after opening oConn, you do something like this:

set cmd = CreateObject("ADODB.Command")
cmd.Activeconnection = oConn
cmd.CommandText = "dbo.myreport"
cmd.commandType = 4 'adCmdStoredProc
dateParam = date()
cmd.parameters.Append ccmd.CreateParameter("ReportDate", adDate, 1, 0, dateParam)
set oRS = cmd.execute()
do while not oRS.eof 
  ...

Sorry, my classic ASP is rusty, so you may have to make adjustments. Thankfully, there are dozens and dozens of sites out there that include tutorials on using connection, command and recordset objects in classic ASP.