设为首页收藏本站Access中国
开启辅助访问 切换到宽版

Office中国论坛/Access中国论坛

 找回密码
 注册

QQ登录

只需一步，快速开始

Office中国论坛/Access中国论坛»论坛首页 › ACCESS 与Sql Server 讨论区 › Access ADP交流 › 日期相关的SQL大全
返回列表 发新帖
查看: 4417|回复: 7
打印 上一主题 下一主题

日期相关的SQL大全

[复制链接]
andymark
跳转到指定楼层
1#
发表于 2011-11-17 23:18:22 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
1.日期概念理解中的一些测试.sql:
  1. SQL code
  2. --A. 测试 datetime 精度问题
  3. DECLARE @t TABLE(date char(21))
  4. INSERT @t SELECT '1900-1-1 00:00:00.000'
  5. INSERT @t SELECT '1900-1-1 00:00:00.001'
  6. INSERT @t SELECT '1900-1-1 00:00:00.009'
  7. INSERT @t SELECT '1900-1-1 00:00:00.002'
  8. INSERT @t SELECT '1900-1-1 00:00:00.003'
  9. INSERT @t SELECT '1900-1-1 00:00:00.004'
  10. INSERT @t SELECT '1900-1-1 00:00:00.005'
  11. INSERT @t SELECT '1900-1-1 00:00:00.006'
  12. INSERT @t SELECT '1900-1-1 00:00:00.007'
  13. INSERT @t SELECT '1900-1-1 00:00:00.008'
  14. SELECT date,转换后的日期=CAST(date as datetime) FROM @t

  16. /*--结果

  18. date                  转换后的日期
  19. --------------------- --------------------------
  20. 1900-1-1 00:00:00.000 1900-01-01 00:00:00.000
  21. 1900-1-1 00:00:00.001 1900-01-01 00:00:00.000
  22. 1900-1-1 00:00:00.009 1900-01-01 00:00:00.010
  23. 1900-1-1 00:00:00.002 1900-01-01 00:00:00.003
  24. 1900-1-1 00:00:00.003 1900-01-01 00:00:00.003
  25. 1900-1-1 00:00:00.004 1900-01-01 00:00:00.003
  26. 1900-1-1 00:00:00.005 1900-01-01 00:00:00.007
  27. 1900-1-1 00:00:00.006 1900-01-01 00:00:00.007
  28. 1900-1-1 00:00:00.007 1900-01-01 00:00:00.007
  29. 1900-1-1 00:00:00.008 1900-01-01 00:00:00.007

  31. （所影响的行数为 10 行）
  32. --*/
  33. GO

  35. --B. 对于 datetime 类型的纯日期和时间的十六进制表示
  36. DECLARE @dt datetime

  38. --单纯的日期
  39. SET @dt='1900-1-2'
  40. SELECT CAST(@dt as binary(8))
  41. --结果: 0x0000000100000000

  43. --单纯的时间
  44. SET @dt='00:00:01'
  45. SELECT CAST(@dt as binary(8))
  46. --结果: 0x000000000000012C
  47. GO

  49. --C. 对于 smalldatetime 类型的纯日期和时间的十六进制表示
  50. DECLARE @dt smalldatetime

  52. --单纯的日期
  53. SET @dt='1900-1-2'
  54. SELECT CAST(@dt as binary(4))
  55. --结果: 0x00010000

  57. --单纯的时间
  58. SET @dt='00:10'
  59. SELECT CAST(@dt as binary(4))
  60. --结果: 0x0000000A


复制代码
2.CONVERT在日期转换中的使用示例.sql:
  1. SQL code
  2. --字符转换为日期时,Style的使用

  4. --1. Style=101时,表示日期字符串为:mm/dd/yyyy格式
  5. SELECT CONVERT(datetime,'11/1/2003',101)
  6. --结果:2003-11-01 00:00:00.000

  8. --2. Style=101时,表示日期字符串为:dd/mm/yyyy格式
  9. SELECT CONVERT(datetime,'11/1/2003',103)
  10. --结果:2003-01-11 00:00:00.000


  13. /*== 日期转换为字符串 ==*/
  14. DECLARE @dt datetime
  15. SET @dt='2003-1-11'

  17. --1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式
  18. SELECT CONVERT(varchar,@dt,101)
  19. --结果:01/11/2003

  21. --2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式
  22. SELECT CONVERT(varchar,@dt,103)
  23. --结果:11/01/2003

  25. /*== 这是很多人经常犯的错误,对非日期型转换使用日期的style样式 ==*/
  26. SELECT CONVERT(varchar,'2003-1-11',101)
  27. --结果:2003-1-11


