SQL server中关于年月日周的添加

添加一张表

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 星期几

 

相关文章