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 Twitter | Follow me on FaceBook