复制代码
3.SET DATEFORMAT对日期处理的影响.sql
  1. SQL code
  2. --1.
  3. /*--说明
  4.     SET DATEFORMAT设置对使用CONVERT把字符型日期转换为日期的处理也具有影响
  5.     但不影响明确指定了style的CONVERT处理。
  6. --*/

  8. --示例 ，在下面的示例中，第一个CONVERT转换未指定style，转换的结果受SET DATAFORMAT的影响，第二个CONVERT转换指定了style，转换结果受style的影响。
  9. --设置输入日期顺序为 日/月/年
  10. SET DATEFORMAT DMY

  12. --不指定Style参数的CONVERT转换将受到SET DATEFORMAT的影响
  13. SELECT CONVERT(datetime,'2-1-2005')
  14. --结果: 2005-01-02 00:00:00.000

  16. --指定Style参数的CONVERT转换不受SET DATEFORMAT的影响
  17. SELECT CONVERT(datetime,'2-1-2005',101)
  18. --结果: 2005-02-01 00:00:00.000
  19. GO

  21. --2.
  22. /*--说明

  24.     如果输入的日期包含了世纪部分，则对日期进行解释处理时
  25.     年份的解释不受SET DATEFORMAT设置的影响。
  26. --*/

  28. --示例，在下面的代码中，同样的SET DATEFORMAT设置，输入日期的世纪部分与不输入日期的世纪部分，解释的日期结果不同。
  29. DECLARE @dt datetime

  31. --设置SET DATEFORMAT为:月日年
  32. SET DATEFORMAT MDY

  34. --输入的日期中指定世纪部分
  35. SET @dt='01-2002-03'
  36. SELECT @dt
  37. --结果: 2002-01-03 00:00:00.000

  39. --输入的日期中不指定世纪部分
  40. SET @dt='01-02-03'
  41. SELECT @dt
  42. --结果: 2003-01-02 00:00:00.000
  43. GO

  45. --3.
  46. /*--说明

  48.     如果输入的日期不包含日期分隔符，那么SQL Server在对日期进行解释时
  49.     将忽略SET DATEFORMAT的设置。
  50. --*/

  52. --示例，在下面的代码中，不包含日期分隔符的字符日期，在不同的SET DATEFORMAT设置下，其解释的结果是一样的。
  53. DECLARE @dt datetime

  55. --设置SET DATEFORMAT为:月日年
  56. SET DATEFORMAT MDY
  57. SET @dt='010203'
  58. SELECT @dt
  59. --结果: 2001-02-03 00:00:00.000

  61. --设置SET DATEFORMAT为:日月年
  62. SET DATEFORMAT DMY
  63. SET @dt='010203'
  64. SELECT @dt
  65. --结果: 2001-02-03 00:00:00.000

  67. --输入的日期中包含日期分隔符
  68. SET @dt='01-02-03'
  69. SELECT @dt
  70. --结果: 2003-02-01 00:00:00.000


复制代码
分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
收藏收藏2 分享分享 分享淘帖 订阅订阅
回复

使用道具 举报

andymark
2#
 楼主| 发表于 2011-11-17 23:20:44 | 只看该作者
4.SET LANGUAGE对日期处理的影响示例.sql
  1. SQL code
  2. --以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。
  3. USE master

  5. --设置会话的语言环境为: English
  6. SET LANGUAGE N'English'
  7. SELECT
  8.     DATENAME(Month,GETDATE()) AS [Month],
  9.     DATENAME(Weekday,GETDATE()) AS [Weekday],
  10.     CONVERT(varchar,GETDATE(),109) AS [CONVERT]
  11. /*--结果:
  12. Month    Weekday   CONVERT
  13. ------------- -------------- -------------------------------
  14. March    Tuesday   Mar 15 2005  8:59PM
  15. --*/

  17. --设置会话的语言环境为: 简体中文
  18. SET LANGUAGE N'简体中文'
  19. SELECT
  20.     DATENAME(Month,GETDATE()) AS [Month],
  21.     DATENAME(Weekday,GETDATE()) AS [Weekday],
  22.     CONVERT(varchar,GETDATE(),109) AS [CONVERT]
  23. /*--结果
  24. Month    Weekday    CONVERT
  25. ------------- --------------- -----------------------------------------
  26. 05       星期四     05 19 2005  2:49:20:607PM
  27. --*/


复制代码
5.日期格式化处理.sql
  1. SQL code
  2. DECLARE @dt datetime
  3. SET @dt=GETDATE()

  5. --1．短日期格式：yyyy-m-d
  6. SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')

  8. --2．长日期格式：yyyy年mm月dd日
  9. --A. 方法1
  10. SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'
  11. --B. 方法2
  12. SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'

  14. --3．长日期格式：yyyy年m月d日
  15. SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日'

  17. --4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm
  18. SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)


复制代码
6.日期推算处理.sql
  1. SQL code
  2. DECLARE @dt datetime
  3. SET @dt=GETDATE()

  5. DECLARE @number int
  6. SET @number=3

  8. --1．指定日期该年的第一天或最后一天
  9. --A. 年的第一天
  10. SELECT CONVERT(char(5),@dt,120)+'1-1'

  12. --B. 年的最后一天
  13. SELECT CONVERT(char(5),@dt,120)+'12-31'


  16. --2．指定日期所在季度的第一天或最后一天
  17. --A. 季度的第一天
  18. SELECT CONVERT(datetime,
  19.     CONVERT(char(8),
  20.         DATEADD(Month,
  21.             DATEPART(Quarter,@dt)*3-Month(@dt)-2,
  22.             @dt),
  23.         120)+'1')

  25. --B. 季度的最后一天（CASE判断法）
  26. SELECT CONVERT(datetime,
  27.     CONVERT(char(8),
  28.         DATEADD(Month,
  29.             DATEPART(Quarter,@dt)*3-Month(@dt),
  30.             @dt),
  31.         120)
  32.     +CASE WHEN DATEPART(Quarter,@dt) in(1,4)
  33.         THEN '31'ELSE '30' END)

  35. --C. 季度的最后一天（直接推算法）
  36. SELECT DATEADD(Day,-1,
  37.     CONVERT(char(8),
  38.         DATEADD(Month,
  39.             1+DATEPART(Quarter,@dt)*3-Month(@dt),
  40.             @dt),
  41.         120)+'1')


  44. --3．指定日期所在月份的第一天或最后一天
  45. --A. 月的第一天
  46. SELECT CONVERT(datetime,CONVERT(char(8),@dt,120)+'1')

  48. --B. 月的最后一天
  49. SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@dt),120)+'1')

  51. --C. 月的最后一天（容易使用的错误方法）
  52. SELECT DATEADD(Month,1,DATEADD(Day,-DAY(@dt),@dt))


  55. --4．指定日期所在周的任意一天
  56. SELECT DATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)


  59. --5．指定日期所在周的任意星期几
  60. --A.  星期天做为一周的第1天
  61. SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)

  63. --B.  星期一做为一周的第1天
  64. SELECT DATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)

