531
關於sql中日期相關跨年處理
關於sql數據庫裏日期的跨年處理：
讀取本周和上周紀錄時，涉及跨年數據要特殊處理：
數據庫裏周數保存 1-52周。
按照sql函數取得周數時會出現53的問題。
寫入數據庫時要處理州周為53的情況，
week=53 則當作下1年處理
nian=nian+1
week=1
本周：
SET DATEFIRST 1 -- 設置周一為一周的第一天
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 -- 設置周一為一周的第一天
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 -- 設置周一為一周的第一天
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
最後更新：2017-04-02 06:52:21