SQL Server EOMONTH function in SQL Server 2012 and in DAX

EOMONTH() and BOMONTH?

Hello!

We at SQLServerGeeks.com are committed to spread the knowledge as we gain in the process of evaluating the newly released SQL Server 2012 CTP 3 release code named “Denali”.

This post comes as a supplementary addition to the earlier post which describes the RELATED FUNCTION.

Consider the syntax as stated below using which one can calculate the last day of the month

SQL syntax to calculate last day of month for SQL Server 2008R2

SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101)

SQL syntax to calculate last day of month for SQL Server 2012

SELECT CONVERT(VARCHAR(10),EOMONTH(GETDATE()),101)

DAX representation to calculate last day of month

End of Month:=EOMONTH(DATE[DATE OF PURCHASE])

The EOMONTH() functions is the new DATE TIME function introduced in SQL Server 2012(for SQL Querying), which can be used comfortable without much manipulation as highlighted in the first SQL query.

Description

The EOMONTH(param1, [param2])

Here the param2 is the optional parameter which can be used to supply the offset if any.

SELECT CONVERT(VARCHAR(10),EOMONTH(GETDATE(),3),101)

This can be used to calculate the End date for the month which is 3 months ahead from the current supplied date.

How about calculating the beginning of the month.

SQL syntax to calculate first day of month for SQL Server 2008R2

SELECT BOMONTH = CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(GETDATE())-1), GETDATE()),101)

SQL syntax to calculate first day of month for SQL Server 2012

SELECT BOMONTH = CONVERT(VARCHAR(10),EOMONTH(GETDATE(),-1)+1,101)

DAX representation to calculate first day of month

First day of Month:=EOMONTH(DATE[DATE OF PURCHASE],-1)+1

So, by slightly tweaking the existing SQL Server EOMONTH function, who needs BOMONTH()?

That’s all. Happy Learning!

 

Regards

Raunak Jhawar

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *