Previous week

I have a process that run in Monday morning at 8am and I want to get "send_date" from a previous week. It is a datetime sample 2010-01-23 00:00:00.000 . What would the select statement be like?

select *from table where send_date is from previouse week.
Who is Participating?
Scott PletcherConnect With a Mentor Senior DBACommented:
FROM dbo.tablename
    send_date >= DATEADD(DAY, -7, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AND
    send_date < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

You should avoid using any function, such as DATEDIFF(), on the column itself if at all possible, because if any function is used, SQL won't be able to fully utilize the index on that column.
Surendra NathConnect With a Mentor Technology LeadCommented:
you want to get the the send_date column of previous_week's from a table? if so, please do the below

in the below scenario I am assuming two things

1) your table name is X_table
2) your datetime sample or what ever is stored in a column sample_dt

replace them as in your actual stuff.

select send_date from x_table
where abs(datediff(dd,sample_dt,getdate())) = 7

Open in new window

sachitjainConnect With a Mentor Commented:
select * from table where Convert(varchar,send_date,106) = Convert(varchar,dateadd(day, -7, getdate()), 106)
SharathConnect With a Mentor Data EngineerCommented:
see this
  FROM dbo.tablename 
 WHERE send_date >= DATEADD(day, -7, CONVERT(DATE, GETDATE())) 
   AND send_date < CONVERT(DATE, GETDATE()) 

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.