复制代码
回复 支持 反对

使用道具 举报

andymark
3#
 楼主| 发表于 2011-11-17 23:23:00 | 只看该作者
7.特殊日期加减函数.sql
  1. SQL code
  2. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_DateADD]') and xtype in (N'FN', N'IF', N'TF'))
  3.     drop function [dbo].[f_DateADD]
  4. GO

  6. /*--特殊日期加减函数

  8.     对于日期指定部分的加减，使用DATEADD函数就可以轻松实现。
  9.     在实际的处理中，还有一种比较另类的日期加减处理
  10.     就是在指定的日期中，加上（或者减去）多个日期部分
  11.     比如将2005年3月11日，加上1年3个月11天2小时。
  12.     对于这种日期的加减处理，DATEADD函数的力量就显得有点不够。

  14.     本函数实现这样格式的日期字符串加减处理：
  15.     y-m-d h:m:s.m | -y-m-d h:m:s.m
  16.     说明：
  17.     要加减的日期字符输入方式与日期字符串相同。日期与时间部分用空格分隔
  18.     最前面一个字符如果是减号（-）的话，表示做减法处理，否则做加法处理。
  19.     如果日期字符只包含数字，则视为日期字符中，仅包含天的信息。
  20. --*/

  22. /*--调用示例

  24.     SELECT dbo.f_DateADD(GETDATE(),'11:10')
  25. --*/

  27. CREATE FUNCTION dbo.f_DateADD(
  28. @Date     datetime,
  29. @DateStr   varchar(23)
  30. )RETURNS datetime
  31. AS
  32. BEGIN
  33.     DECLARE @bz int,@s varchar(12),@i int

  35.     IF @DateStr IS NULL OR @Date IS NULL
  36.         OR(CHARINDEX('.',@DateStr)>0
  37.             AND @DateStr NOT LIKE '%[:]%[:]%.%')
  38.         RETURN(NULL)
  39.     IF @DateStr='' RETURN(@Date)

  41.     SELECT @bz=CASE
  42.             WHEN LEFT(@DateStr,1)='-' THEN -1
  43.             ELSE 1 END,
  44.         @DateStr=CASE
  45.             WHEN LEFT(@Date,1)='-'
  46.             THEN STUFF(RTRIM(LTRIM(@DateStr)),1,1,'')
  47.             ELSE RTRIM(LTRIM(@DateStr)) END

  49.     IF CHARINDEX(' ',@DateStr)>1
  50.         OR CHARINDEX('-',@DateStr)>1
  51.         OR(CHARINDEX('.',@DateStr)=0
  52.             AND CHARINDEX(':',@DateStr)=0)
  53.     BEGIN
  54.         SELECT @i=CHARINDEX(' ',@DateStr+' ')
  55.             ,@s=REVERSE(LEFT(@DateStr,@i-1))+'-'
  56.             ,@DateStr=STUFF(@DateStr,1,@i,'')
  57.             ,@i=0
  58.         WHILE @s>'' and @i<3
  59.             SELECT @Date=CASE @i
  60.                     WHEN 0 THEN DATEADD(Day,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)
  61.                     WHEN 1 THEN DATEADD(Month,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)
  62.                     WHEN 2 THEN DATEADD(Year,@bz*REVERSE(LEFT(@s,CHARINDEX('-',@s)-1)),@Date)
  63.                 END,
  64.                 @s=STUFF(@s,1,CHARINDEX('-',@s),''),
  65.                 @i=@i+1               
  66.     END
  67.     IF @DateStr>''
  68.     BEGIN
  69.         IF CHARINDEX('.',@DateStr)>0
  70.             SELECT @Date=DATEADD(Millisecond
  71.                     ,@bz*STUFF(@DateStr,1,CHARINDEX('.',@DateStr),''),
  72.                     @Date),
  73.                 @DateStr=LEFT(@DateStr,CHARINDEX('.',@DateStr)-1)+':',
  74.                 @i=0
  75.         ELSE
  76.             SELECT @DateStr=@DateStr+':',@i=0
  77.         WHILE @DateStr>'' and @i<3
  78.             SELECT @Date=CASE @i
  79.                     WHEN 0 THEN DATEADD(Hour,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
  80.                     WHEN 1 THEN DATEADD(Minute,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
  81.                     WHEN 2 THEN DATEADD(Second,@bz*LEFT(@DateStr,CHARINDEX(':',@DateStr)-1),@Date)
  82.                 END,
  83.                 @DateStr=STUFF(@DateStr,1,CHARINDEX(':',@DateStr),''),
  84.                 @i=@i+1
  85.     END

  87.     RETURN(@Date)
  88. END
  89. GO


复制代码
8.查询指定日期段内过生日的人员.sql
  1. SQL code
  2. --测试数据
  3. DECLARE @t TABLE(ID int,Name varchar(10),Birthday datetime)
  4. INSERT @t SELECT 1,'aa','1999-01-01'
  5. UNION ALL SELECT 2,'bb','1996-02-29'
  6. UNION ALL SELECT 3,'bb','1934-03-01'
  7. UNION ALL SELECT 4,'bb','1966-04-01'
  8. UNION ALL SELECT 5,'bb','1997-05-01'
  9. UNION ALL SELECT 6,'bb','1922-11-21'
  10. UNION ALL SELECT 7,'bb','1989-12-11'

  12. DECLARE @dt1 datetime,@dt2 datetime

  14. --查询 2003-12-05 至 2004-02-28 生日的记录
  15. SELECT @dt1='2003-12-05',@dt2='2004-02-28'
  16. SELECT * FROM @t
  17. WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
  18.         BETWEEN @dt1 AND @dt2
  19.     OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
  20.         BETWEEN @dt1 AND @dt2
  21. /*--结果
  22. ID         Name       Birthday
  23. ---------------- ---------------- --------------------------
  24. 1           aa         1999-01-01 00:00:00.000
  25. 7           bb         1989-12-11 00:00:00.000
  26. --*/

  28. --查询 2003-12-05 至 2006-02-28 生日的记录
  29. SET @dt2='2006-02-28'
  30. SELECT * FROM @t
  31. WHERE DATEADD(Year,DATEDIFF(Year,Birthday,@dt1),Birthday)
  32.         BETWEEN @dt1 AND @dt2
  33.     OR DATEADD(Year,DATEDIFF(Year,Birthday,@dt2),Birthday)
  34.         BETWEEN @dt1 AND @dt2
  35. /*--查询结果
  36. ID         Name       Birthday
  37. ---------------- ----------------- --------------------------
  38. 1           aa         1999-01-01 00:00:00.000
  39. 2           bb         1996-02-29 00:00:00.000
  40. 7           bb         1989-12-11 00:00:00.000
  41. --*/



复制代码
9.生成日期列表的函数.sql
  1. SQL code
  2. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdate]') and xtype in (N'FN', N'IF', N'TF'))
  3. drop function [dbo].[f_getdate]
  4. GO

  6. /*--生成日期列表
  7.    
  8.     生成指定年份的工作日/休息日列表

  10. --邹建 2003.12(引用请保留此信息)--*/

  12. /*--调用示例

  14.     --查询 2003 年的工作日列表
  15.     SELECT * FROM dbo.f_getdate(2003,0)
  16.    
  17.     --查询 2003 年的休息日列表
  18.     SELECT * FROM dbo.f_getdate(2003,1)

  20.     --查询 2003 年全部日期列表
  21.     SELECT * FROM dbo.f_getdate(2003,NULL)
  22. --*/
  23. CREATE FUNCTION dbo.f_getdate(
  24. @year int,    --要查询的年份
  25. @bz bit       --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
  26. )RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
  27. AS
  28. BEGIN
  29.     DECLARE @tb TABLE(ID int IDENTITY(0,1),Date datetime)
  30.     INSERT INTO @tb(Date) SELECT TOP 366 DATEADD(Year,@YEAR-1900,'1900-1-1')
  31.     FROM sysobjects a ,sysobjects b
  32.     UPDATE @tb SET Date=DATEADD(DAY,id,Date)
  33.     DELETE FROM @tb WHERE Date>DATEADD(Year,@YEAR-1900,'1900-12-31')
  34.    
  35.     IF @bz=0
  36.         INSERT INTO @re(Date,Weekday)
  37.         SELECT Date,DATENAME(Weekday,Date)
  38.         FROM @tb
  39.         WHERE (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
  40.     ELSE IF @bz=1
  41.         INSERT INTO @re(Date,Weekday)
  42.         SELECT Date,DATENAME(Weekday,Date)
  43.         FROM @tb
  44.         WHERE (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 IN (0,6)
  45.     ELSE
  46.         INSERT INTO @re(Date,Weekday)
  47.         SELECT Date,DATENAME(Weekday,Date)
  48.         FROM @tb
  49.         
  50.     RETURN
  51. END
  52. GO


  55. /*====================================================================*/

  57. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getdate]') and xtype in (N'FN', N'IF', N'TF'))
  58. drop function [dbo].[f_getdate]
  59. GO

  61. /*--生成列表

  63.     生成指定日期段的日期列表

  65. --邹建 2005.03(引用请保留此信息)--*/

  67. /*--调用示例

  69.     --查询工作日
  70.     SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',0)
  71.    
  72.     --查询休息日
  73.     SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',1)
  74.    
  75.     --查询全部日期
  76.     SELECT * FROM dbo.f_getdate('2005-1-3','2005-4-5',NULL)
  77. --*/

  79. CREATE FUNCTION dbo.f_getdate(
  80. @begin_date Datetime,  --要查询的开始日期
  81. @end_date Datetime,    --要查询的结束日期
  82. @bz bit                --@bz=0 查询工作日,@bz=1 查询休息日,@bz IS NULL 查询全部日期
  83. )RETURNS @re TABLE(id int identity(1,1),Date datetime,Weekday nvarchar(3))
  84. AS
  85. BEGIN
  86.     DECLARE @tb TABLE(ID int IDENTITY(0,1),a bit)
  87.     INSERT INTO @tb(a) SELECT TOP 366 0
  88.     FROM sysobjects a ,sysobjects b
  89.    
  90.     IF @bz=0
  91.         WHILE @begin_date<=@end_date
  92.         BEGIN
  93.             INSERT INTO @re(Date,Weekday)
  94.             SELECT Date,DATENAME(Weekday,Date)
  95.             FROM(
  96.                 SELECT Date=DATEADD(Day,ID,@begin_date)
  97.                 FROM @tb               
  98.             )a WHERE Date<=@end_date
  99.                 AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 BETWEEN 1 AND 5
  100.             SET @begin_date=DATEADD(Day,366,@begin_date)
  101.         END
  102.     ELSE IF @bz=1
  103.         WHILE @begin_date<=@end_date
  104.         BEGIN
  105.             INSERT INTO @re(Date,Weekday)
  106.             SELECT Date,DATENAME(Weekday,Date)
  107.             FROM(
  108.                 SELECT Date=DATEADD(Day,ID,@begin_date)
  109.                 FROM @tb               
  110.             )a WHERE Date<=@end_date
  111.                 AND (DATEPART(Weekday,Date)+@@DATEFIRST-1)%7 in(0,6)
  112.             SET @begin_date=DATEADD(Day,366,@begin_date)
  113.         END
  114.     ELSE
  115.         WHILE @begin_date<=@end_date
  116.         BEGIN
  117.             INSERT INTO @re(Date,Weekday)
  118.             SELECT Date,DATENAME(Weekday,Date)
  119.             FROM(
  120.                 SELECT Date=DATEADD(Day,ID,@begin_date)
  121.                 FROM @tb               
  122.             )a WHERE Date<=@end_date
  123.             SET @begin_date=DATEADD(Day,366,@begin_date)
  124.         END

  126.     RETURN
  127. END

复制代码
回复 支持 反对

使用道具 举报

andymark
4#
 楼主| 发表于 2011-11-17 23:25:23 | 只看该作者
10.复杂年月处理.sql
  1. SQL code --定义基本数字表
  2. declare @T1 table(代码 int,名称 varchar(10),参加时间 datetime,终止时间 datetime)
  3. insert into @T1
  4.     select 12,'单位1','2003/04/01','2004/05/01'
  5.     union all select 22,'单位2','2001/02/01','2003/02/01'
  6.     union all select 42,'单位3','2000/04/01','2003/05/01'
  7.     union all select 25,'单位5','2003/04/01','2003/05/01'

  9. --定义年表
  10. declare @NB table(代码 int,名称 varchar(10),年份 int)
  11. insert into @NB
  12.     select 12,'单位1',2003
  13.     union all select 12,'单位1',2004
  14.     union all select 22,'单位2',2001
  15.     union all select 22,'单位2',2002
  16.     union all select 22,'单位2',2003

  18. --定义月表
  19. declare @YB table(代码 int,名称 varchar(10),年份 int,月份 varchar(2))
  20. insert into @YB
  21.     select 12,'单位1',2003,'04'
  22.     union all select 22,'单位2',2001,'01'
  23.     union all select 22,'单位2',2001,'12'

  25. --为年表+月表数据处理准备临时表
  26. select top 8246 y=identity(int,1753,1)
  27. into #tby from
  28.     (select id from syscolumns) a,
  29.     (select id from syscolumns) b,
  30.     (select id from syscolumns) c

  32. --为月表数据处理准备临时表
  33. select top 12 m=identity(int,1,1)
  34. into #tbm from syscolumns

  36. /*--数据处理--*/
  37. --年表数据处理
  38. select a.*
  39. from(
  40. select a.代码,a.名称,年份=b.y
  41. from @T1 a,#tby b
  42. where b.y between year(参加时间) and year(终止时间)
  43. ) a left join @NB b on a.代码=b.代码 and a.年份=b.年份
  44. where b.代码 is null

  46. --月表数据处理
  47. select a.*
  48. from(
  49. select a.代码,a.名称,年份=b.y,月份=right('00'+cast(c.m as varchar),2)
  50. from @T1 a,#tby b,#tbm c
  51. where b.y*100+c.m between convert(varchar(6),参加时间,112)
  52.     and convert(varchar(6),终止时间,112)
  53. ) a left join @YB b on a.代码=b.代码 and a.年份=b.年份 and a.月份=b.月份
  54. where b.代码 is null
  55. order by a.代码,a.名称,a.年份,a.月份

  57. --删除数据处理临时表
  58. drop table #tby,#tbm
复制代码
11.交叉表.sql
  1. SQL code --示例

  3. --示例数据
  4. create table tb(ID int,Time datetime)
  5. insert tb select 1,'2005/01/24 16:20'
  6. union all select 2,'2005/01/23 22:45'
  7. union all select 3,'2005/01/23 0:30'
  8. union all select 4,'2005/01/21 4:28'
  9. union all select 5,'2005/01/20 13:22'
  10. union all select 6,'2005/01/19 20:30'
  11. union all select 7,'2005/01/19 18:23'
  12. union all select 8,'2005/01/18 9:14'
  13. union all select 9,'2005/01/18 18:04'
  14. go

  16. --查询处理：
  17. select     case when grouping(b.Time)=1 then 'Total' else b.Time end,
  18.     [Mon]=sum(case a.week when 1 then 1 else 0 end),
  19.     [Tue]=sum(case a.week when 2 then 1 else 0 end),
  20.     [Wed]=sum(case a.week when 3 then 1 else 0 end),
  21.     [Thu]=sum(case a.week when 4 then 1 else 0 end),
  22.     [Fri]=sum(case a.week when 5 then 1 else 0 end),
  23.     [Sat]=sum(case a.week when 6 then 1 else 0 end),
  24.     [Sun]=sum(case a.week when 0 then 1 else 0 end),
  25.     [Total]=count(a.week)
  26. from(
  27.     select Time=convert(char(5),dateadd(hour,-1,Time),108)
  28.             --时间交界点是1am,所以减1小时，避免进行跨天处理
  29.         ,week=(@@datefirst+datepart(weekday,Time)-1)%7
  30.             --考虑@@datefirst对datepart的影响
  31.     from tb
  32. )a right join(
  33.     select id=1,a='16:00',b='19:59',Time='[5pm - 9pm)' union all
  34.     select id=2,a='20:00',b='23:59',Time='[9pm - 1am)' union all
  35.     select id=3,a='00:00',b='02:59',Time='[1am - 4am)' union all
  36.     select id=4,a='03:00',b='07:29',Time='[4am - 8:30am)' union all
  37.     select id=5,a='07:30',b='11:59',Time='[8:30am - 1pm)' union all
  38.     select id=6,a='12:00',b='15:59',Time='[1pm - 5pm)'
  39. )b on a.Time>=b.a and a.Time<b.b
  40. group by b.id,b.Time with rollup
  41. having grouping(b.Time)=0 or grouping(b.id)=1
  42. go

  44. --删除测试
  45. drop table tb

  47. /*--测试结果

  49.                Mon   Tue   Wed   Thu   Fri   Sat   Sun   Total
  50. -------------- ----- ----- ----- ----- ----- ------ ---- -------
  51. [5pm - 9pm)    0     1     2     0     0     0     0     3
  52. [9pm - 1am)    0     0     0     0     0     0     2     2
  53. [1am - 4am)    0     0     0     0     0     0     0     0
  54. [4am - 8:30am) 0     0     0     0     1     0     0     1
  55. [8:30am - 1pm) 0     1     0     0     0     0     0     1
  56. [1pm - 5pm)    1     0     0     1     0     0     0     2
  57. Total          1     2     2     1     1     0     2     9

  59. （所影响的行数为 7 行）
  60. --*/
复制代码
12.任意两个时间之间的星期几的次数-横.sql
  1. SQL code if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_weekdaycount]') and xtype in (N'FN', N'IF', N'TF'))
  2. drop function [dbo].[f_weekdaycount]
  3. GO

  5. /*--计算任意两个时间之间的星期几的次数(横向显示)

  7.     本方法直接判断 @@datefirst 做对应处理
  8.     不受 sp_language 及 set datefirst 的影响     

  10. --邹建 2004.08(引用请保留此信息)--*/

  12. /*--调用示例
  13.    
  14.     select * from f_weekdaycount('2004-9-01','2004-9-02')
  15. --*/
  16. create function f_weekdaycount(
  17. @dt_begin datetime,
  18. @dt_end datetime
  19. )returns table
  20. as
  21. return(
  22.     select 跨周数
  23.         ,周一=case a
  24.             when -1 then case when 1 between b and c then 1 else 0 end
  25.             when  0 then case when b<=1 then 1 else 0 end
  26.                     +case when c>=1 then 1 else 0 end
  27.             else a+case when b<=1 then 1 else 0 end
  28.                 +case when c>=1 then 1 else 0 end
  29.             end
  30.         ,周二=case a
  31.             when -1 then case when 2 between b and c then 1 else 0 end
  32.             when  0 then case when b<=2 then 1 else 0 end
  33.                     +case when c>=2 then 1 else 0 end
  34.             else a+case when b<=2 then 1 else 0 end
  35.                 +case when c>=2 then 1 else 0 end
  36.             end
  37.         ,周三=case a
  38.             when -1 then case when 3 between b and c then 1 else 0 end
  39.             when  0 then case when b<=3 then 1 else 0 end
  40.                     +case when c>=3 then 1 else 0 end
  41.             else a+case when b<=3 then 1 else 0 end
  42.                 +case when c>=3 then 1 else 0 end
  43.             end
  44.         ,周四=case a
  45.             when -1 then case when 4 between b and c then 1 else 0 end
  46.             when  0 then case when b<=4 then 1 else 0 end
  47.                     +case when c>=4 then 1 else 0 end
  48.             else a+case when b<=4 then 1 else 0 end
  49.                 +case when c>=4 then 1 else 0 end
  50.             end
  51.         ,周五=case a
  52.             when -1 then case when 5 between b and c then 1 else 0 end
  53.             when  0 then case when b<=5 then 1 else 0 end
  54.                     +case when c>=5 then 1 else 0 end
  55.             else a+case when b<=5 then 1 else 0 end
  56.                 +case when c>=5 then 1 else 0 end
  57.             end
  58.         ,周六=case a
  59.             when -1 then case when 6 between b and c then 1 else 0 end
  60.             when  0 then case when b<=6 then 1 else 0 end
  61.                     +case when c>=6 then 1 else 0 end
  62.             else a+case when b<=6 then 1 else 0 end
  63.                 +case when c>=6 then 1 else 0 end
  64.             end
  65.         ,周日=case a
  66.             when -1 then case when 0 between b and c then 1 else 0 end
  67.             when  0 then case when b<=0 then 1 else 0 end
  68.                     +case when c>=0 then 1 else 0 end
  69.             else a+case when b<=0 then 1 else 0 end
  70.                 +case when c>=0 then 1 else 0 end
  71.             end
  72.     from(
  73.         select 跨周数=case when @dt_begin<@dt_end
  74.                 then (datediff(day,@dt_begin,@dt_end)+7)/7
  75.                 else (datediff(day,@dt_end,@dt_begin)+7)/7 end
  76.             ,a=case when @dt_begin<@dt_end
  77.                 then datediff(week,@dt_begin,@dt_end)-1
  78.                 else datediff(week,@dt_end,@dt_begin)-1 end
  79.             ,b=case when @dt_begin<@dt_end
  80.                 then (@@datefirst+datepart(weekday,@dt_begin)-1)%7
  81.                 else (@@datefirst+datepart(weekday,@dt_end)-1)%7 end
  82.             ,c=case when @dt_begin<@dt_end
  83.                 then (@@datefirst+datepart(weekday,@dt_end)-1)%7
  84.                 else (@@datefirst+datepart(weekday,@dt_begin)-1)%7 end)a
  85. )
  86. go

复制代码
回复 支持 反对

使用道具 举报

andymark
5#
 楼主| 发表于 2011-11-17 23:28:41 | 只看该作者
13.统计--交叉表+日期+优先.sql
  1. SQL code --交叉表,根据优先级取数据,日期处理

  3. create table tb(qid int,rid nvarchar(4),tagname nvarchar(10),starttime smalldatetime,endtime smalldatetime,startweekday int,endweekday int,startdate smalldatetime,enddate smalldatetime,d int)
  4. insert tb select 1,'A1','未订','08:00','09:00',1   ,5   ,null       ,null       ,1
  5. union all select 1,'A1','未订','09:00','10:00',1   ,5   ,null       ,null       ,1
  6. union all select 1,'A1','未订','10:00','11:00',1   ,5   ,null       ,null       ,1
  7. union all select 1,'A1','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2
  8. --union all select 1,'A1','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
  9. union all select 1,'A1','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
  10. union all select 1,'A2','未订','08:00','09:00',1   ,5   ,null       ,null       ,1
  11. union all select 1,'A2','未订','09:00','10:00',1   ,5   ,null       ,null       ,1
  12. union all select 1,'A2','未订','10:00','11:00',1   ,5   ,null       ,null       ,1
  13. --union all select 1,'A2','装修','08:00','09:00',null,null,'2005-1-18','2005-1-19',2
  14. union all select 1,'A2','装修','09:00','10:00',null,null,'2005-1-18','2005-1-19',2
  15. --union all select 1,'A2','装修','10:00','11:00',null,null,'2005-1-18','2005-1-19',2
  16. go

  18. /*--楼主这个问题要考虑几个方面

  20.     1. 取星期时,set datefirst 的影响
  21.     2. 优先级问题
  22.     3. qid,rid 应该是未知的(动态变化的)
  23. --*/

  25. --实现的存储过程如下
  26. create proc p_qry
  27. @date smalldatetime --要查询的日期
  28. as
  29. set nocount on
  30. declare @week int,@s nvarchar(4000)
  31. --格式化日期和得到星期
  32. select @date=convert(char(10),@date,120)
  33.     ,@week=(@@datefirst+datepart(weekday,@date)-1)%7
  34.     ,@s=''
  35. select id=identity(int),* into #t
  36. from(
  37.     select top 100 percent
  38.         qid,rid,tagname,
  39.         starttime=convert(char(5),starttime,108),
  40.         endtime=convert(char(5),endtime,108)
  41.     from tb
  42.     where (@week between startweekday and endweekday)
  43.         or(@date between startdate and enddate)
  44.     order by qid,rid,starttime,d desc)a

  46. select @s=@s+N',['+rtrim(rid)
  47.     +N']=max(case when qid='+rtrim(qid)
  48.     +N' and rid=N'''+rtrim(rid)
  49.     +N''' then tagname else N'''' end)'
  50. from #t group by qid,rid
  51. exec('
  52. select starttime,endtime'+@s+'
  53. from #t a
  54. where not exists(
  55.     select * from #t
  56.     where qid=a.qid and rid=a.rid
  57.         and starttime=a.starttime
  58.         and endtime=a.endtime
  59.         and id<a.id)
  60. group by starttime,endtime')
  61. go

  63. --调用
  64. exec p_qry '2005-1-17'
  65. exec p_qry '2005-1-18'
  66. go

  68. --删除测试
  69. drop table tb
  70. drop proc p_qry

  72. /*--测试结果

  74. starttime endtime A1         A2         
  75. --------- ------- ---------- ----------
  76. 08:00     09:00   未订         未订
  77. 09:00     10:00   未订         未订
  78. 10:00     11:00   未订         未订

  80. starttime endtime A1         A2         
  81. --------- ------- ---------- ----------
  82. 08:00     09:00   装修         未订
  83. 09:00     10:00   未订         装修
  84. 10:00     11:00   装修         未订
  85. --*/
复制代码
14.工作日处理函数(标准节假日).sql
  1. SQL code if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
  2. drop function [dbo].[f_WorkDay]
  3. GO

  5. --计算两个日期相差的工作天数
  6. CREATE FUNCTION f_WorkDay(
  7. @dt_begin datetime,  --计算的开始日期
  8. @dt_end  datetime    --计算的结束日期
  9. )RETURNS int
  10. AS
  11. BEGIN
  12.     DECLARE @workday int,@i int,@bz bit,@dt datetime
  13.     IF @dt_begin>@dt_end
  14.         SELECT @bz=1,@dt=@dt_begin,@dt_begin=@dt_end,@dt_end=@dt
  15.     ELSE
  16.         SET @bz=0
  17.     SELECT @i=DATEDIFF(Day,@dt_begin,@dt_end)+1,
  18.         @workday=@i/7*5,
  19.         @dt_begin=DATEADD(Day,@i/7*7,@dt_begin)
  20.     WHILE @dt_begin<=@dt_end
  21.     BEGIN
  22.         SELECT @workday=CASE
  23.             WHEN (@@DATEFIRST+DATEPART(Weekday,@dt_begin)-1)%7 BETWEEN 1 AND 5
  24.             THEN @workday+1 ELSE @workday END,
  25.             @dt_begin=@dt_begin+1
  26.     END
  27.     RETURN(CASE WHEN @bz=1 THEN -@workday ELSE @workday END)
  28. END
  29. GO



  33. /*=================================================================*/

  35. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
  36. drop function [dbo].[f_WorkDayADD]
  37. GO

  39. --在指定日期上,增加指定工作天数后的日期
  40. CREATE FUNCTION f_WorkDayADD(
  41. @date    datetime,  --基础日期
  42. @workday int       --要增加的工作日数
  43. )RETURNS datetime
  44. AS
  45. BEGIN
  46.     DECLARE @bz int
  47.     --增加整周的天数
  48.     SELECT @bz=CASE WHEN @workday<0 THEN -1 ELSE 1 END
  49.         ,@date=DATEADD(Week,@workday/5,@date)
  50.         ,@workday=@workday%5
  51.     --增加不是整周的工作天数
  52.     WHILE @workday<>0
  53.         SELECT @date=DATEADD(Day,@bz,@date),
  54.             @workday=CASE WHEN (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 BETWEEN 1 AND 5
  55.                 THEN @workday-@bz ELSE @workday END
  56.     --避免处理后的日期停留在非工作日上
  57.     WHILE (@@DATEFIRST+DATEPART(Weekday,@date)-1)%7 in(0,6)
  58.         SET @date=DATEADD(Day,@bz,@date)
  59.     RETURN(@date)
  60. END



复制代码
15.工作日处理函数(自定义节假日).sql
  1. SQL code if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  2. drop table [tb_Holiday]
  3. GO

  5. --定义节假日表
  6. CREATE TABLE tb_Holiday(
  7. HDate smalldatetime primary key clustered, --节假日期
  8. Name nvarchar(50) not null)             --假日名称
  9. GO

  11. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))
  12. drop function [dbo].[f_WorkDay]
  13. GO

  15. --计算两个日期之间的工作天数
  16. CREATE FUNCTION f_WorkDay(
  17. @dt_begin datetime,  --计算的开始日期
  18. @dt_end  datetime   --计算的结束日期
  19. )RETURNS int
  20. AS
  21. BEGIN
  22.     IF @dt_begin>@dt_end
  23.         RETURN(DATEDIFF(Day,@dt_begin,@dt_end)
  24.             +1-(
  25.                 SELECT COUNT(*) FROM tb_Holiday
  26.                 WHERE HDate BETWEEN @dt_begin AND @dt_end))
  27.     RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)
  28.         +1-(
  29.             SELECT COUNT(*) FROM tb_Holiday
  30.             WHERE HDate BETWEEN @dt_end AND @dt_begin)))
  31. END
  32. GO

  34. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))
  35. drop function [dbo].[f_WorkDayADD]
  36. GO

  38. --在指定日期上增加工作天数
  39. CREATE FUNCTION f_WorkDayADD(
  40. @date    datetime,  --基础日期
  41. @workday int       --要增加的工作日数
  42. )RETURNS datetime
  43. AS
  44. BEGIN
  45.     IF @workday>0
  46.         WHILE @workday>0
  47.             SELECT @date=@date+@workday,@workday=count(*)
  48.             FROM tb_Holiday
  49.             WHERE HDate BETWEEN @date AND @date+@workday
  50.     ELSE
  51.         WHILE @workday<0
  52.             SELECT @date=@date+@workday,@workday=-count(*)
  53.             FROM tb_Holiday
  54.             WHERE HDate BETWEEN @date AND @date+@workday
  55.     RETURN(@date)
  56. END

