declare @year as int set @year = 2008 select cast(@year - 1 as varchar) + '-01-01' set @year = 2006 select cast(@year - 1 as varchar) + '-01-01' set @year = 2009 select cast(@year - 1 as varchar) + '-01-01'/* ------------------------------------ 2007-01-01(所影响的行数为 1 行) ------------------------------------ 2005-01-01(所影响的行数为 1 行) ------------------------------------ 2008-01-01(所影响的行数为 1 行) */
输入变量@year返回(cast(@year as int) -1 )+'-01-01'具体过程看看帮助吧
DECLARE @s NVARCHAR(1000) SET @s= '2000' SELECT DateAdd(yy,-1,Convert(datetime ,@s+'-01-01'))
create function get_d(@dt varchar(4)) returns datetime as begin declare @d datetime if isdate(@dt+'-01-01')=1 begin set @d=convert(datetime,@dt+'-01-01') end return(@d) endselect dbo.get_d('2008')----------------------- 2008-01-01 00:00:00.000(1 行受影响)
alter function get_d(@dt varchar(4)) returns datetime as begin declare @d datetime if isdate(@dt+'-01-01')=1 begin set @d=convert(datetime,convert(varchar(4),convert(int,@dt)-1)+'-01-01') end return(@d) end
汗,应该是(cast(@year as varchar) -1 )+'-01-01' 竟然又没编辑好-,-
CREATE FUNCTION my_func (@year int) RETURNS varchar(10) AS BEGIN declare @return as varchar(10) set @return = cast((@year - 1) as varchar) + '-01-01' RETURN(@return) END goselect dbo.my_func(2008) as [2008] select dbo.my_func(2006) as [2006] select dbo.my_func(2009) as [2009]drop function my_func/* 008 ---------- 2007-01-01(所影响的行数为 1 行)2006 ---------- 2005-01-01(所影响的行数为 1 行)2009 ---------- 2008-01-01(所影响的行数为 1 行) */
我快疯了-,-最后编辑一次输入变量@year int 返回cast(@year-1 as varchar)+'-01-01'
create proc testyear_proc @varYear datetime as begin declare @test varchar(20) set @test=convert(varchar(4),@varYear-1,120)+'-01-01' end 然后 declare @test timedate set @test=''--在''中输入你年份,比如2008 exec testyear_proc @test
declare @year as int set @year = 2008 select cast(@year - 1 as varchar) + '-01-01'
declare @year int set @year = 2007declare @date datetime set @date = ltrim(@year-1) select @date /* 2006-01-01 00:00:00.000 */
--定义@year为varchar类型也可 declare @year INT set @year = 2007--返回varchar select cast(@year - 1 as varchar) + '-01-01' --返回datetime select cast(str(@year-1) as datetime)
--定义@year为varchar类型也可 declare @year INT set @year = 2007 --返回varchar select cast(@year - 1 as varchar) + '-01-01' --返回datetime select cast(str(@year-1) as datetime)
set @year = 2008
select cast(@year - 1 as varchar) + '-01-01'
set @year = 2006
select cast(@year - 1 as varchar) + '-01-01'
set @year = 2009
select cast(@year - 1 as varchar) + '-01-01'/*
------------------------------------
2007-01-01(所影响的行数为 1 行)
------------------------------------
2005-01-01(所影响的行数为 1 行)
------------------------------------
2008-01-01(所影响的行数为 1 行)
*/
输入变量@year返回(cast(@year as int) -1 )+'-01-01'具体过程看看帮助吧
SET @s= '2000'
SELECT DateAdd(yy,-1,Convert(datetime ,@s+'-01-01'))
create function get_d(@dt varchar(4))
returns datetime
as
begin
declare @d datetime
if isdate(@dt+'-01-01')=1
begin
set @d=convert(datetime,@dt+'-01-01')
end
return(@d)
endselect dbo.get_d('2008')-----------------------
2008-01-01 00:00:00.000(1 行受影响)
returns datetime
as
begin
declare @d datetime
if isdate(@dt+'-01-01')=1
begin
set @d=convert(datetime,convert(varchar(4),convert(int,@dt)-1)+'-01-01')
end
return(@d)
end
AS
BEGIN
declare @return as varchar(10)
set @return = cast((@year - 1) as varchar) + '-01-01'
RETURN(@return)
END
goselect dbo.my_func(2008) as [2008]
select dbo.my_func(2006) as [2006]
select dbo.my_func(2009) as [2009]drop function my_func/*
008
----------
2007-01-01(所影响的行数为 1 行)2006
----------
2005-01-01(所影响的行数为 1 行)2009
----------
2008-01-01(所影响的行数为 1 行)
*/
返回cast(@year-1 as varchar)+'-01-01'
create proc testyear_proc @varYear datetime
as
begin
declare @test varchar(20)
set @test=convert(varchar(4),@varYear-1,120)+'-01-01'
end
然后
declare @test timedate
set @test=''--在''中输入你年份,比如2008
exec testyear_proc @test
set @year = 2008
select cast(@year - 1 as varchar) + '-01-01'
set @year = 2007declare @date datetime
set @date = ltrim(@year-1)
select @date
/*
2006-01-01 00:00:00.000
*/
declare @year INT
set @year = 2007--返回varchar
select cast(@year - 1 as varchar) + '-01-01'
--返回datetime
select cast(str(@year-1) as datetime)
declare @year INT
set @year = 2007 --返回varchar
select cast(@year - 1 as varchar) + '-01-01'
--返回datetime
select cast(str(@year-1) as datetime)