關(guān)于sql中日期相關(guān)跨年處理

?

關(guān)于sql數(shù)據(jù)庫(kù)里日期的跨年處理：

讀取本周和上周紀(jì)錄時(shí)，涉及跨年數(shù)據(jù)要特殊處理：

數(shù)據(jù)庫(kù)里周數(shù)保存 1-52周。

按照sql函數(shù)取得周數(shù)時(shí)會(huì)出現(xiàn)53的問題。

寫入數(shù)據(jù)庫(kù)時(shí)要處理州周為53的情況，

week=53?? 則當(dāng)作下1年處理

nian=nian+1
week=1

?

本周：

?SET DATEFIRST 1 -- 設(shè)置周一為一周的第一天
?declare @week_year int
?set @week_year = year(getdate())
?declare @week smallint?-- 第幾周
?select @week = datepart(week,getdate())
?
?if(@week=53)
?begin
??set @week_year=@week_year+1
??set @week=1
?end

上周：

?SET DATEFIRST 1 -- 設(shè)置周一為一周的第一天

?declare @last_week_year int

?set @last_week_year = year(getdate())

?declare @last_week smallint?-- 第幾周

?select @last_week = datepart(week,getdate())
?
?if(@last_week=1)
??begin
???set @last_week_year=@last_week_year-1
???set @last_week=52
??end
?else
??begin
???set @last_week=@last_week-1
??end

上上周：

?SET DATEFIRST 1 -- 設(shè)置周一為一周的第一天

?declare @last_last_week_year int

?set @last_last_week_year = year(getdate())

?declare @last_last_week smallint?-- 第幾周

?select @last_last_week = datepart(week,getdate())
?
?if(@last_last_week=1)
??begin
???set @last_last_week_year=@last_last_week_year-1
???set @last_last_week=51
??end
?else if(@last_last_week=2)
??begin
???set @last_last_week_year=@last_last_week_year-1
???set @last_last_week=52
??end
?else
??begin
???set @last_last_week=@last_last_week-2
??end

?

?

本月：

?declare @today_year int
?declare @today_month int

?set @today_year = year(getdate())
?set @today_month = month(getdate())

?

?

上月：

?

?declare @last_month_year int
?declare @last_month int
?set @last_month_year = year(getdate())
?set @last_month = month(getdate())
?
?if (@last_month = 1)
??begin
??? set @last_month = 12
??? set @last_month_year =@last_month_year -1
??end?
?else if (@last_month != 1)
??begin
?? set @last_month = @last_month-1
??end
?