复制代码
回复 支持 反对

使用道具 举报

andymark
6#
 楼主| 发表于 2011-11-17 23:30:02 | 只看该作者
16.计算工作时间的函数.sql
  1. SQL code if exists (select * from dbo.sysobjects where id = object_id(N'[tb_worktime]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  2. drop table [tb_worktime]
  3. GO

  5. --定义工作时间表
  6. CREATE TABLE tb_worktime(
  7.     ID       int identity(1,1) PRIMARY KEY,            --序号
  8.     time_start smalldatetime,                            --工作的开始时间
  9.     time_end  smalldatetime,                           --工作的结束时间
  10.     worktime  AS DATEDIFF(Minute,time_start,time_end)  --工作时数(分钟)
  11. )
  12. GO

  14. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkTime]') and xtype in (N'FN', N'IF', N'TF'))
  15. drop function [dbo].[f_WorkTime]
  16. GO

  18. --计算两个日期之间的工作时间
  19. CREATE FUNCTION f_WorkTime(
  20. @date_begin datetime,  --计算的开始时间
  21. @date_end datetime     --计算的结束时间
  22. )RETURNS int
  23. AS
  24. BEGIN
  25.     DECLARE @worktime int
  26.     IF DATEDIFF(Day,@date_begin,@date_end)=0
  27.         SELECT @worktime=SUM(DATEDIFF(Minute,
  28.             CASE WHEN CONVERT(VARCHAR,@date_begin,108)>time_start
  29.                 THEN CONVERT(VARCHAR,@date_begin,108)
  30.                 ELSE time_start END,
  31.             CASE WHEN CONVERT(VARCHAR,@date_end,108)<time_end
  32.                 THEN CONVERT(VARCHAR,@date_end,108)
  33.                 ELSE time_end END))
  34.         FROM tb_worktime
  35.         WHERE time_end>CONVERT(VARCHAR,@date_begin,108)
  36.             AND time_start<CONVERT(VARCHAR,@date_end,108)
  37.     ELSE
  38.         SET @worktime
  39.             =(SELECT SUM(CASE
  40.                     WHEN CONVERT(VARCHAR,@date_begin,108)>time_start
  41.                     THEN DATEDIFF(Minute,CONVERT(VARCHAR,@date_begin,108),time_end)
  42.                     ELSE worktime END)
  43.                 FROM tb_worktime
  44.                 WHERE time_end>CONVERT(VARCHAR,@date_begin,108))
  45.             +(SELECT SUM(CASE
  46.                     WHEN CONVERT(VARCHAR,@date_end,108)<time_end
  47.                     THEN DATEDIFF(Minute,time_start,CONVERT(VARCHAR,@date_end,108))
  48.                     ELSE worktime END)
  49.                 FROM tb_worktime
  50.                 WHERE time_start<CONVERT(VARCHAR,@date_end,108))
  51.             +CASE
  52.                 WHEN DATEDIFF(Day,@date_begin,@date_end)>1
  53.                 THEN (DATEDIFF(Day,@date_begin,@date_end)-1)
  54.                     *(SELECT SUM(worktime) FROM tb_worktime)
  55.                 ELSE 0 END
  56.     RETURN(@worktime)
  57. END

复制代码
回复 支持 反对

使用道具 举报

zhuyiwen

点击这里给我发消息
7#
发表于 2011-11-18 21:16:46 | 只看该作者
赞一个！
回复 支持 反对

使用道具 举报

yodong
8#
发表于 2011-11-22 14:54:31 | 只看该作者
哗.好东西.
回复 支持 反对

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|站长邮箱|小黑屋|手机版|Office中国/Access中国 ( 粤ICP备10043721号-1 )  

GMT+8, 2024-4-29 20:11 , Processed in 0.114978 second(s), 31 queries .

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表