Click here to Skip to main content
15,867,453 members
Please Sign up or sign in to vote.
2.50/5 (2 votes)
See more:
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


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

But I need with 30 mins also
Posted
Updated 3-Jul-18 21:39pm

1 solution

Here is one way:
SQL
;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


[EDIT]Here is an alternative, using your original method - the trick is to convert to using minutes in sets of 30
SQL
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
 
Share this answer
 
v2
Comments
Member 12926744 4-Jul-18 3:52am    
Thank you very much for your answer but this will give it in 24 hr format.I want it as 12 hr format with am and pm . Any help will be really appreciated.
Member 12926744 4-Jul-18 3:54am    
select distinct CONVERT(varchar(15),CAST( dateadd(minute,30 * (number-1),'01-jan-2017' ) AS TIME),100) as slots from master..spt_values where number>1 and number<=50 order by slots
Thank you I got the answer . Thank you very much for your help.
CHill60 4-Jul-18 3:56am    
You beat me to it! Sorry, I dropped the formatting when working on my solutions and forgot to put it back on

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900