Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted May 4, 2005

SQL Server: Customized Calendar Tables - Page 2

By Muthusamy Anantha Kumar aka The MAK

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


Fig 1.5

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


Fig 1.6

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

Note: Feel free to modify the table Corporate_Calendar and the function udf_isProcessDate according to your requirements. Additional columns in the table and additional parameters in the function are sometimes required.

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM