Click here to Skip to main content
13,357,973 members (55,486 online)
Click here to Skip to main content
Add your own
alternative version


15 bookmarked
Posted 27 Apr 2004

SQL - Time Table With Stored Procedure (SQL Server)

Rate this:
Please Sign up or sign in to vote.
A Stored Procedure for Create a Time Table with all interesting details

Sample Image - TableTemps.gif


In many project, we can use a time table (for example in HR domain).

This SQL Script is used to create the Time Table and the Stored Procedure that completes automatically a range between 2 given dates.

This Script is based on SQL Server and based on french holiday, for the other countries, the public holidays part must be modified.

Time Table

The details of the Table created by the Script :

  • TEMPS_ID (Type : smallINt, Propriété : NOT NULL) : ID of the Date
  • TEMPS_DATE (Type : smalldatetime, Propriété : NOT NULL) : Date
  • TEMPS_ANNEE (Type : smallINt, Propriété : NOT NULL) : Year of The Date
  • TEMPS_MOIS (Type : tINyINt, Propriété : NOT NULL) : Month of the Date
  • TEMPS_JOUR (Type : tINyINt, Propriété : NOT NULL) : Day of The Date
  • TEMPS_JOURDESEMAINE (Type : tINyINt, Propriété : NOT NULL) : WeekDay N° of the Date
  • TEMPS_JOURDELANNEE (Type : smallINt, Propriété : NOT NULL) : N° of the Day in the year of the Date
  • TEMPS_JOUROUVRE (Type : char(1), Propriété : NOT NULL) : Flag to know if the day is open or not
  • TEMPS_TRIMESTRE (Type : tINyINt , Propriété : NOT NULL) : N° of the quarter of the Date
  • TEMPS_SAISON (Type : char(10), Propriété : NOT NULL) : Season of the Date
  • TEMPS_FERIE (Type : varchar(20), Propriété : NULL) : If the date is public holiday, which one ?

The Stored Procedure

The Stored Procedure is used to add data into the Time Table, in fact with this SP we can clear all data of the existing Time Table and just execute this SP.

The SP is in the SQL source code.

Related Links

The french version (complete version) of this article is visible at this link :



I modify the SQL script for add functions (from Frédéric BROUARD) to calculate exactly the seasons, You can see these function at the adress :



This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Romelard Fabrice (Alias F___)
Web Developer
Switzerland Switzerland
I'm French Web developer and MVP.
My favorites languages are ASP.NET and SharePoint. I made a lot of projects in open source and many articles.
You can see all my articles on french developer sites like :
- http:/

My technical blog is in French community :

Romelard Fabrice [MVP]

You may also be interested in...

Comments and Discussions

GeneralVery Very Urgent Pin
Shiv528-Jun-06 3:53
memberShiv528-Jun-06 3:53 
GeneralRe: Very Very Urgent Pin
Romelard Fabrice (Alias F___)28-Jun-06 13:09
memberRomelard Fabrice (Alias F___)28-Jun-06 13:09 
GeneralVery Urgent Pin
Shiv528-Jun-06 3:48
memberShiv528-Jun-06 3:48 
GeneralRe: Very Urgent Pin
Romelard Fabrice (Alias F___)28-Jun-06 13:08
memberRomelard Fabrice (Alias F___)28-Jun-06 13:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180111.1 | Last Updated 15 Jul 2004
Article Copyright 2004 by Romelard Fabrice (Alias F___)
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid