T-SQL: EOMONTH

Question

T-SQL: EOMONTH

Nagaraju K

SSC-Addicted

Points: 439
More actions
September 18, 2016 at 5:59 am

#321485

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

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

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

BrainDonor SSCoach Points: 19262 More actions · Answer 1

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: 259165 More actions · Answer 2

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 SSChampion Points: 10077 More actions · Answer 3

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 More actions · Answer 4

akljfhnlaflkj

SSC Guru

Points: 76202

September 19, 2016 at 7:05 am

#1901612

Wish this was available in 2008.

daniel.plocinik Hall of Fame Points: 3288 More actions · Answer 5

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 6

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: 12388 More actions · Answer 7

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 SSC-Dedicated Points: 31991 More actions · Answer 8

Thanks - interesting question.

- webrunner

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

Revenant SSC-Forever Points: 42467 More actions · Answer 9

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: 19262 More actions · Answer 10

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 More actions · Answer 11

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: 19262 More actions · Answer 12

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 More actions · Answer 13

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]

Gareth Davison Right there with Babe Points: 723 More actions · Answer 14

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.

This is true for the DATE, DATETIME2, and DATETIMEOFFSET types, but DATETIME, and SMALLDATETIME will interpret XXXX-XX-XX as YYYY-DD-MM if your date format is DMY. The following demonstrates this quite nicely, despite using the exact same literals, the two columns return two different values:

SET DATEFORMAT DMY;

DECLARE @DateTime DATETIME = '2012-02-01',

@Date DATE = '2012-02-01';

SELECT[Datetime] = EOMONTH( @Date, 2 ),

[Date] = EOMONTH( @DateTime, 2 );

For a dateformat of MDY, then both datatypes produce the same result:

SET DATEFORMAT MDY;

DECLARE @DateTime DATETIME = '2012-02-01',

@Date DATE = '2012-02-01';

SELECT[Datetime] = EOMONTH( @Date, 2 ),

[Date] = EOMONTH( @DateTime, 2 );

For DATETIME and SMALLDATETIME the only culture invariant format is YYYYMMDD

T-SQL: EOMONTH

Cookies on SQLServerCentral