DATEADD(day, -7, GETDATE()) - Does it take time into account?

sql-server tsql date dateadd
nak5120 picture nak5120 · Jul 19, 2017 · Viewed 8.6k times · Source

I am writing a SQL query in Aginity through Amazon Redshift to extract the last 7 days of data. The Date column which I am calling is in the variable type:

DATE

An example output is this:

5/30/2017 0:00

When I call the below function, does it matter what time of day I run this query or will it always take the full day's worth of data?

WHERE Date >= DATEADD(day,-7, GETDATE())

Answer

Gordon Linoff picture Gordon Linoff · Jul 19, 2017

Yes, this includes the current time component when subtracting 7 days. To get rid of that, convert to a date:

WHERE Date >= CAST(DATEADD(day, -7, GETDATE()) as DATE)

Related questions

How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
Best approach to remove time part of datetime in SQL Server

Which method provides the best performance when removing the time portion from a datetime field in SQL Server? a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0) or b) select cast(convert(char(11), getdate(), 113) as datetime) The second method does send a …

Read More
MS SQL compare dates?

I have 2 dates (datetimes): date1 = 2010-12-31 15:13:48.593 date2 = 2010-12-31 00:00:00.000 Its the same day, just different times. Comparing date1 and date2 using <= doesnt work because of the date1 time. So date1 <= date2 is wrong, but should be true. …

Read More