如何在SQL中以30分钟的间隔获取一天中的所有时间 [英] How to get all times in a day with 30 mins interval in SQL

问题描述

大家好，

我需要以12小时的格式获得一天中的所有时间，间隔为30分钟，即我需要输出为



o / p

Hi all,
I need to get all times in a day in 12 hr format with 30 mins interval ie , I need to get the output as

o/p

1:00AM
1:30AM
1:00PM
1:30PM
10:00AM
10:30Am
10:00PM
10:30PM
11:00AM
11:00PM
11:30AM
11:30PM
12:00PM
12:30PM
2:00AM
2:00PM
2:30AM
2:30PM

依此类推



我的尝试：

and so on

What I have tried:

select distinct CONVERT(varchar(15),CAST( dateadd(hour,number-1,'01-jan-2017' ) AS TIME),100) as slots from master..spt_values where number>1 and number<=24 order by slots

这使得o / p为：

This gives the o/p as :

1:00AM
1:00PM
10:00AM
10:00PM
11:00AM
11:00PM
12:00PM
2:00AM
2:00PM
3:00AM
3:00PM
4:00AM
4:00PM
5:00AM
5:00PM
6:00AM
6:00PM
7:00AM
7:00PM
8:00AM
8:00PM
9:00AM
9:00PM

但我需要30分钟

But I need with 30 mins also

推荐答案

这是一个方式：

Here is one way:

;WITH q AS
    (
    SELECT  30 AS num
    UNION ALL
    SELECT  num + 30
    FROM    q
    WHERE num < 24 * 60
    )
SELECT  dateadd(MINUTE, num, cast('00:00:00' as time))
FROM    q

这是一个替代方案，使用您的原始方法 - 诀窍是转换为使用分钟 s套30个

Here is an alternative, using your original method - the trick is to convert to using minutes in sets of 30

select distinct CAST( dateadd(minute,30 * (number-1),'01-jan-2017' ) AS TIME) as slots from master..spt_values where number>1 and number<=50 order by slots

这篇关于如何在SQL中以30分钟的间隔获取一天中的所有时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！