February 9, 2010 at 11:46 am
This is T-SQL so I hope I am in the right place, if not please let me know where I should move this to.
I am looking to push some start dates out to the following monday at 9am. Anything with a report date of Sat or Sun (I have this code down - [font="Courier New"]DATEPART(dw, @ReportDate) in (1,7)[/font]) gets it's "targstartdate" = next monday at 9am.
Am I forced to go through a CASE stmt and check to see which day of the week my report date is and add the appropriate number of days to all 7 CASE WHEN conditions? I was hoping there might be a shorter (one line of code asking too much???) way of saying "next monday at 9am". I am having trouble coming up with anything!
Thanks in advance!
Sharon
February 9, 2010 at 12:23 pm
Did you try putting it in a udf ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 9, 2010 at 4:04 pm
The formula is fairly simple:
Set datefirst 7;
Declare @day int
,@today datetime
,@nextMonday datetime;
Set @day = 2 -- Monday is day 2 when datefirst is Sunday (7)
Set @today = getdate()); -- replace with your date here
Set @nextMonday = dateadd(day, 7 + (@day - datepart(weekday, @today)), @today); -- Next Monday
Select @today, datename(weekday, @today), @nextMonday, datename(weekday, @nextMonday);
This is dependent upon the datefirst setting - but you can take that into consideration for this.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 9, 2010 at 6:20 pm
I'm not sure you clearly explained your requirements.
Do you want to push to next Monday at 9:00 am always, or only when it is Saturday or Sunday?
The code below shows an example of each. Also, note that this code does not depend on the setting of DATEFIRST.
select
*,
DayOfWeek = left(datename(dw,Mydate),9),
[NextMondayAt9AM] =
dateadd(dd,((datediff(dd,0,MyDate)+7)/7)*7,'09:00'),
[ScheduleDate] =
case when datediff(dd,0,MyDate)%7<5 then Mydate
else dateadd(dd,((datediff(dd,0,MyDate)+7)/7)*7,'09:00') end
from
(
select Mydate = getdate() union all
select Mydate = getdate()+1 union all
select Mydate = getdate()+2 union all
select Mydate = getdate()+3 union all
select Mydate = getdate()+4 union all
select Mydate = getdate()+5 union all
select Mydate = getdate()+6 union all
select Mydate = getdate()+7
) a
Results:
Mydate DayOfWeek NextMondayAt9AM ScheduleDate
----------------------- --------- ----------------------- -----------------------
2010-02-09 20:11:14.770 Tuesday 2010-02-15 09:00:00.000 2010-02-09 20:11:14.770
2010-02-10 20:11:14.770 Wednesday 2010-02-15 09:00:00.000 2010-02-10 20:11:14.770
2010-02-11 20:11:14.770 Thursday 2010-02-15 09:00:00.000 2010-02-11 20:11:14.770
2010-02-12 20:11:14.770 Friday 2010-02-15 09:00:00.000 2010-02-12 20:11:14.770
2010-02-13 20:11:14.770 Saturday 2010-02-15 09:00:00.000 2010-02-15 09:00:00.000
2010-02-14 20:11:14.770 Sunday 2010-02-15 09:00:00.000 2010-02-15 09:00:00.000
2010-02-15 20:11:14.770 Monday 2010-02-22 09:00:00.000 2010-02-15 20:11:14.770
2010-02-16 20:11:14.770 Tuesday 2010-02-22 09:00:00.000 2010-02-16 20:11:14.770
(8 row(s) affected)
February 10, 2010 at 7:13 am
Thanks everyone! This is perfect! Sorry I wasn't too clear...I only want to push out the start date if the report date falls on Sat or Sun. I should be able to use the suggestions above for my needs. Thank you again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply