SQLServer常用sql2(二)
2015-01-24 01:45:51
·
作者:
·
浏览: 13
en 'Apr'
--when SUBSTRING('20131230',5,2)= 05 then 'May'
-- when SUBSTRING('20131230',5,2)= 06 then 'Jun'
-- when SUBSTRING('20131230',5,2)= 07 then 'Jul'
-- when SUBSTRING('20131230',5,2)= 08 then 'Aug'
--when SUBSTRING('20131230',5,2)= 09 then 'Sept'
-- when SUBSTRING('20131230',5,2)= 10 then 'Oct'
--when SUBSTRING('20131230',5,2)= 11 then 'Nov'
-- else 'Dec'
--end as MonthEN,
-----转化日期的存储过程 30-Dec-13
--create proc ChangeDate
--@d1 nvarchar(10)
--as
--select a.d+'-'+b.MonthEN+'-'+c.y
--from (select SUBSTRING(@d1,7,2) as d)a,
--( select
--case
--when SUBSTRING(@d1,5,2)= 01 then 'Jan'
-- when SUBSTRING(@d1,5,2)= 02 then 'Feb'
--when SUBSTRING(@d1,5,2)= 03 then 'Mar'
-- when SUBSTRING(@d1,5,2)= 04 then 'Apr'
--when SUBSTRING(@d1,5,2)= 05 then 'May'
--when SUBSTRING(@d1,5,2)= 06 then 'Jun'
- --when SUBSTRING(@d1,5,2)= 07 then 'Jul'
-- when SUBSTRING(@d1,5,2)= 08 then 'Aug'
--when SUBSTRING(@d1,5,2)= 09 then 'Sept'
--when SUBSTRING(@d1,5,2)= 10 then 'Oct'
--when SUBSTRING(@d1,5,2)= 11 then 'Nov'
--else 'Dec'
--end as MonthEN)b,
--(select SUBSTRING(@d1,3,2) as y) c
--exec ChangeDate @d1='20130220'
---自定义函数
CREATE FUNCTION ChangeIt(@dd nvarchar(10))
returns nvarchar(20)
AS begin
declare @result nvarchar(20)
SET @result=(
select (a.d+'-'+b.MonthEN+'-'+c.y)
from (select SUBSTRING(@dd,9,2) AS d)a,
( select
case
when SUBSTRING(@dd,6,2)= 01 then 'Jan'
when SUBSTRING(@dd,6,2)= 02 then 'Feb'
when SUBSTRING(@dd,6,2)= 03 then 'Mar'
when SUBSTRING(@dd,6,2)= 04 then 'Apr'
when SUBSTRING(@dd,6,2)= 05 then 'May'
when SUBSTRING(@dd,6,2)= 06 then 'Jun'
when SUBSTRING(@dd,6,2)= 07 then 'Jul'
when SUBSTRING(@dd,6,2)= 08 then 'Aug'
when SUBSTRING(@dd,6,2)= 09 then 'Sept'
when SUBSTRING(@dd,6,2)= 10 then 'Oct'
when SUBSTRING(@dd,6,2)= 11 then 'Nov'
else 'Dec'
end AS MonthEN)b,
(select SUBSTRING(@dd,3,2) as y) c
)
RETURN (@result)
END
--drop function ChangeIt
SELECT [dbo].ChangeIt('20131201')
SELECT dbo.ChangeIt('20131201')
-- SET LANGUAGE us_english
--SELECT DATENAME(dd, '2010-5-31')+'-'+DATENAME(m, '2010-5-31')+'-'+DATENAME(yy, '2010-5-31')
DECLARE @BeginDate DATE;
SELECT @BeginDate = '20131230';
WHILE @BeginDate<='20171231'
BEGIN
SELECT
dbo.ChangeIt(@BeginDate)
--@BeginDate
SET @BeginDate=DATEADD(DAY,1,@BeginDate);
end
update BI_Dim_Date set Dim_Week=Dim_Week-1 where WeekDayEN='Sun'
select * from BI_Dim_Date
连表更新
?
ALTER TABLE bi_dim_date ALTER COLUMN Event_name [varchar](100)
update bi_dim_date
SET Event_name= ename
from bi_dim_date,TempDayOff
WHERE DateKey=TempDayOff.Bdate
用case...when 更新
?
update BI_Dim_Date set MonthEN=(
case
when Dim_Month= 1 then 'Jan'
when Dim_Month= 2 then 'Feb'
when Dim_Month= 3 then 'Mar'
when Dim_Month= 4 then 'Apr'
when Dim_Month= 5 then 'May'
when Dim_Month= 6 then 'Jun'
when Dim_Month= 7 then 'Jul'
when Dim_Month= 8 then 'Aug'
when Dim_Month= 9 then 'Sept'
when Dim_Month= 10 then 'Oct'
when Dim_Month= 11 then 'Nov'
when Dim_Month= 12 then 'Dec'
end
update BI_Dim_Date set MonthEN=(
case
when Dim_Month= 1 then 'Jan'
when Dim_Month= 2 then 'Feb'
when Dim_Month= 3 then 'Mar'
when Dim_Month= 4 then 'Apr'
when Dim_Month= 5 then 'May'
when Dim_Month= 6 then 'Jun'
when Dim_Month= 7 then 'Jul'
when Dim_Month= 8 then 'Aug'
when Dim_Month= 9 then 'Sept'
when Dim_Month= 10 then 'Oct'
when Dim_Month= 11 then 'Nov'
else 'Dec'
end
)
update BI_Dim_Date set MonthEN=(
case Dim_Month
when 1 then 'Jan'
when 2