15 days before today in SQL Server

Question

I am trying to find the list of specific job # from load date in our database with the specific condition and I have to use inner join as well. i want to have last 15 days worth of Job # in my store procedure. How do I set up the LoadDate that will change auto for last 15 days only.

Here is my query:

select pr.Job_Number,
       Count(ItemCode1) as [Total Records], 
       si.PackageComplete
from 
    processed_record pr
inner join scanner_2 si on pr.ItemCode1 = si.ItemCode1
where 
    pr.Format_Name like '%Lin%' and pr.LoadDate >= '03/01/2016' 
group by 
    pr.Job_Number, si.PackageComplete
order by 
    si.PackageComplete, pr.Job_Number

Set [Bad Habits to kick - putting NOLOCK everywhere](http://blogs.sqlsentry.com/aaronbertrand/bad-habits-nolock-everywhere/) - it is *not recommended* to use this table hint everywhere — marc_s, Mar 30 '16 at 14:25
why don't you use DATEADD(day,-15,getdate()) instead of '03/01/2016'? — alessalessio, Mar 30 '16 at 14:26
Possible duplicate of [How to select last one week data from today's date](http://stackoverflow.com/questions/13266130/how-to-select-last-one-week-data-from-todays-date) — Tab Alleman, Mar 30 '16 at 15:14

score 1 · Answer 1 · answered Mar 30 '16 at 14:39

1

Your query should be as follow:

select pr.Job_Number,
       Count(ItemCode1) as [Total Records], 
       si.PackageComplete
from 
    processed_record pr
inner join scanner_2 si on pr.ItemCode1 = si.ItemCode1
where 
    pr.Format_Name like '%Lin%' and pr.LoadDate >= DATEADD(DAY,-15,GETDATE())
group by 
    pr.Job_Number, si.PackageComplete
order by 
    si.PackageComplete, pr.Job_Number

GETDATE() will get the current date and DATEADD() function will add (subtract) -15 days.

answered Mar 30 '16 at 14:39

Rafael Dontal Gonçalez

140
6

in loaddate (datetime, null) column values are like 2015-11-18 15:47:20.310 and so I am not getting expected result using the above query. – Binit Patel Mar 30 '16 at 15:16
I think your question is not clear. You asked for 15 days of data and in your query you aren't selecting the loaddate column. What are you expecting as output? Why you say "2015-11-18 15:47:20.310" is a problem? – Rafael Dontal Gonçalez Mar 31 '16 at 07:00
we have the specific column name "LoadDate" which capture the date and time in the above example for each record. – Binit Patel Apr 01 '16 at 17:26

score 0 · Accepted Answer · answered Mar 30 '16 at 14:30

You could use

CONVERT(date, DATEADD(DAY, -15, GETDATE()))

insted your fix Date Value.

With this code you recieve the current Date GetDate() and substract 15 Days DateAdd(day, -15, DateValue)

Finaly you Convert it into Date Typ Convert(date, value) otherwise you would get the current time, too.

15 days before today in SQL Server

2 Answers2