SQL日历表数据的简单生成
为了公司考勤系统的需要
编写的几个简单存储过程(可以手动运行,也可以设置事务自动运行!感觉还行比较通用,写出来共享下)
Calendar表结构很简单,2个字段:
fdDate 日期
fdType 考勤类型(工作日N,周末W,节假日H[需要根据需要自己修改])
--判断一段时间范围内的工作日（N）和周末（W）
Create PROCEDURE [dbo].[NewMonthWeekDay_Calendar]
@sdate smalldatetime,
@edate smalldatetime
AS
declare @fdDate smalldatetime
declare @WeekDay varchar(20)
declare cr0 cursor for
select fdDate from calendar where fddate>=@sdate and fddate<=@edate
open cr0
fetch next from cr0 into @fdDate
while @@fetch_status=0
begin
if (datename(weekday,@fdDate)='星期一')
update calendar set fdType='N' where fdDate=@fdDate
if (datename(weekday,@fdDate)='星期二')
update calendar set fdType='N' where fdDate=@fdDate
if (datename(weekday,@fdDate)='星期三')
update calendar set fdType='N' where fdDate=@fdDate
if (datename(weekday,@fdDate)='星期四')
update calendar set fdType='N' where fdDate=@fdDate
if (datename(weekday,@fdDate)='星期五')
update calendar set fdType='N' where fdDate=@fdDate
if (datename(weekday,@fdDate)='星期六')
update calendar set fdType='W' where fdDate=@fdDate
if (datename(weekday,@fdDate)='星期日')
update calendar set fdType='W' where fdDate=@fdDate
fetch next from cr0 into @fdDate
end
close cr0
deallocate cr0
--根据年和月自动插入Calendar表新日期数据
Create PROCEDURE [dbo].[NewMonth_Calendar]
@Year int,
@Month int
AS
Select TOP 50 ID = Identity(Int, 0, 1) Into #T from SysColumns
insert into cas..calendar(fdDate)
Select Convert(Varchar(10), DateAdd(dd, ID, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)), 120) from #T
W
相关文档：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace BuildQuery
{
/// <summary>
/// 使用提供的数据建立一个SQL查询
/// </summary>
public class BuildQuery
{
#region 类的变量
int numFieldsCount, ......
下列语句部分是Mssql语句，不可以在access中使用。
SQL分类：
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)
首先,简要介绍基础语句：
1、说明：创建数据库
CREATE DATABASE data ......
一、简单实例
1.首先来一个最简单的存储过程吧
CREATE PROCEDURE dbo.testProcedure_AX
AS
select userID from USERS order by userid desc
注:dbo.testProcedure_AX是你创建的存储过程名,可以改为:AXzhz等,别跟关键字冲突就行了,AS下面就是一条SQL语句.
2.如何在ASP.NET中调用这个存储过程?
&n ......
DATEDIFF
返回跨两个指定日期的日期和时间边界数。
一、 语法
DATEDIFF ( datepart , startdate , enddate )
二、参数
datepar ......
