March 2, 2009 at 12:26 pm
Hi All,
I have one senario.
if i give like
3/5/09, then need to get 3/2 through 3/5.
if i give like
3/2/09 then i need to get 02/23/2009 through 02/27/2009
Inputs are welcome!
karthik
March 2, 2009 at 12:37 pm
karthikeyan (3/2/2009)
Hi All,I have one senario.
if i give like
3/5/09, then need to get 3/2 through 3/5.
if i give like
3/2/09 then i need to get 02/23/2009 through 02/27/2009
Inputs are welcome!
I'm confused. If you give the date 5 March 2009, you want 2 March 2009 - 5 March 2009 returned; but if you give 2 March 2009 you want 23 February 2009 - 27 February 2009 returns? Does not make sense. If the week starts on Monday, if you give the Monday date you should get the current week? Also, what about Saturday and Sunday dates, what should they return?
March 2, 2009 at 12:44 pm
Lynn,
if i give the start date of a week, then it should take the last week's start date and end date.
saturday & sunday should be eliminated.
i.e 23/feb/2009 to 27/feb/2009
why?
simply i am just taking the last week values to calculate the returns.
if give like 03/mar/2009 then it will take the returns from 02/mar/2009 to 03/mar/2009.
Please let me know if you are not still clear.
karthik
March 2, 2009 at 12:46 pm
What is the value returned by the following query on your system?
select @@datefirst
March 2, 2009 at 12:50 pm
Something like this:
declare @Date datetime;
select @Date = '3/5/09';
select
case datepart(weekday, @Date)
when 2 then dateadd(week, -1, @Date)
else dateadd(day, -1 * datepart(weekday, @Date), @Date) + 2
end as SDate,
case datepart(weekday, @Date)
when 2 then dateadd(week, -1, @Date) + 4
else dateadd(day, -1 * datepart(weekday, @Date), @Date) + 5
end as EDate;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 2, 2009 at 1:06 pm
GSquared (3/2/2009)
Something like this:
declare @Date datetime;
select @Date = '3/5/09';
select
case datepart(weekday, @Date)
when 2 then dateadd(week, -1, @Date)
else dateadd(day, -1 * datepart(weekday, @Date), @Date) + 2
end as SDate,
case datepart(weekday, @Date)
when 2 then dateadd(week, -1, @Date) + 4
else dateadd(day, -1 * datepart(weekday, @Date), @Date) + 5
end as EDate;
Works great, if @@datefirst = 7. That's why I asked what his server returned for this: select @@datefirst
March 2, 2009 at 4:51 pm
The following works regardless of the value of @@DATEFIRST
DECLARE @today datetime
DECLARE @weekday int
DECLARE @startInterval datetime
DECLARE @endInterval datetime
SELECT
@today = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0),
@weekday = DATEDIFF(day, 0, @today) % 7,
@startInterval = @today - (CASE WHEN @weekday = 0 THEN 7 ELSE @weekday END),
@endInterval = @today - (CASE WHEN @weekday = 0 THEN 3 ELSE 0 END)
SELECT @today, @startInterval, @endInterval
March 2, 2009 at 7:18 pm
Actually finding the beginning of current week should not be any different as finding of the beginning of current day.
DECLARE @Date datetime
SET @Date = GETDATE()
select @Date as [Date], DATEADD(wk, DATEDIFF(wk, 0, @Date-1), 0) as WeekStart
It's also @@DATEFIRST independent.
_____________
Code for TallyGenerator
March 2, 2009 at 10:07 pm
Based on Sergiy's code, I think this is what you are looking for:
DECLARE @Date datetime
SET @Date = GETDATE()
select
@Date as [Date],
DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) as WeekStart,
case when DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) >= dateadd(dd, datediff(dd, 0, @Date), 0)
then DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) - 7
else DATEADD(wk, DATEDIFF(wk, 0, @Date), 0)
end as StartDate,
case when
case when DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) >= dateadd(dd, datediff(dd, 0, @Date), 0)
then DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) - 7
else DATEADD(wk, DATEDIFF(wk, 0, @Date), 0)
end + 4 <= dateadd(dd, datediff(dd, 0, @Date), 0) then
case when DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) >= dateadd(dd, datediff(dd, 0, @Date), 0)
then DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) - 7
else DATEADD(wk, DATEDIFF(wk, 0, @Date), 0)
end + 4
else dateadd(dd, datediff(dd, 0, @Date), 0)
end as EndDate
SET @Date = GETDATE() + 1
select
@Date as [Date],
DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) as WeekStart,
case when DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) >= dateadd(dd, datediff(dd, 0, @Date), 0)
then DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) - 7
else DATEADD(wk, DATEDIFF(wk, 0, @Date), 0)
end as StartDate,
case when
case when DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) >= dateadd(dd, datediff(dd, 0, @Date), 0)
then DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) - 7
else DATEADD(wk, DATEDIFF(wk, 0, @Date), 0)
end + 4 <= dateadd(dd, datediff(dd, 0, @Date), 0) then
case when DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) >= dateadd(dd, datediff(dd, 0, @Date), 0)
then DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) - 7
else DATEADD(wk, DATEDIFF(wk, 0, @Date), 0)
end + 4
else dateadd(dd, datediff(dd, 0, @Date), 0)
end as EndDate
SET @Date = GETDATE() - 2
select
@Date as [Date],
DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) as WeekStart,
case when DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) >= dateadd(dd, datediff(dd, 0, @Date), 0)
then DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) - 7
else DATEADD(wk, DATEDIFF(wk, 0, @Date), 0)
end as StartDate,
case when
case when DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) >= dateadd(dd, datediff(dd, 0, @Date), 0)
then DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) - 7
else DATEADD(wk, DATEDIFF(wk, 0, @Date), 0)
end + 4 <= dateadd(dd, datediff(dd, 0, @Date), 0) then
case when DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) >= dateadd(dd, datediff(dd, 0, @Date), 0)
then DATEADD(wk, DATEDIFF(wk, 0, @Date), 0) - 7
else DATEADD(wk, DATEDIFF(wk, 0, @Date), 0)
end + 4
else dateadd(dd, datediff(dd, 0, @Date), 0)
end as EndDate
March 2, 2009 at 10:50 pm
Actully, Lynn, it's much easier.
If based on my code. 😎
DECLARE @Date datetime
SET @Date = GETDATE()
SELECT @Date as [Date], WeekStart, WeekStart + 4 as WeekEnd
FROM (
select DATEADD(wk, DATEDIFF(wk, 0, @Date - 2), 0) as WeekStart
) DT
_____________
Code for TallyGenerator
March 2, 2009 at 11:00 pm
Not quite. If the date is 3 March 2009, the end date should be 3 March 2009 not 6 March 2009. Based on the OP's requirements.
But, I must admit, much simplier and cleaner. I'd probably come up with something similiar if I worked a little harder. I was just trying to find at least one way to meet the OP's requirements.
March 3, 2009 at 3:37 am
Hi All,
Thanks for all your inputs!:)
I found the solution.
Declare @InputDate Datetime
select @InputDate = '02/mar/2009'
select case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,-7,@InputDate)
when upper(datename(dw,@InputDate)) = 'TUESDAY' then dateadd(dd,-1,@InputDate)
when upper(datename(dw,@InputDate)) = 'WEDNESDAY' then dateadd(dd,-2,@InputDate)
when upper(datename(dw,@InputDate)) = 'THURSDAY' then dateadd(dd,-3,@InputDate)
when upper(datename(dw,@InputDate)) = 'FRIDAY' then dateadd(dd,-4,@InputDate)
when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-5,@InputDate)
when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-6,@InputDate)
END as StartDate,
case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,-3,@InputDate)
when upper(datename(dw,@InputDate)) in('TUESDAY','WEDNESDAY','THURSDAY','FRIDAY') then @InputDate
when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-1,@InputDate)
when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-2,@InputDate)
END as EndDate
It worked well for all the cases.
karthik
March 3, 2009 at 4:28 am
The below query will give the current week's first date and lastdate.
Declare @InputDate Datetime
select @InputDate = '28/feb/2009'
select case when upper(datename(dw,@InputDate)) = 'MONDAY' then @InputDate
when upper(datename(dw,@InputDate)) = 'TUESDAY' then dateadd(dd,-1,@InputDate)
when upper(datename(dw,@InputDate)) = 'WEDNESDAY' then dateadd(dd,-2,@InputDate)
when upper(datename(dw,@InputDate)) = 'THURSDAY' then dateadd(dd,-3,@InputDate)
when upper(datename(dw,@InputDate)) = 'FRIDAY' then dateadd(dd,-4,@InputDate)
when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-5,@InputDate)
when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-6,@InputDate)
END as StartDate,
case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,4,@InputDate)
when upper(datename(dw,@InputDate)) = 'TUESDAY' then dateadd(dd,3,@InputDate)
when upper(datename(dw,@InputDate)) = 'WEDNESDAY' then dateadd(dd,2,@InputDate)
when upper(datename(dw,@InputDate)) = 'THURSDAY' then dateadd(dd,1,@InputDate)
when upper(datename(dw,@InputDate)) = 'FRIDAY' then @InputDate
when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-1,@InputDate)
when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-2,@InputDate)
END as EndDate
karthik
March 3, 2009 at 4:33 am
I am sure there should be some other way to do the same. Please post here if anybody has alternate query.
karthik
March 3, 2009 at 5:07 am
karthikeyan (3/3/2009)
Hi All,Thanks for all your inputs!:)
I found the TSQL 101 solution.
Declare @InputDate Datetime
select @InputDate = '02/mar/2009'
select case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,-7,@InputDate)
when upper(datename(dw,@InputDate)) = 'TUESDAY' then dateadd(dd,-1,@InputDate)
when upper(datename(dw,@InputDate)) = 'WEDNESDAY' then dateadd(dd,-2,@InputDate)
when upper(datename(dw,@InputDate)) = 'THURSDAY' then dateadd(dd,-3,@InputDate)
when upper(datename(dw,@InputDate)) = 'FRIDAY' then dateadd(dd,-4,@InputDate)
when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-5,@InputDate)
when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-6,@InputDate)
END as StartDate,
case when upper(datename(dw,@InputDate)) = 'MONDAY' then dateadd(dd,-3,@InputDate)
when upper(datename(dw,@InputDate)) in('TUESDAY','WEDNESDAY','THURSDAY','FRIDAY') then @InputDate
when upper(datename(dw,@InputDate)) = 'SATURDAY' then dateadd(dd,-1,@InputDate)
when upper(datename(dw,@InputDate)) = 'SUNDAY' then dateadd(dd,-2,@InputDate)
END as EndDate
It worked well for all the cases.
Karthik, did you bother to try Sergiy's solution? It's well worth spending a few minutes checking it out:
DROP TABLE #Dates
CREATE TABLE #Dates (aDate DATETIME)
INSERT INTO #Dates
SELECT TOP 100 GETDATE()+50-number
FROM Numbers
SELECT aDate, WeekStart, WeekEnd, datediff(dd, aDate, WeekStart),
DATENAME(dw,aDate), DATENAME(dw,WeekStart), DATENAME(dw,WeekEnd)
FROM (
SELECT aDate,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) AS WeekStart,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) + 4 AS WeekEnd
FROM #Dates ) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply