March 2013 – Page 2 – SQLXpertise

How to find First or Last Day of a Month ? SQL Server 2008 vs SQL Server 2012 compared

SQL Server 2012 has a new Function called EOMONTH() which returns the last day of the month for the specified date in parameter and also has an option to define offset as well.

Let us first see How we can do this in SQL Server 2008

--Last Day of Last Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) AS [Last Day of Last Month]
--Last Day of This Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0)) AS [Last Day of This Month]
--Last Day of Next Month
SELECT    DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 2, 0)) AS [Last Day of Next Month]

Let us see How SQL Server 2012 simplifies the above code

--Last Day of Last Month
SELECT    EOMONTH(GETDATE(), -1) AS [Last Day of Last Month]
--Last Day of Current Month
SELECT    EOMONTH(GETDATE()) AS [Last Day of This Month]
--Last Day of Next Month
SELECT    EOMONTH(GETDATE(), 1) AS [Last Day of Next Month]

In SQL Server 2008 we had to make 4 function calls in order get the output, whereas SQL Server 2012 helps us do the same thing in 2 function calls

We can use the same function to get First Day of the Month as well

--First Day of Last Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)) AS [First Day of Last Month]
--First Day of This Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AS [First Day of This Month]
--First Day of Next Month
SELECT DATEADD(DAY, 1, EOMONTH(GETDATE())) AS [First Day of Next Month]

Hope this helps !!!

How to backup your SQL Database and Upload to FTP or Cloud Automatically ?

Its always a better idea to Keep Offsite backup of databases, “SQL Backup and FTP” allows you to do that freely

SQL Backup and FTP is available for download for free at http://sqlbackupandftp.com

It supports direct backup to

1. FTP

2. Local or Network Folder

3. Drop Box

4. Box

5. Amazon S3

6. SkyDrive

7. Google Drive

Professional Version also supports SFTP and FTPS and AES Encryption of Backup files

Please follow the below steps for installation

The installation has completed successfully, Let us launch SQL Backup and FTP and create a Backup

Select the databases you want to Backup

Click on “Add backup destination” to specify the backup destination

For our demo, Let us select Drop box option

Click on “Authorize…” and Login to your Drop Box account

Click “Allow” to approve the Drop Box access

Once Drop Box access is authorized, Please click on “Finish” to proceed. You can configure automatic cleanup of backups as well.

Specify the Folder to which Backup has to be done

Click “Test” to make sure it works

Once Test is completed, Click “Ok” to proceed

Let us now connect to SQL Server and do a backup, By default, if you have a SQL Server default instance running, it will automatically connect and display the databases, Please select the database which you want to backup