1
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

7
  • What is the type of ordDate? datetime or char? Commented Jan 29, 2015 at 17:47
  • Its datetime I just hope its a simple process, my (hunt it down, break it and hack it til it works) skills are basic. LOL Commented Jan 29, 2015 at 19:33
  • You already have an answer and it's good ;) Commented Jan 29, 2015 at 19:45
  • 3
    @Robert I think that is quite possibly the worst way to do that. Riding a tricycle to work "works" but it's going to take me all day. Other methods not only work, but they work better. Commented Jan 30, 2015 at 16:31
  • 1
    @AaronBertrand as a dedicated Tricyclist I am offended.
    – Zane
    Commented Jan 30, 2015 at 17:05

1 Answer 1

5

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:

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:

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.

2
  • I apologize because I need some hand-holding here. Here's the beginning part of my page. I am not familiar with "DECLARE" statements Dim oConn, oRS, sSQL Set oConn=Server.CreateObject("ADODB.Connection") Set oRS=Server.CreateObject("ADODB.Recordset") oConn.Open(sDSN) sSQL = "SELECT ordID, ordName, ordDate, ordShipping, ordStateTax, ordHandling, ordTotal, ordDiscount, ordPrivateStatus FROM orders WHERE (ordStatus = 7)" oRS.open sSQL, oConn,1,3 'first of all determine whether there are records If Not oRS.EOF Then Do While not oRS.eof Commented Jan 30, 2015 at 13:37
  • @Robert I suggest using stored procedures instead of dumping ad hoc SQL in classic ASP code. That said, you should be able to put exactly what I wrote inside the same SQL string and pass it to your conn object... Commented Jan 30, 2015 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.