SQL Server : GETDATE not returning today's date

July 12, 2022

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

>Solution :

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:

WHERE acsMFG.dbo.production_posting_trans.company_code='10' 
  AND acsMFG.dbo.production_posting_trans.production_date >= CONVERT(date, GETDATE()) 
  AND acsMFG.dbo.production_posting_trans.production_date < DATEADD(DAY, 1, CONVERT(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'

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'
  )