How to go back two days from a date in sql?

I wrote an SQL query that allows me to get the sales of certain stores. My query runs every mornings and I would like to get the sales from 2 days ago at runtime.

For example if my query runs tomorrow morning, on 08/12, I would like to have the sales whose value in the column "GP_HEURECREATION" starts with “20200612”, to have all the sales of the whole day.

The GP_HEURECREATION column has a format like this: "20200612 00:00:00" and is of the DATE type.

I tried with NOW() and DATEADD() but I have 2018 values that stand out for example. How can I get the values only two days before the query is executed?

SELECT 
    T_ETABLISSEMENT, ET1.ET_LIBELLE AS C1, GL_ETABLISSEMENT, 
    GP_HEURECREATION, GP_REFINTERNE, GL_CODEARTICLE, 
    LIBDIM2, LIBDIM1, GL_QTEFACT, GL_PUTTC, 
    (GL_TOTALHT * GP_COTATIONDOS) AS TOTALHTDEV, GL_DPR, GL_DEVISE, 
    GL_NATUREPIECEG, GA_LIBELLE 
FROM 
    GCLIGNEARTDIM  
LEFT OUTER JOIN 
    PGI_LOOKUP(TTETABLISSEMENT) ET1 ON GL_ETABLISSEMENT = ET1.ET_ETABLISSEMENT 
WHERE 
    (GP_HEURECREATION <= DATEADD(day, -2, GETDATE())
    AND (GL_NATUREPIECEG = "FFO") 
    AND GL_ETABLISSEMENT = "20897", "10519", "20267", "26451", "20269", "26078", "28047", "20900", "28085", "24984", "27113", "20268", "19994", "28450", "26876", "24063", "18066", "3220"
ORDER BY 
    GP_REFINTERNE

Answer

The syntax of your existing query suggests SQL Server. If you want records that belong to day -2, you can do:

where gp_heurecreation >= dateadd(day, -2, convert(date, getdate()))
  and gp_heurecreation <  dateadd(day, -1, convert(date, getdate()))

If gp_heurecreation has no time component (in SQL Server, that’s a date datatype), this is simpler:

where gp_heurecreation = dateadd(day, -2, convert(date, getdate()))

Advertisement

Answer