SQL Server: Customized Calendar Tables - Page 2

Apply custom calendar in SQL Server jobs by using the user-defined function.

To execute a procedure on all of the end of week processing dates at 11 PM:

Step 1

Create a job with the job scheduler to run daily at 11 PM, as shown below. Refer Fig 1.3

Step 2

Update the job steps as shown below. Refer Fig 1.4

Step 3

When the job runs during the days that are not End of week processing dates, you will see the following message in the job history. Refer Fig 1.5

Skipped. Not a END of week processing day

4. When the job runs during End of week processing date then you will see the following message in the job history. Refer Fig 1.6

Processed

The same function can be used for various purposes. Example:

--Is Today a Workday
if master.dbo.udf_isProcessDate(getdate(),'WorkDay')
begin
exec Myproc
end

--Is Today a End of Month processing date
if master.dbo.udf_isProcessDate(getdate(),'EOM')
begin
exec Myproc
end

--Is Today a holiday
if master.dbo.udf_isProcessDate(getdate(),'Holiday')
begin
exec Myproc
end

--Is Today a special processing date
if master.dbo.udf_isProcessDate(getdate(),'Special')
begin
exec Myproc
end

--Is Today a end of week processing date?
if master.dbo.udf_isProcessDate(getdate(),'EOW')
begin
exec Myproc
end

--Was Yesterday a Hoilday?
if master.dbo.udf_isProcessDate(getdate()-1,'Holiday')
begin
exec Myproc
end

Conclusion

This article has discussed how to create customized calendar tables and populate data. It has also explained how to apply custom calendar in SQL Server jobs by using the user-defined function udf_isProcessDate.

» See All Articles by Columnist MAK