locked
Last Day of The Previous Month RRS feed

  • Question

  • 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.

    Friday, March 19, 2010 3:22 PM

Answers

  • 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! :)
    • Proposed as answer by Challen Fu Monday, March 22, 2010 9:02 AM
    • Marked as answer by Jerry Nee Thursday, March 25, 2010 2:43 AM
    Friday, March 19, 2010 5:33 PM
  • 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,

    Lawrence


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Challen Fu Thursday, March 25, 2010 1:24 AM
    • Proposed as answer by Challen Fu Thursday, March 25, 2010 1:24 AM
    • Marked as answer by Jerry Nee Thursday, March 25, 2010 2:43 AM
    Friday, March 19, 2010 6:13 PM
    Answerer

All replies

  • 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! :)
    Friday, March 19, 2010 3:36 PM
  • 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

     

     

     

     

    • Proposed as answer by Mart76 Friday, March 19, 2010 3:53 PM
    Friday, March 19, 2010 3:53 PM
  • 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.

    Friday, March 19, 2010 3:54 PM
  • 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

     

    Friday, March 19, 2010 4:00 PM
  • 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! :)
    Friday, March 19, 2010 4:08 PM
  • 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.

    Friday, March 19, 2010 4:11 PM
  • 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.

     

     

    Friday, March 19, 2010 4:13 PM
  • 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.

     

     

    Friday, March 19, 2010 4:13 PM
  • 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 MDX

    Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
    Friday, March 19, 2010 4:14 PM
  • Jason, i am using T-SQL only.

    Platform : SQL Server 2005.

     

     

    Friday, March 19, 2010 4:17 PM
  • 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! :)
    Friday, March 19, 2010 4:34 PM
  • Thanks Jason.

    I will give you further updates.

    Have a Great Day !!!

    Friday, March 19, 2010 4:42 PM
  • 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! :)
    Friday, March 19, 2010 4:47 PM
  • 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.

    Friday, March 19, 2010 4:52 PM
  • 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.

    Friday, March 19, 2010 4:52 PM
  • 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! :)
    Friday, March 19, 2010 4:56 PM
  • 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()))
    
    
    Friday, March 19, 2010 5:04 PM
  • 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.

    Friday, March 19, 2010 5:09 PM
  • 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
    Friday, March 19, 2010 5:26 PM
  • Thanks Will.

    Let me check.

     

    i will give you further updates.

     

    Have a Great Day !!!

    Friday, March 19, 2010 5:30 PM
  • 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! :)
    • Proposed as answer by Challen Fu Monday, March 22, 2010 9:02 AM
    • Marked as answer by Jerry Nee Thursday, March 25, 2010 2:43 AM
    Friday, March 19, 2010 5:33 PM
  • 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

     

    Friday, March 19, 2010 5:40 PM
  • 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 column

    Cheers, Jason P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
    Friday, March 19, 2010 5:43 PM
  • Okay Dude.

    Thanks so much.

    i will igve updates.

     

     

    Friday, March 19, 2010 5:44 PM
  • 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,

    Lawrence


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Proposed as answer by Challen Fu Thursday, March 25, 2010 1:24 AM
    • Proposed as answer by Challen Fu Thursday, March 25, 2010 1:24 AM
    • Marked as answer by Jerry Nee Thursday, March 25, 2010 2:43 AM
    Friday, March 19, 2010 6:13 PM
    Answerer
  • 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

    Friday, January 14, 2011 1:59 AM
  • Hello Jason 

    My req is to get previous month first day(using mdx)?

    In the same  hierarchy i have month and date.

    Then i am using your suggested code which is

    ancestor([Hierarchy1].currentmember,[hierarchy1].month).firstchild

    When applying it in my cube my code is

    select { [Measures].[Internet Sales Amount] } on columns,
    ancestor ( [Date].[Calender Yr to Date].currentmember,
    [Date].[Calender Yr to Date].[Month].&[200907]).firstchild on rows
    from [SSAS2008SBS];

    i am getting the empty value .The result is like in the following way

    Internet sales amount

    Can you please guide me where i am doing the mistake?

    Monday, April 15, 2013 7:30 AM