I am running SQL Server 2008 R2, and I need to create a new job that will basically run a query at the start of each month (at the 1st of each month at 01:00 AM).

Here is the query:

INSERT INTO [SupportTracker].[dbo].[DashboardRecords]
SELECT [bg_id]
      ,[bg_short_desc]
      ,[bg_reported_date]
      ,[bg_status_updated_date]
      ,[us_firstname]
      ,[us_lastname]
      ,[LastUpdatedUserFirstname]
      ,[LastUpdatedUserLastname]
      ,[st_name]
      ,[pr_name]
      ,[ct_name]
      ,[pj_name]
      ,[AssignedUserFirstname]
      ,[AssignedUserLastname]
      ,[bg_project]
      ,[no_of_hours]
      ,[BugType]
      ,[SubType]
      ,[Device]
      ,[pj_parent_id]
FROM [SupportTracker].[dbo].[ViewIssueListwBugTypeNDevice]
WHERE 
     bg_reported_date between '2012-12-01 00:00:00.000' AND '2012-12-31 23:59:59.999' 
     AND bg_id NOT IN (SELECT bg_id FROM [SupportTracker].[dbo].[DashboardRecords])
ORDER BY bg_reported_date ASC


My problem is at the WHERE clause. These two dates have to change every month.


If we are on the 01-01-2013 01:00:00:00, I need these 2 dates to be:
2012-12-01 00:00:00.000 and 2012-12-31 23:59:59.999
If we are on the 01-02-2013 01:00:00:00, I need these 2 dates to be:
2013-01-01 00:00:00.000 and 2013-01-31 23:59:59.999.


Basically I need to capture parts of the database and save it in the table, as it was at the end of each month...

Thanks

It's done with ALTER USER username CREATEDB;

See ALTER USER in the doc.

To drop a database, either you're superuser (which can be granted with ALTER USER too) or you must own the database.
