create table ym (a int default(year(getdate())), b int default(month(getdate())), id int )
insert into ym(id) values(1),(2),(3)
--由于直接拼接字符串优先向数值转化,而'-'不是数字所以转化失败 select CAST(a as varchar(4))+'-'+CAST(b AS VARCHAR(2)) from ym
以上insert 语句 08及以上有效
兄弟们,不是在SQL语句里,是放在某个字段的默认值
最后三个 select 是结果,是你想要的吗 declare @year int,@month intset @year=YEAR(GETDATE()) set @month=month(GETDATE())select cast(@year as varchar(4))+'-'+cast(@month as varchar(2)) select convert(char(6),DATEADD(D,-1,DATEADD(M,@month,cast(@year as CHAR(4))+'0101')),112) select convert(char(7),DATEADD(D,-1,DATEADD(M,@month,cast(@year as CHAR(4))+'0101')),120)
稍微改一下就可以了: create table a ( id int, b varchar(10) default(cast(year(getdate()) as varchar(10))+'-'+cast(month(getdate()) as varchar(4))) )
insert into a(id) values(1),(2),(3)
--由于直接拼接字符串优先向数值转化,而'-'不是数字所以转化失败 select * from a
--创建表的时候添加 CREATE TABLE A( B VARCHAR(6) DEFAULT CONVERT(VARCHAR(6),GETDATE(),112) ) --已有表的时候 新增 ALTER TABLE A ADD CONSTRAINT DF_A_B DEFAULT(CONVERT(VARCHAR(6),GETDATE(),112)) FOR B
楼上可用 CREATE TABLE #A( B VARCHAR(6) DEFAULT CONVERT(VARCHAR(6),GETDATE(),112) ) GO INSERT #A VALUES(DEFAULT) GO SELECT * FROM #A GO
create table ym
(a int default(year(getdate())),
b int default(month(getdate())),
id int
)
insert into ym(id)
values(1),(2),(3)
--由于直接拼接字符串优先向数值转化,而'-'不是数字所以转化失败
select CAST(a as varchar(4))+'-'+CAST(b AS VARCHAR(2)) from ym
declare @year int,@month intset @year=YEAR(GETDATE())
set @month=month(GETDATE())select cast(@year as varchar(4))+'-'+cast(@month as varchar(2))
select convert(char(6),DATEADD(D,-1,DATEADD(M,@month,cast(@year as CHAR(4))+'0101')),112)
select convert(char(7),DATEADD(D,-1,DATEADD(M,@month,cast(@year as CHAR(4))+'0101')),120)
( id int,
b varchar(10) default(cast(year(getdate()) as varchar(10))+'-'+cast(month(getdate()) as varchar(4)))
)
insert into a(id)
values(1),(2),(3)
--由于直接拼接字符串优先向数值转化,而'-'不是数字所以转化失败
select * from a
CREATE TABLE A(
B VARCHAR(6) DEFAULT CONVERT(VARCHAR(6),GETDATE(),112)
)
--已有表的时候 新增
ALTER TABLE A ADD CONSTRAINT DF_A_B DEFAULT(CONVERT(VARCHAR(6),GETDATE(),112)) FOR B
CREATE TABLE #A(
B VARCHAR(6) DEFAULT CONVERT(VARCHAR(6),GETDATE(),112)
)
GO
INSERT #A VALUES(DEFAULT)
GO
SELECT * FROM #A
GO