January 28, 2012 at 4:32 pm
Comments posted to this topic are about the item UDF_GET_NEXT_WEEKDAY
May 11, 2012 at 3:56 am
Try this as well.But see what is you first day of Month.
declare @date date, @day varchar(10)
set @date = dateadd(dd,-5,getdate())
set @day = 'Saturday'
select dateadd(dd,case when val <= dwpart then 7-dwpart + val else 7-dwpart end,dt)
,*
from (
select @date dt
--,datename(dw,@date) dwname
,case @day
when 'Sunday' then 1
when 'Monday' then 2
when 'Tuesday' then 3
when 'Wednesday' then 4
when 'Thursday' then 5
when 'Friday' then 6
when 'Saturday' then 7
end val
,datepart(dw,@date) dwpart
) dta
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 11, 2012 at 3:57 am
Sorry Firast day of week not Month*
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
May 11, 2016 at 7:26 am
Thanks for the script.
July 27, 2016 at 4:23 am
how to get a first week day start on Monday ignore <Set DateFirst>
----- start week date on Monday
declare @CurrentDate datetime
set @CurrentDate = '2016-07-24'
set datefirst 3
select dateadd(day,case when ((DATEPART(dw, @CurrentDate) + @@DATEFIRST) % 7) =0 then -6 else -1* (((DATEPART(dw, @CurrentDate) + @@DATEFIRST) % 7)-1) end ,@CurrentDate) as WeekStartDay
-------- test --------
select
DATENAME(weekday,WeekStartDay) as WeekStartDayName
,RE2.*
from
(select
dateadd(day,case when ((DATEPART(dw, CurrentDate) + @@DATEFIRST) % 7) =0 then -6 else -1* (((DATEPART(dw, CurrentDate) + @@DATEFIRST) % 7)-1) end ,CurrentDate) as WeekStartDay
,DATENAME(weekday,CurrentDate) CurrentDateDayName
,DATEPART(weekday,CurrentDate) CurrentDateWeekDay
,DATEPART(wk, CurrentDate) as WeekNumber
,RE1.*
from
(
select
dateadd(DAY,RowNumber-1,StartDate) as CurrentDate
,@@datefirst as DateFirstSetUp
,RW.*
,FD.*
from
(select top 20 Row_Number() over(order by (select getdate()) ) as RowNumber from sysobjects ) as RW
cross join (select isnull(@CurrentDate,'2016-07-23') as StartDate) as FD
) as RE1
) as RE2
order by RowNumber
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply