Hi guys,
I have a formula .ACTION_DT between DATEADD(day,-7, GETDATE()) and getdate()... this gives me data up to 9/26/18 but missing 9/25, is this the best way to do last 7 days or last full week? I am getting 28 rows instead of 31 rows??
Thanks.
Pasi
No - this isn't the best way to get the past nn days/weeks/months. First - what is the data type of ACTION_DT? If it is a date then you can safely use BETWEEN - but if it is a datetime you will not be able to get the correct data.
Instead of worrying about whether or not you can use BETWEEN - it is much easier to do this:
ACTION_DT >= dateadd(day, datediff(day, 0, getdate()) - 7, 0)
AND ACTION_DT < dateadd(day, datediff(day, 0, getdate()), 0)
This will return all datetimes from 7 days ago through today - not including today. Unless you specifically want to include everything up to the point in time the query is run - and you expect to have different results every time this is run then you should not include today's data.
If ACTION_DT is a date column and you really want to use BETWEEN and include today:
ACTION_DT BETWEEN cast(getdate() - 7 AS date) AND cast(getdate() AS date)
If the 7 days includes today, then you would only go back 6 days:
ACTION_DT BETWEEN cast(getdate() - 6 AS date) AND cast(getdate() AS date)
Or - if you want the past 7 days not including today:
ACTION_DT BETWEEN cast(getdate() - 7 AS date) AND cast(getdate() - 1 AS date)
The key is to insure that you are not including the time portion of a datetime. DATEADD(day,-7,GETDATE()) returns a datetime value 7 days ago at this time - which will be different every time it is run today.
Thanks Jeff, the field is a "date" not date time. Yes I want everything from today going back 7 days or 6 days including today.its interesting that my formula which you thing it should calculate correctly and is not! it is interesting that SQL works strange ways for calculating dates, times, etc..
Thanks for the tips!
Pasi
Not surprised yours isn't doing what you expected...
Since the column is defined as a DATE - it will be implicitly converted to a datetime (which has higher precedence). Therefore - 2018-09-26 becomes 2018-09-26 00:00:00.000 which is not between '2018-09-26 12:37:05.513' AND '2018-10-03 12:37:05.513'
This is why it is very important to know what data type of the column - and validating the expression you are using is valid for that data type.
Ok Thanks!
