Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    8

    Question Unanswered: Using DATEADD Function

    Hi,
    I'm getting problem while using DATEADD Function.

    When I use DATEADD(MONTH, 1 '01/31/2005'), it returns 02/28/2005 and when I use DATEADD(MONTH, 1 '02/28/2005'), it returns 03/28/2005 but I want the result as 03/31/2005 ie last day of the month.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    What happens if you'd add a month and subtract one day?

  3. #3
    Join Date
    Jan 2005
    Posts
    8

    Using DATEADD Function

    How do I know if the date that supplied is the last day of the month ?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is an age-old problem for calendars where some months have different numbers of days than other months. I don't know of any truly satisfying answer to this problem, but I'd like to hear if anyone ever finds one!

    The problem lies in the transition from a long month (such as January) to a short month (such as February). When you add a month to any day after January 28 (or 29 on a leap year), just where does that leave you? The commonly accepted answer is the last day of the next month.

    The transition from a short month (like February) to a long month (like March) isn't such a problem. A month after February 28th is always going to be March 28th, at least for most people's use.

    At least this calendar has a predictable number of days per month. There are some calendars where you can't truly know when next month will start (there are calendars that depend on a human observer to declare the first of every month, so it can move by one day on any given month)!

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by prajesh
    How do I know if the date that supplied is the last day of the month ?
    Code:
    DECLARE @myDate DATETIME
    SELECT @myDate = GetDate()
    
    IF Month(@myDate) = Month(DateAdd(day, 1, @myDate))
       THEN PRINT "mid-month"
       ELSE PRINT "Today is a month-end"
    -PatP

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So you are treating the last day of the month as a "special case". IE: if you were adding a month to February 27, would you want the result to be March 27 or March 30 (the second-to-last day of March). If so, then the problem lies with the fact that YOU are not being consistent in your handling of dates, and is not an issue with the DATEADD function. You will need to write a custom function if you want non-standard behavior.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2005
    Posts
    8
    Thanks for suggestion Pat. I'll try that on my stored procedure.

  8. #8
    Join Date
    Jan 2005
    Posts
    8
    Hi PatP,
    The following is the code I tried
    DECLARE
    @CNT INT,
    @PERIODM INTEGER,
    @PDATE DATETIME

    CREATE TABLE #TEMP(PDATE DATETIME)
    SELECT @CNT = 1
    SELECT @PERIODM = 60
    SELECT @PDATE = '01/31/2005' -- if this value is '01/31/2005' then in second row it will be '02/28/2005' and in third row I want '03/31/2005'
    WHILE @CNT <= @PERIODM
    BEGIN
    INSERT INTO #TEMP
    VALUES(@PDATE)

    SELECT @CNT = @CNT + 1
    IF MONTH(@PDATE) = MONTH(DATEADD(DAY, 1, @PDATE))
    SELECT @PDATE = DATEADD(MONTH, 1, @PDATE)
    ELSE
    SELECT @PDATE = ??????????????
    -- DATEADD(MONTH, 1, DATEADD(DAY, -1, @PDATE))
    END
    SELECT * FROM #TEMP ORDER BY 1

    DROP TABLE #TEMP


    How do I do, please suggest..

    Thanks in advance.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the first row contains 01/30/2005, what should the next five rows be? There are some really complex problems to deal with here, in that there isn't any 02/30/2005, but there is a 03/30/2005 and a 04/30/2005 which happens to be the last day of that month!

    This is a really, really slippery slope... Think carefully about what you want, then if you can give me an example that goes from January 30 through May, I'll see if I know enough to write some code to help you get it.

    -PatP

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Is the original problem to get the last day of the next month?

    I'd decompose the problem to determine the first day of two month later, and subtract 1 day, so

    12/31/2004 --> 02/01/2005 --> 01/31/2005

    If this is, want you want to achieve, the code can be done easily.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Jan 2005
    Posts
    2

    try this..

    Declare @date nvarchar(20)
    set @date = '2/5/2004'

    -- -- First day of last month
    select convert(nvarchar(20),Dateadd(d,-datepart(d,dateadd(m,-1,@date)),dateadd(m,-1,@date))+1,101)
    --
    -- -- Last Day of last Month
    select convert(nvarchar(10),dateadd(d,-datepart(d,@date),@date),101)


    Hope this helps...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •