CONVERT在日期转换中的使用示例.sql:
- --字符转换为日期时,Style的使用
- --1. Style=101时,表示日期字符串为:mm/dd/yyyy格式
- SELECT CONVERT(datetime,'11/1/2003',101)
- --结果:2003-11-01 00:00:00.000
- --2. Style=101时,表示日期字符串为:dd/mm/yyyy格式
- SELECT CONVERT(datetime,'11/1/2003',103)
- --结果:2003-01-11 00:00:00.000
- /*== 日期转换为字符串 ==*/
- DECLARE @dt datetime
- SET @dt='2003-1-11'
- --1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式
- SELECT CONVERT(varchar,@dt,101)
- --结果:01/11/2003
- --2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式
- SELECT CONVERT(varchar,@dt,103)
- --结果:11/01/2003
- /*== 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 ==*/
- SELECT CONVERT(varchar,'2003-1-11',101)
- --结果:2003-1-11
SET DATEFORMAT对日期处理的影响.sql
- --1.
- /*--说明
- SET DATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响
- 但不影响明确指定了style的CONVERT处理。
- --*/
- --示例 ,在下面的示例中,第一个CONVERT转换未指定style,转换的结果受SET DATAFORMAT的影响,第二个CONVERT转换指定了style,转换结果受style的影响。
- --设置输入日期顺序为 日/月/年
- SET DATEFORMAT DMY
- --不指定Style参数的CONVERT转换将受到SET DATEFORMAT的影响
- SELECT CONVERT(datetime,'2-1-2005')
- --结果: 2005-01-02 00:00:00.000
- --指定Style参数的CONVERT转换不受SET DATEFORMAT的影响
- SELECT CONVERT(datetime,'2-1-2005',101)
- --结果: 2005-02-01 00:00:00.000
- GO
- --2.
- /*--说明
- 如果输入的日期包含了世纪部分,则对日期进行解释处理时
- 年份的解释不受SET DATEFORMAT设置的影响。
- --*/
- --示例,在下面的代码中,同样的SET DATEFORMAT设置,输入日期的世纪部分与不输入日期的世纪部分,解释的日期结果不同。
- DECLARE @dt datetime
- --设置SET DATEFORMAT为:月日年
- SET DATEFORMAT MDY
- --输入的日期中指定世纪部分
- SET @dt='01-2002-03'
- SELECT @dt
- --结果: 2002-01-03 00:00:00.000
- --输入的日期中不指定世纪部分
- SET @dt='01-02-03'
- SELECT @dt
- --结果: 2003-01-02 00:00:00.000
- GO
- --3.
- /*--说明
- 如果输入的日期不包含日期分隔符,那么SQL Server在对日期进行解释时
- 将忽略SET DATEFORMAT的设置。
- --*/
- --示例,在下面的代码中,不包含日期分隔符的字符日期,在不同的SET DATEFORMAT设置下,其解释的结果是一样的。
- DECLARE @dt datetime
- --设置SET DATEFORMAT为:月日年
- SET DATEFORMAT MDY
- SET @dt='010203'
- SELECT @dt
- --结果: 2001-02-03 00:00:00.000
- --设置SET DATEFORMAT为:日月年
- SET DATEFORMAT DMY
- SET @dt='010203'
- SELECT @dt
- --结果: 2001-02-03 00:00:00.000
- --输入的日期中包含日期分隔符
- SET @dt='01-02-03'
- SELECT @dt
- --结果: 2003-02-01 00:00:00.000
SET LANGUAGE对日期处理的影响示例.sql
- --以下示例演示了在不同的语言环境(SET LANGUAGE)下,DATENAME与CONVERT函数的不同结果。
- USE master
- --设置会话的语言环境为: English
- SET LANGUAGE N'English'
- SELECT
- DATENAME(Month,GETDATE()) AS [Month],
- DATENAME(Weekday,GETDATE()) AS [Weekday],
- CONVERT(varchar,GETDATE(),109) AS [CONVERT]
- /*--结果:
- Month Weekday CONVERT
- ------------- -------------- -------------------------------
- March Tuesday Mar 15 2005 8:59PM
- --*/
- --设置会话的语言环境为: 简体中文
- SET LANGUAGE N'简体中文'
- SELECT
- DATENAME(Month,GETDATE()) AS [Month],
- DATENAME(Weekday,GETDATE()) AS [Weekday],
- CONVERT(varchar,GETDATE(),109) AS [CONVERT]
- /*--结果
- Month Weekday CONVERT
- ------------- --------------- -----------------------------------------
- 05 星期四 05 19 2005 2:49:20:607PM
- --*/
日期格式化处理.sql
- DECLARE @dt datetime
- SET @dt=GETDATE()
- --1.短日期格式:yyyy-m-d
- SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')
- --2.长日期格式:yyyy年mm月dd日
- --A. 方法1
- SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'
- --B. 方法2
- SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'
- --3.长日期格式:yyyy年m月d日
- SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日'
- --4.完整日期+时间格式:yyyy-mm-dd hh:mi:ss:mmm
- SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)
日期推算处理.sql
- DECLARE @dt datetime
- SET @dt=GETDATE()
- DECLARE @number int
- SET @number=3
- --1.指定日期该年的第一天或最后一天
- --A. 年的第一天
- SELECT CONVERT(char(5),@dt,120)+'1-1'
- --B. 年的最后一天
- SELECT CONVERT(char(5),@dt,120)+'12-31'
- --2.指定日期所在季度的第一天或最后一天
- --A. 季度的第一天
- SELECT CONVERT(datetime,
- CONVERT(char(8),
- DATEADD(Month,
- DATEPART(Quarter,@dt)*3-Month(@dt)-2,
- @dt),
- 120)+'1')
- --B. 季度的最后一天(CASE判断法)
- SELECT CONVERT(datetime,
- CONVERT(char(8),
- DATEADD(Month,
- DATEPART(Quarter,@dt)*3-Month(@dt),
- @dt),
- 120)
- +CASE WHEN DATEPART(Quarter,@dt) in(1,4)
- THEN '31'ELSE '30' END)
- --C. 季度的最后一天(直接推算法)
- SELECT DATEADD(Day,-1,
- CONVERT(char(8),
- DATEADD(Month,
- 1+DATEPART(Quarter,@dt)*3-Month(@dt),
- @dt),
- 120)+'1')
- --3.指定日期所在月份的第一天或最后一天
- --A. 月的第一天
- SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')
- --B. 月的最后一天
- SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')
- --C. 月的最后一天(容易使用的错误方法)
- SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))
- --4.指定日期所在周的任意一天
- SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)
- --5.指定日期所在周的任意星期几
- --A. 星期天做为一周的第1天
- SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
- --B. 星期一做为一周的第1天
- SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
特殊日期加减函数.sql
- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_DateADD]') and xtype in (N'FN', N'IF', N'TF'))
- drop function [dbo].[f_DateADD]
- GO
- /*--特殊日期加减函数
- 对于日期指定部分的加减,使用DATEADD函数就可以轻松实现。
- 在实际的处理中,还有一种比较另类的日期加减处理
- 就是在指定的日期中,加上(或者减去)多个日期部分
- 比如将2005年3月11日,加上1年3个月11天2小时。
- 对于这种日期的加减处理,DATEADD函数的力量就显得有点不够。
- 本函数实现这样格式的日期字符串加减处理:
- y-m-d h:m:s.m | -y-m-d h:m:s.m
- 说明:
- 要加减的日期字符输入方式与日期字符串相同。日期与时间部分用空格分隔
- 最前面一个字符如果是减号(-)的话,表示做减法处理,否则做加法处理。
- 如果日期字符只包含数字,则视为日期字符中,仅包含天的信息。
- --*/
- /*--调用示例
- SELECT dbo.f_DateADD(GETDATE(),'11:10')
- --*/
- CREATE FUNCTION dbo.f_DateADD(
- @Date datetime,
- @DateStr varchar(23)
- )RETURNS datetime
- AS
- BEGIN
- DECLARE @bz int,@s varchar(12),@i int
- IF @DateStr IS NULL OR @Date IS NULL
- OR(CHARINDEX('.',@DateStr)>0
- AND @DateStr NOT LIKE '%[:]%[:]%.%')
- RETURN(NULL)
- IF @DateStr='' RETURN(@Date)
- SELECT @bz=CASE
- WHEN LEFT(@DateStr,1)='-' THEN -1
- ELSE 1 END,
- @DateStr=CASE
- WHEN LEFT(@Date,1)='-'
- THEN STUFF(RTRIM(LTRIM(@DateStr)),1,1,'')
- ELSE RTRIM(LTRIM(@DateStr)) END
- IF CHARINDEX(' ',@DateStr)>1
- OR CHARINDEX('-',@DateStr)>1
- OR(CHARINDEX('.',@DateStr)=0
- AND CHARINDEX(':',@DateStr)=0)
- BEGIN
- SELECT @i=CHARINDEX(' ',@DateStr+' ')
- ,@s=REVERSE(LEFT(@DateStr,@i-1))+'-'
- ,@DateStr=STUFF(@DateStr,1,@i,'')
- ,@i=0
- WHILE @s>'' and @i<3
- SELECT @Date=CASE @i
- WHEN 0 THEN DATEADD(Day,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)
- WHEN 1 THEN DATEADD(Month,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)
- WHEN 2 THEN DATEADD(Year,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)
- END,
- @s=STUFF(@s,1,CHARINDEX('-',@s),''),
- @i=@i+1
- END
- IF @DateStr>''
- BEGIN
- IF CHARINDEX('.',@DateStr)>0
- SELECT @Date=DATEADD(Millisecond
- ,@bz*STUFF(@DateStr,1,CHARINDEX('.',@DateStr),''),
- @Date),
- @DateStr=LEFT(@DateStr,CHARINDEX('.',@DateStr)-1)+':',
- @i=0
- ELSE
- SELECT @DateStr=@DateStr+':',@i=0
- WHILE @DateStr>'' and @i<3
- SELECT @Date=CASE @i
- WHEN 0 THEN DATEADD(Hour,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
- WHEN 1 THEN DATEADD(Minute,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
- WHEN 2 THEN DATEADD(Second,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
- END,
- @DateStr=STUFF(@DateStr,1,CHARINDEX(':',@DateStr),''),
- @i=@i+1
- END
- RETURN(@Date)
- END
- GO
查询指定日期段内过生日的人员.sql
- --测试数据
- DECLARE @t TABLE(ID int,Name varchar(10),Birthday datetime)
- INSERT @t SELECT 1,'aa','1999-01-01'
- UNION ALL SELECT 2,'bb','1996-02-29'
- UNION ALL SELECT 3,'bb','1934-03-01'
- UNION ALL SELECT 4,'bb','1966-04-01'
- UNION ALL SELECT 5,'bb','1997-05-01'
- UNION ALL SELECT 6,'bb','1922-11-21'
- UNION ALL SELECT 7,'bb','1989-12-11'
- DECLARE @dt1 datetime,@dt2 datetime
- --查询 2003-12-05 至 2004-02-28 生日的记录
- SELECT @dt1='2003-12-05',@dt2='2004-02-28'
- SELECT * FROM @t
- WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
- BETWEEN @dt1 AND @dt2
- OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
- BETWEEN @dt1 AND @dt2
- /*--结果
- ID Name Birthday
- ---------------- ---------------- --------------------------
- 1 aa 1999-01-01 00:00:00.000
- 7 bb 1989-12-11 00:00:00.000
- --*/
- --查询 2003-12-05 至 2006-02-28 生日的记录
- SET @dt2='2006-02-28'
- SELECT * FROM @t
- WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
- BETWEEN @dt1 AND @dt2
- OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
- BETWEEN @dt1 AND @dt2
- /*--查询结果
- ID Name Birthday
- ---------------- ----------------- --------------------------
- 1 aa 1999-01-01 00:00:00.000
- 2 bb 1996-02-29 00:00:00.000
- 7 bb 1989-12-11 00:00:00.000
- --*/
生成日期列表的函数.sql
- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdate]') and xtype in (N'FN', N'IF', N'TF'))
- drop function [dbo].[f_getdate]
- GO
- /*--生成日期列表
- 生成指定年份的工作日/休息日列表
- --邹建 2003.12(引用请保留此信息)--*/
- /*--调用示例
- --查询 2003 年的工作日列表
- SELECT * FROM dbo.f_getdate(2003,0)
- --查询 2003 年的休息日列表
- SELECT * FROM dbo.f_getdate(2003,1)
- --查询 2003 年全部日期列表
- SELECT * FROM dbo.f_getdate(2003,NULL)
- --*/
- CREATE FUNCTION dbo.f_getdate(
- @year int, --要查询的年份
- @bz bit --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
- )RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
- AS
- BEGIN
- DECLARE @tb TABLE(ID int IDENTITY(0,1),Date datetime)
- INSERT INTO @tb(Date) SELECT TOP 366 DATEADD(Year,@YEAR-1900,'1900-1-1')
- FROM sysobjects a ,sysobjects b
- UPDATE @tb SET Date=DATEADD(DAY,id,Date)
- DELETE FROM @tb WHERE Date>DATEADD(Year,@YEAR-1900,'1900-12-31')
- IF @bz=0
- INSERT INTO @re(Date,Weekday)
- SELECT Date,DATENAME(Weekday,Date)
- FROM @tb
- WHERE (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
- ELSE IF @bz=1
- INSERT INTO @re(Date,Weekday)
- SELECT Date,DATENAME(Weekday,Date)
- FROM @tb
- WHERE (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 IN (0,6)
- ELSE
- INSERT INTO @re(Date,Weekday)
- SELECT Date,DATENAME(Weekday,Date)
- FROM @tb
- RETURN
- END
- GO
- /*====================================================================*/
- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdate]') and xtype in (N'FN', N'IF', N'TF'))
- drop function [dbo].[f_getdate]
- GO
- /*--生成列表
- 生成指定日期段的日期列表
- --邹建 2005.03(引用请保留此信息)--*/
- /*--调用示例
- --查询工作日
- SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0)
- --查询休息日
- SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',1)
- --查询全部日期
- SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',NULL)
- --*/
- CREATE FUNCTION dbo.f_getdate(
- @begin_date Datetime, --要查询的开始日期
- @end_date Datetime, --要查询的结束日期
- @bz bit --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
- )RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
- AS
- BEGIN
- DECLARE @tb TABLE(ID int IDENTITY(0,1),a bit)
- INSERT INTO @tb(a) SELECT TOP 366 0
- FROM sysobjects a ,sysobjects b
- IF @bz=0
- WHILE @begin_date<=@end_date
- BEGIN
- INSERT INTO @re(Date,Weekday)
- SELECT Date,DATENAME(Weekday,Date)
- FROM(
- SELECT Date=DATEADD(Day,ID,@begin_date)
- FROM @tb
- )a WHERE Date<=@end_date
- AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
- SET @begin_date=DATEADD(Day,366,@begin_date)
- END
- ELSE IF @bz=1
- WHILE @begin_date<=@end_date
- BEGIN
- INSERT INTO @re(Date,Weekday)
- SELECT Date,DATENAME(Weekday,Date)
- FROM(
- SELECT Date=DATEADD(Day,ID,@begin_date)
- FROM @tb
- )a WHERE Date<=@end_date
- AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 in(0,6)
- SET @begin_date=DATEADD(Day,366,@begin_date)
- END
- ELSE
- WHILE @begin_date<=@end_date
- BEGIN
- INSERT INTO @re(Date,Weekday)
- SELECT Date,DATENAME(Weekday,Date)
- FROM(
- SELECT Date=DATEADD(Day,ID,@begin_date)
- FROM @tb
- )a WHERE Date<=@end_date
- SET @begin_date=DATEADD(Day,366,@begin_date)
- END
- RETURN
- END
- GO