--定义给定的一天
DECLARE @Date DATETIME = GETDATE();
SELECT @Date AS '目前时间'
,DATEADD(DD,-1,@Date) AS '前一天'
,DATEADD(DD,1,@Date) AS '后一天'
/月计算/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'
--在SQL Server中0 代表1900-01-01,通过月运算,保证日恒久为1号
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'
--找到下月初再扣减1天,建议使用DATEADD而不要直接“-1”
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天'
/周计算/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'
--注意此处与@@datefirst的值有关
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'-
-注意此处与@@datefirst的值有关
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'
--注意此处与@@datefirst的值有关,其他天数类推
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'-
-注意此处与@@datefirst的值有关
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'
--注意此处与@@datefirst的值有关
,DATENAME(WEEKDAY,@Date) AS '本日是周几'
,DATEPART(WEEKDAY,@Date) AS '本日是周几'
--返回值 1-星期日,2-星期一,3-星期二......7-星期六
/年度计算/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/季度计算/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'
根据英语语言和英文日期格式(日月年)显示相应日期元素:
create table t1
(
id int,
riqi varchar(20)
)
insert into t1
select 1, '2-01-2001' union all
select 2, '12-03-2002' union all
select 3, '21-03-2003' union all
select 4, '01-05-2001' union all
select 5, '02-02-2001'
set language us_english --设置为英语模式
set dateformat dmy --设置日期格式为日月年
select id,DAY(riqi) as riqi1,datename(MONTH,riqi) as riqi2,YEAR(riqi) as riqi3 from t1
查询当前月非周六、周日:
SET DATEFIRST 1
DECLARE @Start DATETIME=CAST(CONVERT(VARCHAR(7),CURRENT_TIMESTAMP,120)+'-01' AS DATETIME), @End DATETIME=DATEADD(month,1,CAST(CONVERT(VARCHAR(7),CURRENT_TIMESTAMP,120)+'-01' AS DATETIME))
SELECT DATEADD(DAY,number,@Start)
FROM master..spt_values WITH(NOLOCK)
WHERE type='P'
AND DATEADD(DAY,number,@Start)<@End
AND DATEPART(weekday,DATEADD(DAY,number,@Start)) NOT IN (6,7)
给定一个开始日期,一个结束日期,显示中间的全部日期:
declare @startdate datetime,
@enddate datetime
select @startdate='2011-07-15',@enddate='2011-07-22'
select DATEADD(DAY,number,@startdate) as riqi
from master..spt_values where type='P' and number between 0 and DATEDIFF(DAY,@startdate,@enddate)
利用MASTER数据库的表生成某一日期段内的日期表:
declare @Start datetime, @End datetime
select @Start='2010-01-01',@End='2011-12-31'
;with aaa as
(select CONVERT(varchar(7),DATEADD(month,number,@Start),120) as riqi
from master..spt_values where ='p' and number between 0 and DATEDIFF(MONTH,@Start,@End)
)
select * from aaa
13 个回复
梁勇 - 天道酬勤、上善若水。爱好商业智能 2013-08-25 回答
赞同来自: 冰咖啡 、我就是宇宙
梁勇 - 天道酬勤、上善若水。爱好商业智能 2013-08-25 回答
赞同来自: 冰咖啡 、我就是宇宙
戴俊青 - 微软数据库开发和性能调优 2013-08-26 回答
赞同来自: 梁勇
DECLARE @Date DATETIME = GETDATE();
SELECT @Date AS '目前时间'
,DATEADD(DD,-1,@Date) AS '前一天'
,DATEADD(DD,1,@Date) AS '后一天'
/月计算/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'
--在SQL Server中0 代表1900-01-01,通过月运算,保证日恒久为1号
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'
--找到下月初再扣减1天,建议使用DATEADD而不要直接“-1”
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天'
/周计算/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'
--注意此处与@@datefirst的值有关
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'-
-注意此处与@@datefirst的值有关
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'
--注意此处与@@datefirst的值有关,其他天数类推
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'-
-注意此处与@@datefirst的值有关
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'
--注意此处与@@datefirst的值有关
,DATENAME(WEEKDAY,@Date) AS '本日是周几'
,DATEPART(WEEKDAY,@Date) AS '本日是周几'
--返回值 1-星期日,2-星期一,3-星期二......7-星期六
/年度计算/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/季度计算/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'
gogodiy - 天善智能数据库专家、Tableau爱好者 2013-09-12 回答
赞同来自: 冰咖啡
Select CONVERT(varchar(100), GETDBTE(), 0): 05 16 2006 10:57BM
Select CONVERT(varchar(100), GETDBTE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDBTE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDBTE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDBTE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDBTE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDBTE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDBTE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDBTE(), 8 ): 10:57:46
Select CONVERT(varchar(100), GETDBTE(), 9): 05 16 2006 10:57:46:827BM
Select CONVERT(varchar(100), GETDBTE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDBTE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDBTE(), 12): 060516
Select CONVERT(varchar(100), GETDBTE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDBTE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDBTE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDBTE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDBTE(), 22): 05/16/06 10:57:47 BM
Select CONVERT(varchar(100), GETDBTE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDBTE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDBTE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDBTE(), 100): 05 16 2006 10:57BM
Select CONVERT(varchar(100), GETDBTE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDBTE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDBTE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDBTE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDBTE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDBTE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDBTE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDBTE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDBTE(), 109): 05 16 2006 10:57:49:437BM
Select CONVERT(varchar(100), GETDBTE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDBTE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDBTE(), 112): 20060516
Select CONVERT(varchar(100), GETDBTE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDBTE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDBTE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDBTE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDBTE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDBTE(), 130): 18 ???? ?????? 1427 10:57:49:907BM
Select CONVERT(varchar(100), GETDBTE(), 131): 18/04/1427 10:57:49:920BM
gogodiy - 天善智能数据库专家、Tableau爱好者 2013-09-12 回答
赞同来自: 梁勇
本年的第一天:Select dateadd(year,datediff(year,0,getdate()),0)
本年的最后一天:select dateadd(day,-1,dateadd(year,datediff(year,0,getdate())+1,0))
上年的最后一天:select dateadd(day,-1,dateadd(year,datediff(year,0,getdate()),0))
本季度的第一天:Select dateadd(quarter,datediff(quarter,0,getdate()),0)
本季度的最后一天:Select dateadd(day,-1,dateadd(quarter,datediff(quarter,0,getdate())+1,0))
上季度的最后一天:Select dateadd(day,-1,dateadd(quarter,datediff(quarter,0,getdate()),0))
本月的第一天:Select dateadd(month,datediff(month,0,getdate()),0)
本月的最后一天:Select dateadd(day,-1,dateadd(month,datediff(month,0,getdate())+1,0))
上月的最后一天:Select dateadd(day,-1,dateadd(month,datediff(month,0,getdate()),0))
本周的第一天:Select dateadd(week,datediff(week,0,getdate()),0)
本周的最后一天:Select dateadd(day,-1,dateadd(week,datediff(week,0,getdate())+1,0))
上周的最后一天:Select dateadd(day,-1,dateadd(week,datediff(week,0,getdate()),0))
总之,先使用datediff函数根据日期单位(年,月,日,季,周等)计算与基础日期1900-01-01之间的差值,然后再使用dateadd函数根据日期单位(年,月,日,季,周等)在基础日期1900-01-01的基础上加上差值。灵活运用各种函数。
gogodiy - 天善智能数据库专家、Tableau爱好者 2013-09-12 回答
赞同来自: 梁勇
create table t1
(
id int,
riqi varchar(20)
)
insert into t1
select 1, '2-01-2001' union all
select 2, '12-03-2002' union all
select 3, '21-03-2003' union all
select 4, '01-05-2001' union all
select 5, '02-02-2001'
set language us_english --设置为英语模式
set dateformat dmy --设置日期格式为日月年
select id,DAY(riqi) as riqi1,datename(MONTH,riqi) as riqi2,YEAR(riqi) as riqi3 from t1
TravyLee - 尼玛!这哪是下雨,分明就是有人在泼水! 2013-08-27 回答
赞同来自:
xiaoxiao - 两年程序员 2013-08-27 回答
赞同来自:
小耳朵 2013-08-28 回答
赞同来自:
gogodiy - 天善智能数据库专家、Tableau爱好者 2013-09-12 回答
赞同来自:
SET DATEFIRST 1
DECLARE @Start DATETIME=CAST(CONVERT(VARCHAR(7),CURRENT_TIMESTAMP,120)+'-01' AS DATETIME),
@End DATETIME=DATEADD(month,1,CAST(CONVERT(VARCHAR(7),CURRENT_TIMESTAMP,120)+'-01' AS DATETIME))
SELECT DATEADD(DAY,number,@Start)
FROM master..spt_values WITH(NOLOCK)
WHERE type='P'
AND DATEADD(DAY,number,@Start)<@End
AND DATEPART(weekday,DATEADD(DAY,number,@Start)) NOT IN (6,7)
gogodiy - 天善智能数据库专家、Tableau爱好者 2013-09-12 回答
赞同来自:
declare @startdate datetime,
@enddate datetime
select @startdate='2011-07-15',@enddate='2011-07-22'
select DATEADD(DAY,number,@startdate) as riqi
from master..spt_values where type='P' and number between 0 and DATEDIFF(DAY,@startdate,@enddate)
gogodiy - 天善智能数据库专家、Tableau爱好者 2013-09-12 回答
赞同来自:
declare @Start datetime,
@End datetime
select @Start='2010-01-01',@End='2011-12-31'
;with aaa as
(select CONVERT(varchar(7),DATEADD(month,number,@Start),120) as riqi
from master..spt_values where ='p' and number between 0 and DATEDIFF(MONTH,@Start,@End)
)
select * from aaa
天桥下的郑成功 - Hadoop大数据开发工程师、数仓架构师、熟悉数据仓库设计、Hadoop、Spark、HBase、Hive、SSIS等开发 2013-12-05 回答
赞同来自:
日期转换的时候 是个很好的参考。 感谢假日的热心分享。。。