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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics