smith February 2016

Fetching record of last 48 hrs from SQL

I have table1 with with the columns DATETIME, ID and Status.

I will call my stored procedure after specific hours and I want to fetch records of last 48 non weekend hours.

I tried to do it by writing case for each day of getdate().

I want to know what is the best way to do this.

Answers


Gordon Linoff February 2016

To avoid weekends, you can use a case or other logic in the where. Your logic isn't 100% clear on what to do on weekend days. But here is an approach:

where (datename(wk, getdate()) in ('Sunday', 'Monday', 'Tuesday') and datetime >= dateadd(hour, 48 + 48, getdate()) ) or
      (datename, wk, getdate()) in ('Wednesday', 'Thursday', 'Friday') and datetime >= dateadd(hour, 48, getdate()) or
      (datename , wk, getdate()) in ('Saturday') and datetime >= dateadd(hour, 24 + 48, getdate());

Post Status

Asked in February 2016
Viewed 1,838 times
Voted 10
Answered 1 times

Search




Leave an answer