34,576
社区成员
发帖
与我相关
我的任务
分享
DECLARE @min varchar(6),@max varchar(6);
SELECT @min = '200804',@max = '200910';
SELECT CONVERT(varchar(6),DATEADD(month,number,@min+'01'),112) AS [Date]
FROM master.dbo.spt_values
WHERE type = 'p'
AND DATEADD(month,number,@min+'01') <= @max+'01';
2.
select 当前日期=convert(varchar(10),getdate(),120)
,当前时间=convert(varchar(8),getdate(),114)
select datename(dw,'2004-10-15')
select 本年第多少周=datename(week,convert(varchar(10),getdate(),120))
,今天是周几=datename(weekday,convert(varchar(10),getdate(),120))
3.返回指定日期之间的所有月份的第一天和最后一天
DECLARE @min varchar(6),@max varchar(6);
SELECT @min = '200804',@max = '200910';
SELECT CONVERT(varchar(6),DATEADD(month,number,@min+'01'),112) AS [Date],
FirstDay = DATEADD(month,number,@min+'01'),
LastDay = DATEADD(day,-1, DATEADD(month,number+1,@min+'01'))
FROM master.dbo.spt_values
WHERE type = 'p'
AND DATEADD(month,number,@min+'01') <= @max+'01';
Select * From test Where Convert(varchar(10),time,120) = Convert(varchar(10),getDate(),120)
select cast(convert(varchar(8),getdate(),120)+'01' as datetime)
/**
-----------------------
2009-10-01 00:00:00.000
(1 行受影响)
**/
select dateadd(day,-1,convert(varchar(8),dateadd(month,1,getdate()),120)+'01')
/**
-----------------------
2009-10-31 00:00:00.000
(1 行受影响)
**/
DECLARE @min varchar(6),@max varchar(6);
SELECT @min = '200804',@max = '200910';
SELECT CONVERT(varchar(6),DATEADD(month,number,@min+'01'),112) AS [Date],
FirstDay = DATEADD(month,number,@min+'01'),
LastDay = DATEADD(day,-1, DATEADD(month,number+1,@min+'01'))
FROM master.dbo.spt_values
WHERE type = 'p'
AND DATEADD(month,number,@min+'01') <= @max+'01';
/*
Date FirstDay LastDay
------ ----------------------- -----------------------
200804 2008-04-01 00:00:00.000 2008-04-30 00:00:00.000
200805 2008-05-01 00:00:00.000 2008-05-31 00:00:00.000
200806 2008-06-01 00:00:00.000 2008-06-30 00:00:00.000
200807 2008-07-01 00:00:00.000 2008-07-31 00:00:00.000
200808 2008-08-01 00:00:00.000 2008-08-31 00:00:00.000
200809 2008-09-01 00:00:00.000 2008-09-30 00:00:00.000
200810 2008-10-01 00:00:00.000 2008-10-31 00:00:00.000
200811 2008-11-01 00:00:00.000 2008-11-30 00:00:00.000
200812 2008-12-01 00:00:00.000 2008-12-31 00:00:00.000
200901 2009-01-01 00:00:00.000 2009-01-31 00:00:00.000
200902 2009-02-01 00:00:00.000 2009-02-28 00:00:00.000
200903 2009-03-01 00:00:00.000 2009-03-31 00:00:00.000
200904 2009-04-01 00:00:00.000 2009-04-30 00:00:00.000
200905 2009-05-01 00:00:00.000 2009-05-31 00:00:00.000
200906 2009-06-01 00:00:00.000 2009-06-30 00:00:00.000
200907 2009-07-01 00:00:00.000 2009-07-31 00:00:00.000
200908 2009-08-01 00:00:00.000 2009-08-31 00:00:00.000
200909 2009-09-01 00:00:00.000 2009-09-30 00:00:00.000
200910 2009-10-01 00:00:00.000 2009-10-31 00:00:00.000
(19 行受影响)
*/
指定日期所在月份的第一天或最后一天
--A. 月的第一天
SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')
--B. 月的最后一天
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
SELECT CAST((LEFT('20093',4)+'-'+SUBSTRING('20093',5,LEN('20093'))+'-'+'01') AS DATETIME)
--第一天
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1,CAST((LEFT('20093',4)+'-'+SUBSTRING('20093',5,LEN('20093'))+'-'+'01') AS DATETIME)))
--最后一天
declare @s varchar(50)
set @s='200803'
declare @e varchar(50)
set @e='200910'
select convert( varchar(6),p,112) from
(
select p=dateadd(month,number,cast(@s+'01' as datetime)) from master..spt_values where type='p'
) t
where p<=cast(@e+'01' as datetime)
/*
------
200803
200804
200805
200806
200807
200808
200809
200810
200811
200812
200901
200902
200903
200904
200905
200906
200907
200908
200909
200910
*/
SELECT DATEADD(MM,NUMBER,'2008-03-01')
FROM MASTER..SPT_VALUES WHERE TYPE='P'
AND DATEADD(MM,NUMBER,'2008-03-01')<='2009-10-01'
------------------------------------------------------
2008-03-01 00:00:00.000
2008-04-01 00:00:00.000
2008-05-01 00:00:00.000
2008-06-01 00:00:00.000
2008-07-01 00:00:00.000
2008-08-01 00:00:00.000
2008-09-01 00:00:00.000
2008-10-01 00:00:00.000
2008-11-01 00:00:00.000
2008-12-01 00:00:00.000
2009-01-01 00:00:00.000
2009-02-01 00:00:00.000
2009-03-01 00:00:00.000
2009-04-01 00:00:00.000
2009-05-01 00:00:00.000
2009-06-01 00:00:00.000
2009-07-01 00:00:00.000
2009-08-01 00:00:00.000
2009-09-01 00:00:00.000
2009-10-01 00:00:00.000
(所影响的行数为 20 行)
declare @sdate varchar(10)
declare @edate varchar(10)
set @sdate = '200803'
set @edate = '200910'
set @sdate = left(@sdate,4) + '-' + right(@sdate,2) + '-01'
set @edate = left(@edate,4) + '-' + right(@edate,2) + '-01'
select
dateadd(mm,num,@sdate)
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(mm,num,@sdate)<=@edate
/*
------------------------------------------------------
2008-03-01 00:00:00.000
2008-04-01 00:00:00.000
2008-05-01 00:00:00.000
2008-06-01 00:00:00.000
2008-07-01 00:00:00.000
2008-08-01 00:00:00.000
2008-09-01 00:00:00.000
2008-10-01 00:00:00.000
2008-11-01 00:00:00.000
2008-12-01 00:00:00.000
2009-01-01 00:00:00.000
2009-02-01 00:00:00.000
2009-03-01 00:00:00.000
2009-04-01 00:00:00.000
2009-05-01 00:00:00.000
2009-06-01 00:00:00.000
2009-07-01 00:00:00.000
2009-08-01 00:00:00.000
2009-09-01 00:00:00.000
2009-10-01 00:00:00.000
(所影响的行数为 20 行)
*/
DECLARE @min varchar(6),@max varchar(6);
SELECT @min = '200804',@max = '200910';
SELECT CONVERT(varchar(6),DATEADD(month,number,@min+'01'),112) AS [Date]
FROM master.dbo.spt_values
WHERE type = 'p'
AND DATEADD(month,number,@min+'01') <= @max+'01';
/*
Date
------
200804
200805
200806
200807
200808
200809
200810
200811
200812
200901
200902
200903
200904
200905
200906
200907
200908
200909
200910
(19 行受影响)
*/
DECLARE @min varchar(6),@max varchar(6);
SELECT @min = '200804',@max = '200910';
SELECT CONVERT(varchar(6),DATEADD(month,number,@min+'01'),120) AS [Date]
FROM master.dbo.spt_values
WHERE type = 'p'
AND DATEADD(month,number,@min+'01') <= @max+'01';
--sql server 2000
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2008-01-01'
set @dt2 = '2009-01-01'
--使用一个临时表
select top 8000 identity(int,0,1) as id into # from syscolumns a,syscolumns b
select yuefen = convert(varchar(7),dateadd(mm , m.id , @dt1 ),120) from # m where convert(varchar(7),dateadd(mm , m.id , @dt1 ),120) <= convert(varchar(7),@dt2,120)
drop table #
/*
yuefen
-------
2008-01
2008-02
2008-03
2008-04
2008-05
2008-06
2008-07
2008-08
2008-09
2008-10
2008-11
2008-12
2009-01
(所影响的行数为 13 行)
*/
--sql server 2005
declare @dt1 as datetime
declare @dt2 as datetime
set @dt1 = '2008-01-01'
set @dt2 = '2009-01-01'
;with t
as
(select @dt1 as y
union all
select dateadd(m,1,y) from t where y <@dt2)
select * from t
/*
y
-----------------------
2008-01-01 00:00:00.000
2008-02-01 00:00:00.000
2008-03-01 00:00:00.000
2008-04-01 00:00:00.000
2008-05-01 00:00:00.000
2008-06-01 00:00:00.000
2008-07-01 00:00:00.000
2008-08-01 00:00:00.000
2008-09-01 00:00:00.000
2008-10-01 00:00:00.000
2008-11-01 00:00:00.000
2008-12-01 00:00:00.000
2009-01-01 00:00:00.000
*/