SQL Server : GETDATE not returning today's date-CodePudding

Below is my query. Everything is working except for the GETDATE function in the WHERE clause. It won't return today's date if I put the date in there like this: 7/12/22. It is a DATETIME column in the backend. Thanks in advance.

SELECT 
    acsMFG.dbo.production_posting_trans.item_no,
    SUM(acsMFG.dbo.production_posting_trans.good_quantity) AS [Good Qty],
    SUM(acsMFG.dbo.production_posting_trans.scrap_quantity) AS [Scrap Qty],
    acsAUTOSYS.dbo.inventory_master.selling_price
FROM
    acsAUTOSYS.dbo.inventory_master
FULL OUTER JOIN 
    acsMFG.dbo.production_posting_trans ON acsMFG.dbo.production_posting_trans.item_no = acsAUTOSYS.dbo.inventory_master.item_no
                                        AND acsAUTOSYS.dbo.inventory_master.company_code = acsMFG.dbo.production_posting_trans.company_code
WHERE  
    acsMFG.dbo.production_posting_trans.company_code = '10'
    AND acsMFG.dbo.production_posting_trans.production_date = GETDATE()
    AND acsMFG.dbo.production_posting_trans.posting_type = 'MMQ'
        OR acsMFG.dbo.production_posting_trans.posting_type = 'IRS'
        OR acsMFG.dbo.production_posting_trans.posting_type = 'PME'
GROUP BY 
    acsMFG.dbo.production_posting_trans.item_no,
    acsAUTOSYS.dbo.inventory_master.selling_price

CodePudding user response：

Well, when you say SELECT GETDATE(); what do you see? There is a time component there too, so if the data in the table is 2022-07-12 15:12 and you run the query at 2022-07-12 15:13, that's not a match.

If you want data from today, you need a range query:

WHERE col >= CONVERT(date, GETDATE())
  AND col <  DATEADD(DAY, 1, CONVERT(date, GETDATE()));

It is cleaner to use variables, e.g.

DECLARE @today date = GETDATE();
DECLARE @tomorrow date = DATEADD(DAY, 1, @today);

...
WHERE col >= @today
  AND col <  @tomorrow;

Don't get tempted into doing this:

WHERE CONVERT(date, col) = CONVERT(date, GETDATE());

It will work, but it's not fantastic.

For the actual problem with OR logic, you have:

  ... date clauses with AND ...
  AND acsMFG.dbo.production_posting_trans.posting_type='MMQ' 
  Or acsMFG.dbo.production_posting_trans.posting_type ='IRS' 
  Or acsMFG.dbo.production_posting_trans.posting_type ='PME'

I think you want:

  AND 
  (
    acsMFG.dbo.production_posting_trans.posting_type='MMQ' 
    Or acsMFG.dbo.production_posting_trans.posting_type ='IRS' 
    Or acsMFG.dbo.production_posting_trans.posting_type ='PME'
  )

As for aliases:

FROM
  acsAUTOSYS.dbo.inventory_master AS im
FULL OUTER JOIN 
  acsMFG.dbo.production_posting_trans AS ppt

Now all your references can be:

  AND 
  (
    ppt.posting_type='MMQ' 
    ppt.posting_type ='IRS' 
    Or ppt.posting_type ='PME'
  )
  GROUP BY 
    ppt.item_no, im.selling_price;

Or better:

  AND 
  (
    ppt.posting_type IN ('MMQ', 'IRS', 'PME')
  )
  GROUP BY 
    ppt.item_no, im.selling_price;

...so much more readable.

CodePudding user response：

Since GETDATE() returns the time too, you will never match. You need something like:

CAST(acsMFG.dbo.production_posting_trans.production_date AS Date) 
         = CAST(GETDATE() AS Date)