T-SQL: EOMONTH

  • Nagaraju K

    SSC-Addicted

    Points: 437

    Comments posted to this topic are about the item T-SQL: EOMONTH

  • This was removed by the editor as SPAM

  • BrainDonor

    SSCoach

    Points: 19202

    I couldn't see any of the answers that matched what I believed I should be getting - then I played around a little.

    Where dates are concerned it is probably helpful to specify the date format in use, as this site has people from all over - and they might not be using the same date format as yourself.

    That aside, a nice question.

    Steve Hall
    Linkedin
    Blog Site

  • Koen Verbeeck

    SSC Guru

    Points: 258942

    Great question. Never even realized EOMONTH had an optional second parameter. Good to know.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • George Vobr

    SSCrazy Eights

    Points: 8981

    Practical for use is that the second parameter may have also a negative value,

    thanks for this seemingly simple question Nagaraj. 🙂

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Wish this was available in 2008.

  • daniel.plocinik

    Hall of Fame

    Points: 3288

    :hehe: Cool - a new tool to get rid of all of my 2005 code...

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    BrainDonor (9/19/2016)


    I couldn't see any of the answers that matched what I believed I should be getting - then I played around a little.

    Where dates are concerned it is probably helpful to specify the date format in use, as this site has people from all over - and they might not be using the same date format as yourself.

    That aside, a nice question.

    With SQL Server, dates formatted as YYYY-XX-YY will always be YYYY-MM-DD,

    YYYY-DD-MM is not a valid format with SQL Server.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • sknox

    SSChampion

    Points: 12227

    George Vobr (9/19/2016)


    Practical for use is that the second parameter may have also a negative value,

    thanks for this seemingly simple question Nagaraj. 🙂

    Was going to post this. For example, one way to compensate for the still-absent BOMONTH():

    SELECT DATEADD (DAY,1,EOMONTH (GETDATE,-1))

  • webrunner

    One Orange Chip

    Points: 29922

    Thanks - interesting question.

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Revenant

    SSC-Forever

    Points: 42467

    Koen Verbeeck (9/19/2016)


    Great question. Never even realized EOMONTH had an optional second parameter. Good to know.

    +1

    Thanks, Nagaraju!

  • BrainDonor

    SSCoach

    Points: 19202

    Alvin Ramard (9/19/2016)


    BrainDonor (9/19/2016)


    I couldn't see any of the answers that matched what I believed I should be getting - then I played around a little.

    Where dates are concerned it is probably helpful to specify the date format in use, as this site has people from all over - and they might not be using the same date format as yourself.

    That aside, a nice question.

    With SQL Server, dates formatted as YYYY-XX-YY will always be YYYY-MM-DD,

    YYYY-DD-MM is not a valid format with SQL Server.

    Date formatting isn't a strong point with me, so could you possibly explain this:

    I only get a match within the answers listed if I swap the day and month.

    I'm completely prepared to believe it is something stupid at this end.

    Steve Hall
    Linkedin
    Blog Site

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    BrainDonor (9/19/2016)


    Alvin Ramard (9/19/2016)


    BrainDonor (9/19/2016)


    I couldn't see any of the answers that matched what I believed I should be getting - then I played around a little.

    Where dates are concerned it is probably helpful to specify the date format in use, as this site has people from all over - and they might not be using the same date format as yourself.

    That aside, a nice question.

    With SQL Server, dates formatted as YYYY-XX-YY will always be YYYY-MM-DD,

    YYYY-DD-MM is not a valid format with SQL Server.

    Date formatting isn't a strong point with me, so could you possibly explain this:

    I only get a match within the answers listed if I swap the day and month.

    I'm completely prepared to believe it is something stupid at this end.

    On my local SQL 2014 machine,

    DECLARE @Date Datetime = '2012-02-01' -- Feb 1, 2012

    SELECT EOMONTH(@Date, 2)

    GO returns: 2012-04-30, i.e. End of month for 2 months after Feb 1, 2012

    DECLARE @Date Datetime = '2012-01-02' -- Jan 2, 2012

    SELECT EOMONTH(@Date, 2)

    GO returns: 2012-03-31, i.e. End of month for 2 months after Jan 2, 2012

    This is what I expected. It looks like your result #2 should go with query #1 and result #1 with query #2.

    If you don't believe YYYY-XX-YY cannot be YYYY-DD-MM, try same queries for dates: 2012-01-13 and 2012-13-01. You'll see that the 2nd date is not a valid datetime.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • BrainDonor

    SSCoach

    Points: 19202

    I've played a little more (this is a nice diversion from trying to understand reduction and distribution in QlikView) and it appears to depend upon the use of the hyphens.

    The following screen-shot is a little cramped, so I could fit it all in:

    1 and 6 are invalid on my instance. 6 I certainly expect but 1 still has me puzzled. Fortunately I always use either YYYYMMDD or 'DD MMM YYYY', so my brief bout of panic over so much of my past work has abated.

    But I would still like to know why 1 and 2 behave the way they do.

    Steve Hall
    Linkedin
    Blog Site

  • Alvin Ramard

    SSC-Forever

    Points: 41190

    BrainDonor (9/19/2016)


    I've played a little more (this is a nice diversion from trying to understand reduction and distribution in QlikView) and it appears to depend upon the use of the hyphens.

    The following screen-shot is a little cramped, so I could fit it all in:

    1 and 6 are invalid on my instance. 6 I certainly expect but 1 still has me puzzled. Fortunately I always use either YYYYMMDD or 'DD MMM YYYY', so my brief bout of panic over so much of my past work has abated.

    But I would still like to know why 1 and 2 behave the way they do.

    QlikView? Maybe it's causing all the problems?



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 15 posts - 1 through 15 (of 19 total)

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