Post reply

Need advice on returning data based on specific day and time ranges

  • April 8, 2011 at 10:44 am

    #235310

    Hi, looking for some insight to see if anyone else has had to solve a problem that I'm encountering. In SQL Server 2008, I have a nightly Agent job that was created way back in SQL Server 2000. It has been kicked around to convert this to something else but never has been done so I'm stuck with working with what I've got. Currently the SQL in the job selects data where the time is between '08:00:00' and '16:30:00'. This pulls back all data for all days of the week. Now I have a new requirement to only select records where the day is a Monday, Tuesday, Wednesday, Thursday or Friday with a time stamp from 6:00 am – 10:00 pm OR a Saturday with a time stamp from 6:00 am – 3:00 pm. Can I use datepart and CASE statements to solve this? I'm still relatively new to SQL coding and am trying to wrap my arms around the most efficient way to solve/code this.

    Any advice would be greatly appreciated.

    thanks!

  • April 8, 2011 at 12:28 pm

    #1309347

    Something like this?

    SELECT * FROM YOURTABLE

    WHERE

    ( DAY(YOURCOLUMN) IN ( 2, 3, 4, 5 ,6) AND YOURCOLUMNTIME BETWEEN '10' AND '6' )

    OR ( DAY(YOURCOLUMN) IN ( 2, 3, 4, 5 ,6) AND YOURCOLUMNTIME BETWEEN '10' AND '3' )

  • April 8, 2011 at 12:30 pm

    #1309348

    Yes! This is what I've been trying and it works, but it takes a really long time; which I believe is due to the OR condition. I didn't know if a subquery of some sort would be better or....

  • April 11, 2011 at 9:42 am

    #1309920

    The function "DAY" will return the day of the month, not the weekday. This will not help you determine whether the day is Monday or Thursday, etc.

    You have you use DATEPART(weekday,COLUMNNAME) to determine what day it is.

    Your results:

    1= Sunday

    2= Monday

    3=Tuesday

    4=Wednesday

    5=Thursday

    6=Friday

    7=Saturday

    You can also use the DATENAME(weekday,COLUMNNAME) to determine the actual name string of the day.

    PRINT DATENAME( weekday,GETDATE())

    SELECT *

    FROM YOURTABLE

    WHERE DATEPART(WEEKDAY, YOURCOLUMN) BETWEEN 2 AND 6 --THIS WILL FILTER MONDAY THROUGH FRIDAY

    OR DATEPART(WEEKDAY, YOURCOLUMN) IN ( 1, 7 ) --THIS WILL FILTER SATURDAY AND SUNDAY

    QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply