SQLServer存储过程

qq_51088701 2021-04-21 03:33:42

写一个存储过程显示成这一个表从任意时间到现在的时间显示这些列怎么写啊急急急！！！

...全文

47 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

qq_51088701 2021-04-21

打赏
举报

引用 1 楼文盲老顾的回复:

日期差小于2048


declare @date date

set @date='2016-1-1'

--select DATEDIFF(d,@date,getdate())

select *,年份名称+月份名称 as 年月名称,substring(convert(varchar,日期,112),1,6) as 年月序号 
from (
	select DATEADD(d,number,@date) as 日期
	from master..spt_values 
	where type='p' and number<=DATEDIFF(d,@date,getdate())
) a
cross apply (
	select 'Y'+CONVERT(varchar,YEAR(日期)) as 年份名称
		,YEAR(日期) as 年份序号
		,'M'+CONVERT(varchar,MONTH(日期)) as 月份名称
		,MONTH(日期) as 月份序号
) o

日期差大于2048

declare @date date,@diff int,@l int

set @date='2010-1-1'

select @diff = DATEDIFF(d,@date,getdate())


select number 
into #t
from master..spt_values 
where type='p'

while (select COUNT(0) from #t)<@diff
	begin
		select @l = COUNT(0) from #t

		insert into #t
		select number+@l
		from master..spt_values 
		where type='p'
	end


select *,年份名称+月份名称 as 年月名称,substring(convert(varchar,日期,112),1,6) as 年月序号 
from (
	select DATEADD(d,number,@date) as 日期
	from #t 
	where number<=@diff
) a
cross apply (
	select 'Y'+CONVERT(varchar,YEAR(日期)) as 年份名称
		,YEAR(日期) as 年份序号
		,'M'+CONVERT(varchar,MONTH(日期)) as 月份名称
		,MONTH(日期) as 月份序号
) o
order by 1

drop table #t

谢谢大佬

文盲老顾 2021-04-21

打赏
举报

日期差小于2048


declare @date date

set @date='2016-1-1'

--select DATEDIFF(d,@date,getdate())

select *,年份名称+月份名称 as 年月名称,substring(convert(varchar,日期,112),1,6) as 年月序号 
from (
	select DATEADD(d,number,@date) as 日期
	from master..spt_values 
	where type='p' and number<=DATEDIFF(d,@date,getdate())
) a
cross apply (
	select 'Y'+CONVERT(varchar,YEAR(日期)) as 年份名称
		,YEAR(日期) as 年份序号
		,'M'+CONVERT(varchar,MONTH(日期)) as 月份名称
		,MONTH(日期) as 月份序号
) o

日期差大于2048

declare @date date,@diff int,@l int

set @date='2010-1-1'

select @diff = DATEDIFF(d,@date,getdate())


select number 
into #t
from master..spt_values 
where type='p'

while (select COUNT(0) from #t)<@diff
	begin
		select @l = COUNT(0) from #t

		insert into #t
		select number+@l
		from master..spt_values 
		where type='p'
	end


select *,年份名称+月份名称 as 年月名称,substring(convert(varchar,日期,112),1,6) as 年月序号 
from (
	select DATEADD(d,number,@date) as 日期
	from #t 
	where number<=@diff
) a
cross apply (
	select 'Y'+CONVERT(varchar,YEAR(日期)) as 年份名称
		,YEAR(日期) as 年份序号
		,'M'+CONVERT(varchar,MONTH(日期)) as 月份名称
		,MONTH(日期) as 月份序号
) o
order by 1

drop table #t