Click here to Skip to main content
Click here to Skip to main content

SQL - Time Table With Stored Procedure (SQL Server)

, 14 Jul 2004
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

Introduction

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 :

 - http://www.asp-php.net/scripts/asp.net/table_sql_temps.php

Updates

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 :

 - http://sqlpro.developpez.com/MSSQLServer_fonctions/UDF.html#FN_SEASONS

License

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

Share

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:/:www.asp-php.net
- http://www.aspfr.com
 
My technical blog is in French community :
- http://blogs.developpeur.org/fabrice69/
 
Romelard Fabrice [MVP]

Comments and Discussions

 
GeneralVery Very Urgent PinmemberShiv528-Jun-06 3:53 
GeneralRe: Very Very Urgent PinmemberRomelard Fabrice (Alias F___)28-Jun-06 13:09 
GeneralVery Urgent PinmemberShiv528-Jun-06 3:48 
GeneralRe: Very Urgent PinmemberRomelard Fabrice (Alias F___)28-Jun-06 13:08 

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

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

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