给定某个的日期，获取下于等于该日期的月

最新推荐文章于 2024-01-04 16:01:56 发布

最新推荐文章于 2024-01-04 16:01:56 发布 · 85 阅读

本文提供了一个使用SQL语句生成指定日期前所有月份日期的示例。通过设置不同的@TIME变量值，可以获取到该时间点之前的所有月份，并以特定格式输出。此方法适用于需要按月统计或查询的历史数据场景。

DECLARE @TIME DATETIME
SET @TIME='2009-05-01'
select convert(nvarchar(6),dateadd(month,number,cast(ltrim(ltrim(year(@TIME))+'-01'+'-01') as datetime)),112) '时间'
from master..spt_values where type='P' and
dateadd(month,number,cast(ltrim(ltrim(year(@TIME))+'-01'+'-01') as datetime))<=@TIME
时间
------
200901
200902
200903
200904
200905

DECLARE @TIME DATETIME
SET @TIME='2009-01-01'
select convert(nvarchar(6),dateadd(month,number,cast(ltrim(ltrim(year(@TIME))+'-01'+'-01') as datetime)),112) '时间'
from master..spt_values where type='P' and
dateadd(month,number,cast(ltrim(ltrim(year(@TIME))+'-01'+'-01') as datetime))<=@TIME

时间
------
200901

DECLARE @TIME DATETIME
SET @TIME='2009-10-01'
select convert(nvarchar(6),dateadd(month,number,cast(ltrim(ltrim(year(@TIME))+'-01'+'-01') as datetime)),112) '时间'
from master..spt_values where type='P' and
dateadd(month,number,cast(ltrim(ltrim(year(@TIME))+'-01'+'-01') as datetime))<=@TIME

时间
------
200901
200902
200903
200904
200905
200906
200907
200908
200909
200910

(10 行受影响)