How to retreive next sunday date from present date.
HI ,
I have created report in which i have to show date in one column and in other immediate sunday date from that date.tell me how to do the same in SSAS reports.
e.g if today is 14 oct,10 ,i want to show as below
col1 col2
14oct,10 17 oct,10
where 17 oct,10 is Sunday.
for other e.g
supoose col1 date is 7 oct then data should be like this
col1 col2
6OCt,10 10 oct,10
where 10 oct,10 is sunday.
Kindly Suggest
amit
October 14th, 2010 12:43pm
Hi Amit,
I use this:
For SSRS
Where date value is Fields!Date.Value
Monday =format(dateadd("d", 2 - Weekday((Fields!Date.Value)),Fields!Date.Value),"dd MMMM yyyy")
Sunday =format(DATEADD("d",6,(dateadd("d", 2 - Weekday((Fields!Date.Value)),Fields!Date.Value))),"dd MMMM yyyy")
For SQL
DECLARE
@TestDate DATETIME
, @MyDate DATETIME
, @Monday DATETIME
, @Sunday DATETIME
SET
DATEFIRST 1
SET
@TestDate = GETDATE
()
SET
@MyDate = CONVERT
(DATETIME
,CONVERT
(INT
,DATEADD
(DAY
,+0,@TestDate))) /*This part trims off time portion of day when using GETDATE()*/
SET
@Monday = DATEADD
(DAY
, DATEDIFF
(DAY
, '18991231'
, DATEADD
(DAY
,-1,@MyDate)) / 7 * 7, '19000101'
)
SET
@Sunday = DATEADD
(DAY
, +6, @Monday)
PRINT
@TestDate
PRINT
@MyDate
PRINT
@Monday
PRINT
@Sunday
I use this to work out business weeks, hope you can do something with it.
Adriaan Sullivan - http://it-burns-when-i-sp.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2010 1:41pm
declare
@d datetime
set
@d =
getdate()
declare
@baseMonday datetime
set
@baseMonday =
'19000101'
select
@d
,
@baseMonday
+
datediff(day,@baseMonday,@d)/7*7
as Monday,
@baseMonday
+
datediff(day,@baseMonday,@d)/7*7+6
as SundayBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
October 14th, 2010 1:52pm
Use the below expression
=DateAdd("d",8-Weekday(Fields!<Columnname>.Value), Fields!<Columnname>.Value)
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2010 2:50pm
i want this in MDX query
October 14th, 2010 3:22pm
For more accurate responses to your questions try posting here
http://social.technet.microsoft.com/Forums/en/sqlanalysisservices/threadsAdriaan Sullivan - http://it-burns-when-i-sp.blogspot.com/
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2010 3:27pm