Last Day of each month function?

  • I have a data feed that is providing dates like so:

    200802 --translates to (02/01/2008)

    202912

    200508

    200508

    200805

    200502

    200508

    200508

    200503

    200607

    I have an app that originally needed to perioodically read the date and interpret each as the first day of each month (02/01/2008).  Accordingly, the following function was created:

    _______________________________________________________

    Create Function dbo.fnConvertExpirationDate(@ExpirationDate int)

    Returns datetime

    as

      begin

     declare @NewExpirationDate datetime

     select @NewExpirationDate = convert(datetime, substring(convert(char(6), @ExpirationDate), 5, 2)

                                                 + '/01/'

                                                 + substring(convert(char(6), @ExpirationDate), 1, 4))

     return @NewExpirationDate

      end

    ________________________________________________________

    Easy, easy stuff...  Now I need each date to automatically be interpreted as the last day of each month (i.e. 02/28/2008 or 29th in leap year).  Is there an easy way to do this??

    Thanks in advance.

    Ryan

  • To get last day of month, I usually add 1 month and subtract 1 day.

    So take the existing algorithm (which gives you 1st of month) and ...

    DATEADD( d, -1,  DATEADD(M, 1, YourDate ) )

  • Just as a heads-up, you will probably be better off just appending '01' to the existing data string you have, rather than using the mm/dd/yyyy format. yyyymmdd is an international standard, and will be understood no matter where your function ends up, whereas mm/dd/yyyy is mostly only found in the USA.

    PW has the idea I was going to present. So, given your data, the Function would be:

    _______________________________________________________

    Create Function dbo.fnConvertExpirationLDOM(@ExpirationDate int)

    Returns datetime

    as

    begin

    declare @NewExpirationDate datetime

    set @NewExpirationDate = Dateadd(Day, -1, DateAdd(Month, 1, convert(datetime, convert(varchar, @ExpirationDate) + '01')))

    return @NewExpirationDate

    end

    _______________

  • FWIW, here's a version without using a UDF. It's based on a suggestion provided by SQL Server MVP Steve Kass.

    create table #dummy

    (

     c1 int

    )

    insert into #dummy values(202912)

    insert into #dummy values(200508)

    insert into #dummy values(200805)

    insert into #dummy values(200502)

    insert into #dummy values(200402) --leap year proof

    declare @31st datetime

    set @31st = '19341031' -- any 31st, no matter if past, present or future

    select

     dateadd(month,datediff(month,@31st,cast(c1 as char(6))+'01'),@31st)

    from #dummy

    drop table #dummy

                                                          

    ------------------------------------------------------

    2029-12-31 00:00:00.000

    2005-08-31 00:00:00.000

    2008-05-31 00:00:00.000

    2005-02-28 00:00:00.000

    2004-02-29 00:00:00.000

    (5 row(s) affected)

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Thanks everyone for the solutions. 

    Ryan

  • Ryan.  Examine this statement:

    SELECT CONVERT(DATETIME, '@ExpirationDate + '01', 112).  s

    This statement is very simple, but it may be difficult to understandd by programmers of other languages.

  • Äh, may I say that the requirement was to construct the *last* day of a month? I can't see your statement doing this. Let alone the fact that it won't run out of the box anyway

    DECLARE @ExpirationDate CHAR(6)

    SET @ExpirationDate = '200402'

    SELECT CONVERT(DATETIME, '@ExpirationDate + '01', 112).  s

    Server: Msg 170, Level 15, State 1, Line 3

    Line 3: Incorrect syntax near '01'.

    Server: Msg 105, Level 15, State 1, Line 3

    Unclosed quotation mark before the character string ', 112).  s

    '.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • End of month logic for "YourDate":

    DATEADD(d, -1, DATEADD(m, 1 + DATEDIFF(m, 0, @YourDate), 0))

     

     

    Also, first of month logic:

    DATEADD(m, DATEDIFF(m, 0, @YourDate), 0)

     

     

     

  • What a great function set - and it truncates off any minutes as well!

    Two thumbs up!

  • Okay, another variation:

    SELECT

     DATEADD(MONTH,DATEDIFF(MONTH,30,GETDATE()),0) FirstDay

     , DATEADD(MONTH,DATEDIFF(MONTH,30,GETDATE()),30) LastDay

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply