Hi friends, suppose my current Date is 01/11/2008. so in this case how to get previous month Last day and Previous month first Day ? Pls provide me suggestions. Thanks.

Suppose you have a hierarchy Hierarchy1 which includes Month and Date in it. Then you can use the following function ancestor([Hierarchy1].currentmember,[hierarchy1].month).firstchild -- (Prev Month First Day) and ancestor([Hierarchy1].currentmember,[hierarchy1].month).lastchild -- (Prev Month last day)Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Hi First day of the previous month for the date entered: SELECT (dateadd(d,-(datepart(d,'2008-11-01')-1), dateadd(mm,-1,'2008-11-01'))) For the last day of the previous month for the date entered: SELECT DATEADD(dd, -DAY(DATEADD(m,-1,'2008-11-01')), '2008-11-01') Always fun working with dates, enjoy Martyn

Hi Jason, Thanks for the reply. Actually, i am using Getdate() in my Reports. I do not have any hierarchy in my database and report. Suppose Getdate() has output - 01/11/2008 then how to get previous month last day ? Let me know. Thanks.

Hi, The following will give you last day of the previous month (GETDATE()-DATEPART(DAY,GETDATE())) And (GETDATE()-DATEPART(DAY,GETDATE()))-(DatePart(Day,GETDATE()-DATEPART(DAY,GETDATE()))-1) will give the first day of previous month Thanks, Shailesh

Hmmm, I am not exactly clear of the requirements, but let me play a blind one here ;) Assumptions 1) MDX is used and not sql 2) Suppose date returned is of the format 01/11/2008, the corresponding key would be [Time].[Date].[01112008] Solution 1) Make a dataset which will return the month of the selected day (or getdate() here) with member [measures].[MName] as [Month].currentmember.member_caption select [measures].[MName] on 0, [Month].allmembers on 1 from [cube] where strtoset(day(getdate())+month(getdate())+year(getdate())) 2) Make a hidden report parameter which stores the result of this value, let it be called RP_Month 3) Now in your main dataset, you can use strtomember(@RP_Month).prevmember.firstchild and strtomember(@RP_Month).prevmember.lastchild to access the first and last day of prev month :) P.S. : Would need to write a condition to check if month or date is less than 10, and if yes, have to prefix zero also, when the strtoset is being constructed. Lemme know if you need more clarifications.Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Jason my prevous post Date formate is MM/DD/YYY. So for Jan 11 2008 , how to get previous month Last day means i need to get Dec 31 2007 in out put. Let me know. Thanks.

Shailesh, I did not understand follwing thigs. The following will give you last day of the previous month (GETDATE()-DATEPART(DAY,GETDATE())) Pls let me know.

1) Could you please tell me whether you are using MDX or sql? 2) What does your unique name for the date attribute look like? Eg:-[Time].[Date].[10102010] for 10/10/2010? I need this info so I can construct the set for you in MDXCheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Jason, i am using T-SQL only. Platform : SQL Server 2005.

Oh, sorry for misleading everyone by using MDX, I am sort of addicted to the benefits of using cubes and MDX that I go into them on default mode :), Sorry. Now coming to the answer, first day of prev month : DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)) last day of prev month : DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1 When dealing with dates it is always best practice to use >= and <. Using >= and < will ensure that no rows get eliminated because of the time portion of code. Here is an example of how to do this: SELECT MyCols FROM MyTable WHERE MyDateCol >= DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)) AND MyDateCol < DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Thanks Jason. I will give you further updates. Have a Great Day !!!

Sure. And hey, i forgot to give credit to the following thread http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1d5772be-be53-4707-a544-8382547e928a/ Read it for more insight into the solution :)Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Jason, in my Case Current date 01/11/2008 - MM/DD/yyyy and to get previous months last day - 12/31/2007. Do i need to subtract year part also ? Because i need to compare 12/31/2007 with Database Column in Where Clause .. letme know. Thanks.

Nopes, the expression will take care of that. Even if your current date is 11 January 2008, first date of prev month would be 1 Dec 2007 with the expression I gave you. Please check and confirm :)Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

