27,579
社区成员
发帖
与我相关
我的任务
分享SQL 时间获取的语句
指定一个时间范围，例如“2016-06-01”至“2016-06-30”，想要生成如下的时间：
日期 小时 分钟
2016-06-01 1 0
2016-06-01 1 5
2016-06-01 1 10
。 。 。
。 。 。
2016-06-30 24 55
2016-06-30 24 60
假如生成的表为a，将a表与b表比对，查看b表中哪个时间段缺失，并且显示出来。
日期 小时 分钟
2016-06-01 1 0
2016-06-01 1 5
2016-06-01 1 10
。 。 。
。 。 。
2016-06-30 24 55
2016-06-30 24 60
假如生成的表为a，将a表与b表比对，查看b表中哪个时间段缺失，并且显示出来。
...全文
请发表友善的回复…
发表回复
weixin_40101242 2017-11-21
- 打赏
- 举报
你们真是太厉害了，赞。
二月十六 2017-11-21
- 打赏
- 举报
试试这个
DECLARE @date1 DATE= '2016-06-01'
DECLARE @date2 DATE= '2016-06-02'
SELECT *
FROM ( SELECT DATEADD(DAY, number, @date1) AS 日期 ,
t.*
FROM master.dbo.spt_values a
CROSS APPLY ( SELECT b.number AS 小时 ,
c.number * 5 AS 分钟
FROM master.dbo.spt_values b
CROSS APPLY ( SELECT
number
FROM
master.dbo.spt_values b
WHERE
type = 'P'
) c
WHERE b.number BETWEEN 1 AND 24
AND c.number BETWEEN 1 AND 12
AND b.type = 'P'
) t
WHERE a.type = 'P'
AND a.number <= DATEDIFF(DAY, @date1, @date2)
) t
WHERE NOT EXISTS ( SELECT 1
FROM b
WHERE t.日期 = b.日期
AND t.小时 = b.小时
AND t.分钟 = b.分钟 )
吉普赛的歌 2017-11-21
- 打赏
- 举报
--增加测试表及数据 开始
IF OBJECT_ID('tempdb..#a') IS NOT NULL DROP TABLE #a
IF OBJECT_ID('tempdb..#b') IS NOT NULL DROP TABLE #b
;WITH dt AS (
SELECT sv.number AS d FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 29
),ht AS(
SELECT sv.number AS h FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 23
),mt AS(
SELECT sv.number AS m FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 59
)
SELECT
DATEADD(DAY,dt.d,'2016-06-01') AS [日期]
,ht.h AS [小时]
,mt.m AS [分钟]
INTO #a
FROM dt
CROSS APPLY ht
CROSS APPLY mt
ORDER BY 1,2,3
SELECT *
INTO #b
FROM #a
WHERE [日期]!='2016-06-02'
--增加测试表及数据 开始
SELECT * FROM #a a
WHERE NOT EXISTS (
SELECT * FROM #b b WHERE a.[日期]=b.[日期] and a.[小时]=b.[小时] and a.[分钟]=b.[分钟]
)
ORDER BY 1,2,3weixin_40101242 2017-11-21
- 打赏
- 举报
没有成功啊，版主大人
二月十六 2017-11-21
- 打赏
- 举报
我写的5#的有b表比较的
weixin_40101242 2017-11-21
- 打赏
- 举报
大神们，还有和b表的比较啊。b表也是和a表一样的，但是应该会缺失某个时间段，通过a表完整的时间比对出b表缺失的时间。
二月十六 2017-11-21
- 打赏
- 举报
DECLARE @date1 DATE= '2016-06-01'
DECLARE @date2 DATE= '2016-06-02'
SELECT *
FROM ( SELECT DATEADD(DAY, number, @date1) AS 日期 ,
t.*
FROM master.dbo.spt_values a
CROSS APPLY ( SELECT b.number AS 小时 ,
c.number * 5 AS 分钟
FROM master.dbo.spt_values b
CROSS APPLY ( SELECT
number
FROM
master.dbo.spt_values b
WHERE
type = 'P'
) c
WHERE b.number BETWEEN 1 AND 24
AND c.number BETWEEN 1 AND 12
AND b.type = 'P'
) t
WHERE a.type = 'P'
AND a.number <= DATEDIFF(DAY, @date1, @date2)
ORDER BY DATEADD(DAY, number, @date1) ,
t.小时 ,
t.分钟
) t
WHERE NOT EXISTS ( SELECT 1
FROM b
WHERE t.日期 = b.日期
AND t.小时 = b.小时
AND t.分钟 = b.分钟 )二月十六 2017-11-21
- 打赏
- 举报
DECLARE @date1 DATE= '2016-06-01'
DECLARE @date2 DATE= '2016-06-02'
SELECT DATEADD(DAY, number, @date1) AS 日期 ,
t.*
FROM master.dbo.spt_values a
CROSS APPLY ( SELECT b.number AS 小时 ,
c.number * 5 AS 分钟
FROM master.dbo.spt_values b
CROSS APPLY ( SELECT number
FROM master.dbo.spt_values b
WHERE type = 'P'
) c
WHERE b.number BETWEEN 1 AND 24
AND c.number BETWEEN 1 AND 12
AND b.type = 'P'
) t
WHERE a.type = 'P'
AND a.number <= DATEDIFF(DAY, @date1, @date2)
ORDER BY DATEADD(DAY, number, @date1) ,
t.小时 ,
t.分钟
顺势而为1 2017-11-21
- 打赏
- 举报
b表是怎样的 ?
吉普赛的歌 2017-11-21
- 打赏
- 举报
;WITH dt AS (
SELECT sv.number AS d FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 29
),ht AS(
SELECT sv.number AS h FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 23
),mt AS(
SELECT sv.number AS m FROM MASTER.dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 0 AND 59
)
SELECT
DATEADD(DAY,dt.d,'2016-06-01') AS [日期]
,ht.h AS [小时]
,mt.m AS [分钟]
FROM dt
CROSS APPLY ht
CROSS APPLY mt
ORDER BY 1,2,3weixin_40101242 2017-11-21
- 打赏
- 举报
求大神帮助。
二月十六 2017-11-21
- 打赏
- 举报
存到什么表？
DECLARE @date1 DATE= '2016-06-01'
DECLARE @date2 DATE= '2016-06-02'
SELECT * INTO #ttt --这里直接into 表
FROM ( SELECT DATEADD(DAY, number, @date1) AS 日期 ,
t.*
FROM master.dbo.spt_values a
CROSS APPLY ( SELECT b.number AS 小时 ,
c.number * 5 AS 分钟
FROM master.dbo.spt_values b
CROSS APPLY ( SELECT
number
FROM
master.dbo.spt_values b
WHERE
type = 'P'
) c
WHERE b.number BETWEEN 1 AND 24
AND c.number BETWEEN 1 AND 12
AND b.type = 'P'
) t
WHERE a.type = 'P'
AND a.number <= DATEDIFF(DAY, @date1, @date2)
) t
WHERE NOT EXISTS ( SELECT 1
FROM b
WHERE t.日期 = b.日期
AND t.小时 = b.小时
AND t.分钟 = b.分钟 )