添加一张表
IF NOT EXISTS ( SELECT 1 FROM sysobjects WHERE name = ‘JyWeb_CDYY‘ AND xtype=‘U‘) BEGIN CREATE TABLE [dbo].[JyWeb_CDYY]( [TheYear] [int] NULL, [TheMonth] [int] NULL, [YYDate] [datetime] NULL, [Creator] [nvarchar](50) NULL, [CreateTime] [datetime] NULL, [WeekDay] [nvarchar](50) NULL, [IsWorkDay] [bit] NULL, [Guid] [nvarchar](50) NULL, [Modifier] [nvarchar](50) NULL, [ModifyTime] [datetime] NULL, [Remark] [nvarchar](50) NULL ) ON [PRIMARY]
END GO
添加存储过程
IF EXISTS ( SELECT 1
FROM sysobjects
WHERE Name = ‘GetCalendarByYear‘ AND xtype = ‘P‘ )
BEGIN
DROP PROCEDURE dbo.GetCalendarByYear
END
GO
CREATE PROCEDURE [dbo].[GetCalendarByYear]
@Creator varchar(50)
AS
BEGIN
DECLARE @WeekString varchar(12),
@dDate SMALLDATETIME,
@adddays int,
@tempDate varchar(6)
SELECT @adddays = 1 --日期增量(可以自由设定)
SELECT @tempDate = DATENAME(YY,DateAdd(yy, 1, max(YYDate))) FROM JyWeb_CDYY --获得当前最大年份并增加一年
IF @tempDate = ‘‘ OR @tempDate IS NULL
BEGIN
SELECT @tempDate = DATENAME(YY,max(GETDATE())) -- 获取当前年份
END
SELECT @dDate = cast(@tempDate+‘-01‘+‘-01‘ as SMALLDATETIME) --设置开始日期
INSERT INTO JyWeb_CDYY(TheYear, TheMonth, YYDate, WeekDay, IsWorkDay, Creator, CreateTime, Guid)
SELECT DATENAME(yy, convert(varchar(20),dateadd(dd,number,convert(varchar(5),@dDate,120)+‘01-01‘),120)) AS TheYear
, DATEPART(month, convert(varchar(20),dateadd(dd,number,convert(varchar(5),@dDate,120)+‘01-01‘),120)) AS TheMonth
, convert(varchar(20),dateadd(dd,number,convert(varchar(5),@dDate,120)+‘01-01‘),120) AS YYDate
, datename(WEEKDAY,dateadd(dd,number,convert(varchar(5),@dDate,120)+‘01-01‘)) AS WeekDay
, CASE WHEN datename(WEEKDAY,dateadd(dd,number,convert(varchar(5),@dDate,120)+‘01-01‘)) IN (‘星期六‘,‘星期日‘) THEN 0 ELSE 1 END AS IsWorkDay
, @Creator AS Creator, GETDATE() AS CreateTime, NEWID() AS Guid
FROM master..spt_values
WHERE type=‘P‘
AND dateadd(dd,number,convert(varchar(5),@dDate,120)+‘01-01‘)<=dateadd(dd,-1,convert(varchar(5),dateadd(YEAR,1,@dDate),120)+‘01-01‘)
END
GO
关于时间日期星期几的转换
select GETDATE() as ‘当前日期时间‘,
DateName(year,GetDate())+‘-‘+DateName(month,GetDate())+‘-‘+DateName(day,GetDate()) as ‘当前日期‘,
DateName(quarter,GetDate()) as ‘第几季度‘,
DateName(week,GetDate()) as ‘一年中的第几周‘,
DateName(DAYOFYEAR,GetDate()) as ‘一年中的第几天‘,
DateName(year,GetDate()) as ‘年‘,
DateName(month,GetDate()) as ‘月‘,
DateName(day,GetDate()) as ‘日‘,
DateName(hour,GetDate()) as ‘时‘,
DateName(minute,GetDate()) as ‘分‘,
DateName(second,GetDate()) as ‘秒‘,
DateName(MILLISECOND,GetDate()) as ‘豪秒‘,
DateName(WEEKDAY,GetDate()) as ‘星期几‘
select GETDATE() as ‘当前日期时间‘,
DatePart(year,GetDate())+‘-‘+DatePart(month,GetDate())+‘-‘+DatePart(day,GetDate()) as ‘当前日期‘,
DatePart(quarter,GetDate()) as ‘第几季度‘,
DatePart(week,GetDate()) as ‘一年中的第几周‘,
DatePart(DAYOFYEAR,GetDate()) as ‘一年中的第几天‘,
DatePart(year,GetDate()) as ‘年‘,
DatePart(month,GetDate()) as ‘月‘,
DatePart(day,GetDate()) as ‘日‘,
DatePart(hour,GetDate()) as ‘时‘,
DatePart(minute,GetDate()) as ‘分‘,
DatePart(second,GetDate()) as ‘秒‘,
DatePart(MILLISECOND,GetDate()) as ‘豪秒‘,
DatePart(WEEKDAY,GetDate()) as ‘星期几‘