How about this: -- First day of the current month select DATEADD(d, -1 * datepart(d, getdate()) + 1, getdate()) -- Last date of the previous month select DATEADD(d, -1 * datepart(d, getdate()), getdate()) -- last date of the current month select dateadd(m, 1, DATEADD(d, -1 * datepart(d, getdate()), GETDATE()))

Jason, Let me describe my scenario. i have one SSRS reports. Which runs on date of 1st and 15th of each month. If current date is 1st in a month then it should display data in reports of previous months date 15th to last day of month. if current date is 15th in a onth then it should display data in repots of date between 1st to 15th of current month. current date : 15th - > display data current month date 1st to 15th current date : 1st -> display data of previous month 15th to last day of month. if need further clarification on this req. let me know. Thanks.

So, something like: 1) Current date: 15 -> display data current month date 1st to 15th as Declare @Startdate datetime Set @Startdate = (select DATEADD(d, -1 * datepart(d, getdate()) + 1, getdate())) Declare @EndDate datetime Set @EndDate = ( select ltrim(str(datepart(year, getdate()))) + '-' + ltrim(str(datepart(month, getdate()))) + '-15' ) and then similar for the second criteria?will

Thanks Will. Let me check. i will give you further updates. Have a Great Day !!!

Hmm, so the requirement isnt exactly to get the first and last days of prev month. For the above requirement, you have to use the code below SELECT MyCols FROM MyTable WHERE MyDateCol >= ( -- if day is 1st of the month, use 15th of prev month else use 1st of prev month case DAy(MyDateCol) when '1' then DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,MyDateCol),14)) else DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,MyDateCol),0)) end) AND MyDateCol < = ( -- if day is 1st of the month, use last day of prev month else use 15th of prev month case DAy(MyDateCol) when '1' then DATEADD(MONTH,DATEDIFF(MONTH,0,MyDateCol),0)-1 else DATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,MyDateCol),14)) end) My apologies for not formatting the code correctly, I am also still learning how to use the html thingies. A word of caution, this code will only work properly for the requirement that you have given (which means on the 1st and 15th of the month) Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Jaso Thanks you so much. So for the above functionality - to check weather current date is 1st or 15th do i need to implement whole functionality in Function ? below is my sameple thoughts and code. select * from table where {date} between dbo.firstdatefunction (Getdate()) and dbo.likedatefunction (Getdate()). let me know , am i going in right direction or not ? Thanks

I already wrote the entire query for you, you dont need to use any additional functions. Just substitute MyDateCol in my query with your Date columnCheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)

Okay Dude. Thanks so much. i will igve updates.

Hi, Are you trying to do this using Report Expression? If so, you can try these... Suppose your date field is called Date, the following expression will give you the last day of previous month, using the AddDays(int) methond on CDate: =CDate(year(Fields!Date.Value) & "/" & month(Fields!Date.Value) & "/01").AddDays(-1) You can apply formatting on it: =Format(CDate(year(Fields!Date.Value) & "/" & month(Fields!Date.Value) & "/01").AddDays(-1), "yyyyMMdd") For first day of previous month, use the AddMonths(int) method on CDate: =CDate(year(Fields!Date.Value) & "/" & month(Fields!Date.Value) & "/01").AddMonths(-1) Formatted: =Format(CDate(year(Fields!Date.Value) & "/" & month(Fields!Date.Value) & "/01").AddMonths(-1), "yyyyMMdd") Hope this helps! Cheers, LawrenceThis posting is provided "AS IS" with no warranties, and confers no rights.

T-SQL How-To Get Last Day of a Month http://hubpages.com/hub/T-SQL-How-To-Get-Last-Day-of-a-Month T-SQL How-To Get First Day of the Month http://hubpages.com/hub/T-SQL-How-To-Get-First-Day-of-the-Month T-SQL How To Get Date of Last Day of Week http://hubpages.com/hub/T-SQL-How-To-Get-Date-of-Last-Day-of-Week