March 4, 2009 at 12:46 am
Michael Valentine Jones (3/3/2009)
karthikeyan (3/3/2009)
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
This is quite a bit simpler, and it works the same no matter what the setting of language or datefirst is.
select
Monday = dateadd(dd,(datediff(dd,-53690,a.DT)/7)*7,-53690) ,
Friday = dateadd(dd,(datediff(dd,-53690,a.DT)/7)*7,-53690)+4
from
(-- Test Data
select DT = convert(datetime,'20090302')union all
select convert(datetime,'20090303')union all
select convert(datetime,'20090304')union all
select convert(datetime,'20090305')union all
select convert(datetime,'20090306')union all
select convert(datetime,'20090307')union all
select convert(datetime,'20090308')union all
select convert(datetime,'20090309')
) a
Results:
Monday Friday
----------------------- -----------------------
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-09 00:00:00.000 2009-03-13 00:00:00.000
(8 row(s) affected)
MVJ,
Thanks for your code. But do i need to hardcode the dates? if i want to run the same code each and every week, then i have to modify the query. Right?
karthik
March 4, 2009 at 12:50 am
Lynn Pettis (3/3/2009)
Yep, your right, I had a slight mistake in my code. Here is the corrected code:
declare @pDate datetime;
set @pDate = getdate();
select
dateadd(wk, datediff(wk, 0, @pDate - 2), 0) as BeginDate,
case when dateadd(wk, datediff(wk, 0, @pDate - 2), 0) + 4 > dateadd(dd, datediff(dd, 0, @pDate), 0)
then dateadd(dd, datediff(dd, 0, @pDate), 0)
else dateadd(wk, datediff(wk, 0, @pDate - 2), 0) + 4
end as EndDate;
Also, you will notice the the end date is not the following Friday until you get to Friday, which is what the OP requested.
Yes, your earlier code will fail if i give 02/mar/2009 as the input. This one works fine.
karthik
March 4, 2009 at 12:50 am
karthikeyan (3/4/2009)
Michael Valentine Jones (3/3/2009)
karthikeyan (3/3/2009)
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
This is quite a bit simpler, and it works the same no matter what the setting of language or datefirst is.
select
Monday = dateadd(dd,(datediff(dd,-53690,a.DT)/7)*7,-53690) ,
Friday = dateadd(dd,(datediff(dd,-53690,a.DT)/7)*7,-53690)+4
from
(-- Test Data
select DT = convert(datetime,'20090302')union all
select convert(datetime,'20090303')union all
select convert(datetime,'20090304')union all
select convert(datetime,'20090305')union all
select convert(datetime,'20090306')union all
select convert(datetime,'20090307')union all
select convert(datetime,'20090308')union all
select convert(datetime,'20090309')
) a
Results:
Monday Friday
----------------------- -----------------------
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-02 00:00:00.000 2009-03-06 00:00:00.000
2009-03-09 00:00:00.000 2009-03-13 00:00:00.000
(8 row(s) affected)
MVJ,
Thanks for your code. But do i need to hardcode the dates? if i want to run the same code each and every week, then i have to modify the query. Right?
The data provided in these posts are test data. Any of the code you use may need to be modified for your actual use.
March 4, 2009 at 1:48 am
Lynn Pettis (3/4/2009)
karthikeyan (3/4/2009)
Chris Morris (3/3/2009)
karthikeyan (3/3/2009)
His method is working fine. I have tested it.Actually my requiremrnt is little bit different what he has given.
Can you demonstrate please, Karthik? I'd be very keen to see your solution, with a description of how the revised requirements differ from the original. New ideas are welcome!
Sure Chris.
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
It is working fine. There is no doubt about it. But the output is
2009-03-04 02:30:31.0832009-03-02 00:00:00.0002009-03-06 00:00:00.000
But my requirement is slightly different .It should display the given date as the weekend.
i.e
2009-03-04 02:30:31.0832009-03-02 00:00:00.0002009-03-04 02:30:31.083
Thats what i have mentioned.
Yes, I am quite aware of your requirements as i have pointed them out myself several times. Have you tried the code I provided in this post?
Yes. Thats what my requirement. It is working fine.
karthik
March 4, 2009 at 2:00 am
Michael Valentine Jones (3/3/2009)
Chris Morris (3/3/2009)
Michael, you might want to check your code, too...
DROP TABLE #DatesCREATE TABLE #Dates (aDate DATETIME)
INSERT INTO #Dates
SELECT TOP 100 DATEADD(dd,DATEDIFF(dd, 0, getdate()),50-number)
FROM Numbers
SELECT aDate, SergiyStart, SergiyEnd, CJMStart, CJMEnd, MVJStart, MVJEnd,
datediff(dd, aDate, SergiyStart) AS DaysToStartweek,
DATENAME(dw,aDate) AS [Day]
FROM (
SELECT aDate,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) AS SergiyStart,
DATEADD(wk, DATEDIFF(wk, 0, aDate - 2), 0) + 4 AS SergiyEnd,
aDate - ISNULL(NULLIF(DATEDIFF(dd, 0, aDate) % 7, 0), 7) AS CJMStart,
aDate - ISNULL(NULLIF(DATEDIFF(dd, 0, aDate) % 7, 0), 7) + 4 AS CJMEnd,
dateadd(dd,(datediff(dd,-53690,aDate)/7)*7,-53690) AS MVJStart,
dateadd(dd,(datediff(dd,-53690,aDate)/7)*7,-53690)+4 AS MVJEnd
FROM #Dates ) d
Cheers
ChrisM
The code you posted doesn't work, unless you happen to have a table named Numbers in the current database.
After I corrected that, it looks to me like my code is the only one returning the correct results:
The latest Monday on or before the current date, and the Friday following that Monday.
The other two seem to be returning the prior Monday when the current date is Monday.
However, the OPs statements about the actual requirements are ambiguous and confusing, so maybe we are working towards different objectives.
MVJ,
I am sorry for not explaining my requirement clearly.
if i give any date between Monday to Friday then the corresponding date's week start date and the given date should be displayed.
say for example,
04/mar/2009 week start date is 02/mar/2009. so i have to display
02/mar/2009 - start date
04/mar/2009 - end date (given date)
suppose if i give 02/mar/2009 (or any date which fall on Monday), then the previous week's start date and end date should be displayed.
say for example,
if i give like 02/mar/2009 which is Monday, so the previous week's start date is 23/feb/2009 and end date is 27/feb/2009. I have to display it.
like that if i give 28/feb/2009 or 01/mar/2009 then again it should display 23/feb/2009 and 27/feb/2009.
I hope i have explained clearly. Please let me know if you still unclear.
karthik
March 4, 2009 at 3:05 am
Michael Valentine Jones (3/3/2009)
The code you posted doesn't work, unless you happen to have a table named Numbers in the current database.
Read about the tally (numbers) table here[/url]. In this instance it provides testing data.
Michael Valentine Jones (3/3/2009)
However, the OPs statements about the actual requirements are ambiguous and confusing, so maybe we are working towards different objectives.
Yep, but it looks like it's finally (kinda) cleared up. In any case, all of the solutions posted - yours, Lynn's, Sergiy's or mine, could be very quickly adapted by any half-competent codemonkey to meet a wide range of requirements.
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
March 4, 2009 at 4:38 pm
Chris Morris (3/4/2009)
Michael Valentine Jones (3/3/2009)
The code you posted doesn't work, unless you happen to have a table named Numbers in the current database.Read about the tally (numbers) table here[/url]. In this instance it provides testing data.
I know what a number table is. I posted my own script for a number table function on the link below four years ago. When I post code like that, I usually at least provide a link to any non-standard objects used by the script.
Number Table Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685
You might want to look at the function; in a lot of cases it may be faster to get the exact list of numbers that you need compared to using a static number table.
For example, it you need numbers from -4357 to 13 inclusive, you could just do this:
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(-4357,13)
Or this, both will produce the same output:
select NUMBER from dbo.F_TABLE_NUMBER_RANGE(13,-4357)
March 5, 2009 at 1:26 am
Michael Valentine Jones (3/4/2009)
I know what a number table is.
Yeah I know Michael, just joshing mate, I've learned tons of stuff from you over the years. Thanks for the link, I had a good look at your function - something else to add to the toolbox for SQL2k.
Cheers
ChrisM
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
March 6, 2009 at 3:48 am
Thanks a lot to all.
I just wanted to know what about my solution? won't it be a god solution? I think 'YES'. Becuase i have used UPPER function. Right? UPPER will consume some amount of I/O. Right?
karthik
March 6, 2009 at 4:06 am
karthikeyan (3/6/2009)
Thanks a lot to all.I just wanted to know what about my solution? won't it be a god solution? I think 'YES'. Becuase i have used UPPER function. Right? UPPER will consume some amount of I/O. Right?
Well I dunno Karthik there's a cardinal and one or two saints on this forum, perhaps it's time for a deity too!
Your solution works, but is it a good one? How good it is depends on how well it performs relative to the other solutions which have been posted - four or five. I couldn't possibly deprive you of the opportunity to test them all relative to each other - the pleasure and satisfaction is all yours. However, without testing them, I'd place a bet on Sergiy's being the most performant, and, I'm sorry, but yours being the least - even though you've used the UPPER function.
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
March 6, 2009 at 4:57 am
Chris Morris (3/6/2009)
karthikeyan (3/6/2009)
Thanks a lot to all.I just wanted to know what about my solution? won't it be a god solution? I think 'YES'. Becuase i have used UPPER function. Right? UPPER will consume some amount of I/O. Right?
Well I dunno Karthik there's a cardinal and one or two saints on this forum, perhaps it's time for a deity too!
Your solution works, but is it a good one? How good it is depends on how well it performs relative to the other solutions which have been posted - four or five. I couldn't possibly deprive you of the opportunity to test them all relative to each other - the pleasure and satisfaction is all yours. However, without testing them, I'd place a bet on Sergiy's being the most performant, and, I'm sorry, but yours being the least - even though you've used the UPPER function.
ah...it is good.. not god.
😀
karthik
March 6, 2009 at 6:51 am
Chris Morris (3/6/2009)
karthikeyan (3/6/2009)
Thanks a lot to all.I just wanted to know what about my solution? won't it be a god solution? I think 'YES'. Becuase i have used UPPER function. Right? UPPER will consume some amount of I/O. Right?
Well I dunno Karthik there's a cardinal and one or two saints on this forum, perhaps it's time for a deity too!
Your solution works, but is it a good one? How good it is depends on how well it performs relative to the other solutions which have been posted - four or five. I couldn't possibly deprive you of the opportunity to test them all relative to each other - the pleasure and satisfaction is all yours. However, without testing them, I'd place a bet on Sergiy's being the most performant, and, I'm sorry, but yours being the least - even though you've used the UPPER function.
I have to agree with Chris. You need to test the solutions offered and decide which one is the best for your application.
March 12, 2009 at 12:50 am
Hi All,
A small modification in the requirement. Earlier we showed if user has selected 'Monday' then we will populate previous week's
Edited: date's
start date and end date . Now users asking to show if it is 'Monday' then show the week start date as 'Monday' and end date as 'Monday'. i.e same date should be week start date and end date.
Inputs are welcome!
Meantime I am also trying to modify the code.
karthik
March 12, 2009 at 4:33 am
All the inputs are 2 bages back.
Please make an effort to read what people answer on your questions.
_____________
Code for TallyGenerator
March 13, 2009 at 3:45 am
DECLARE@InputDate DATETIME
SET@InputDate = '02/mar/2009 13:15'
SELECT@InputDate AS theDay,
DATEADD(DAY, DATEDIFF(DAY, '17530102', @InputDate) / 7 * 7, '17530101') AS StartDate,
DATEADD(DAY, DATEDIFF(DAY, '17530102', @InputDate) / 7 * 7, '17530108') AS EndDate
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply