SQLTeam.com | Weblogs | Forums

Where is my problem here getdate()-1


#1

hello

i have the following code and i have the data for today and tomorrow but i don't have data for yesterday but i needed data for yesterday as well
do you know where my problem is
thank you

SELECT Teil_Nummer, Bedarfs_Typ, Menge_Bedarf, Datum_Bedarf
FROM dbo.Teile_Bedarf
GROUP BY Teil_Nummer, Bedarfs_Typ, Datum_Bedarf, Menge_Bedarf
HAVING (Bedarfs_Typ = '10') AND (Datum_Bedarf <= GETDATE() + 1) AND (Datum_Bedarf >= GETDATE() - 1) AND (Teil_Nummer LIKE '[^0]%')


#2

Please confirm, whether the values stored in the column "Datum_Bedarf" include time part also or only date part.

Eg: "2015-10-05 05:32:55.840" (Date part and Time part)
or
"2015-10-05 00:00:00" (Only date part)


#3

just +1, -1 will give the date along with timestamp.
Please convert to date and try

select cast(getdate()-1 as date), cast(getdate()+1 as date)

#4
SELECT Teil_Nummer, Bedarfs_Typ, Menge_Bedarf, Datum_Bedarf
FROM dbo.Teile_Bedarf
CROSS APPLY (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS Today_Midnight
) AS assign_alias_names
WHERE (Bedarfs_Typ = '10') AND 
      (Datum_Bedarf <= Today_Midnight + 1) AND 
      (Datum_Bedarf >= Today_Midnight - 1) AND 
      (Teil_Nummer LIKE '[^0]%')
GROUP BY Teil_Nummer, Bedarfs_Typ, Datum_Bedarf, Menge_Bedarf