February 29, 2012 at 5:13 pm
how i can get the week range for
date weekrange
2012-05-30 5/27-6/2
2012-06-02 5/27-6/2
2012-11-07 11/4-11/10
2012-11-13 11/11-11/17
i want to update weekrange as above as per the values in date column.i have thousands of rows in date column and i want to update the weekrange as per date column
Thanks
February 29, 2012 at 6:54 pm
February 29, 2012 at 9:04 pm
I think this can help you understand what i want
create table dt(dates datetime,wrange varchar(40))
insert into dt values('2012-02-29 00:00:00.000',null),
('2012-05-30 00:00:00.000',null),
('2007-01-01 00:00:00.000',null),
('2007-01-08 00:00:00.000',null)
select * from dt
i want to update the wrange column in such a way that it should show me that in which week range values of dates column is .
for e.g value date 2012-05-30 is in the week which starts on 05/27(sunday)( and week ends in 06/02saturday)(for better clerfification you can check calender ).
similarly date 2012-02-29(today's date) is in the week range which starts on 02/26(sunday) and this week ends on 03/03(saturday).so in wrange column for today's date
in want 02/26-03/03
so in dt table after updating wrange column i want result which should be like
insert into dt values ('2012-02-29 00:00:00.000','02/26-03/03'),
('2012-05-30 00:00:00.000','05/27-06/02'),
('2007-01-01 00:00:00.000','1/1-1/7'),
('2007-01-08 00:00:00.000','1/8-1/14')
i think we can use that query but it is not giving me that result
update dt
set WRange=WkR from
(select dates, convert(varchar,datepart(m,min(dates))) + '/' + convert(varchar,datepart(d,min(dates)))
+ ' - ' +convert(varchar,datepart(m,max(dates))) + '/' + convert(varchar,datepart(d,max(dates))) as 'WkR'
from dt)
group by [dates]
February 29, 2012 at 9:31 pm
This shows how to find the first and last day of the week for a given date, with the week starting on Sunday and ending on Saturday. You can refer to the CONVERT function in SQL Server Books Online to figure out how to format them in your desired format.
select
a.*,
WeekStarting= dateadd(dd,(datediff(dd,-53684,a.DT)/7)*7,-53684),
WeekEnding= dateadd(dd,((datediff(dd,-53684,a.DT)/7)*7)+6,-53684)
from
( -- Test Data
select DT = convert(datetime,'2012-02-29 00:00:00.000')union all
select DT = convert(datetime,'2012-05-30 00:00:00.000')union all
select DT = convert(datetime,'2007-01-01 00:00:00.000')union all
select DT = convert(datetime,'2007-01-08 00:00:00.000')
) a
Results:
DT WeekStarting WeekEnding
----------------------- ----------------------- -----------------------
2012-02-29 00:00:00.000 2012-02-26 00:00:00.000 2012-03-03 00:00:00.000
2012-05-30 00:00:00.000 2012-05-27 00:00:00.000 2012-06-02 00:00:00.000
2007-01-01 00:00:00.000 2006-12-31 00:00:00.000 2007-01-06 00:00:00.000
2007-01-08 00:00:00.000 2007-01-07 00:00:00.000 2007-01-13 00:00:00.000
The week ranges you posted for 2007-01-01 and 2007-01-08 seem off, since you are showing weeks starting on Monday and ending on Sunday for those two dates. If those dates are what you really want, then you need to explain your actual requirements.
February 29, 2012 at 9:47 pm
Try this:
SELECT DATES
, CrsApp2.DtAd WeekStart
, DATEADD(DD, 6, CrsApp2.DtAd) WeekEnd
FROM dt
CROSS APPLY ( SELECT DATEDIFF(WK,0,DATES) ) CrsApp (DtDf)
CROSS APPLY ( SELECT DATEADD(WK ,CrsApp.DtDf ,-1 ) ) CrsApp2 (DtAd)
I see a flaw in your sample date; for '2007-01-01' , the week start at 2006-12-31. But i see that you have started 2007-01-01 at 2007-01-01 itself. Is that a specific requirement?
February 29, 2012 at 9:48 pm
Whooops.. MVJ beat me to it...
February 29, 2012 at 10:49 pm
thanks all
sorry 2007-01-01 and 2007-01-08 in last update was flaw.
-----
Even i was working with the same way with this
update dt
set WRange=Wrannn from
(select convert(varchar,datepart(m,min(dates))) + '/' +
convert(varchar,datepart(d,min(dates))) + ' - ' + convert(varchar,datepart(m,max(dates))) + '/' + convert(varchar,datepart(d,max(dates))) as 'wrannn'
from dt)t
but when i run this query it just update wrange column with same weekrange irrespective what date value is in dates column. As in dates column i have more than 10000 rows.i want that wrange would update according to the values of dates column.
March 4, 2012 at 7:14 pm
i think this problem was kind weird .but i got the solution after all
UPDATE dt
SET dt.wrange = CrsApp4.WkStrt + ' - ' + CrsApp4.WkEnd
FROM dt
CROSS APPLY (
SELECT DATEDIFF(WK,0,dt.Dates)
) CrsApp (DtDf)
CROSS APPLY (
SELECT DATEADD(WK ,CrsApp.DtDf ,-1 )
) CrsApp2 (WkStrt)
CROSS APPLY (
SELECT DATEADD(DD, 6, CrsApp2.WkStrt)
) CrsApp3 (WkEnd)
CROSS APPLY (
SELECT CONVERT(VARCHAR(5), CrsApp2.WkStrt , 101) ,
CONVERT(VARCHAR(5), CrsApp3.WkEnd , 101)
) CrsApp4( WkStrt, WkEnd)
i think it may be helpful for others.
Thanks all for your effort
March 5, 2012 at 2:08 am
I have this problem in all my apps like accounting, Billing,TimeSheets etc
I keep a table called calendar which is always in the system ( Date, WeekNo, PeriodNo) etc
then all my queries does a external joi to get the weekno or periodno
It is more like you got a pre-calculated table of dates with all your divisions (like 13 Month year etc)
Quite a few use this technique and it is called Calendar Table and there is some discussion on this forum about it
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply