Semi Large block of script used to format multiple columns

Hi all,

I have a need to reuse a block of script to format 7 columns of datetimes. The block of script is:

 case when substring(convert(varchar,[Sunday Start Time],108),4,2)>=0 and substring(convert(varchar,  [Sunday Start Time],108),4,2)<=7
							then left(convert(varchar, [Sunday Start Time],108),2)+':00'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>7 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=15
							then left(convert(varchar, [Sunday Start Time],108),2)+':15'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>15 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=22
							then left(convert(varchar, [Sunday Start Time],108),2)+':15'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>22 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=30
							then left(convert(varchar, [Sunday Start Time],108),2)+':30'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>30 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=37
							then left(convert(varchar, [Sunday Start Time],108),2)+':30'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>37 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=45
							then left(convert(varchar, [Sunday Start Time],108),2)+':45'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>45 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=52
							then left(convert(varchar, [Sunday Start Time],108),2)+':45'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>52 
							then left(convert(varchar, dateadd(hh,1,[Sunday Start Time]),108),2)+':00'
															 end SUN

Open in new window


it rounds the time with in the datetime to the nearest 15 minute interval.

I'd like to make my script more presentable by perhaps using a function and calling the function to format the columns as I need it to.

Is using a function the right approach? Is there another way to do what I need without just copying and pasting the script 7 times?
LVL 1
susnewyorkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris LuttrellSenior Database ArchitectCommented:
You posted in SQL Server 2008 so I assume that is your platform.
Don't forget you have the TIME datatype to work with, I think this function is much simpler than all those string conversions and the case statement and it seems to work when I tested it out compared to your code snippet.
CREATE FUNCTION dbo.udf_Time15(@Time DATETIME)
RETURNS VARCHAR(5)
BEGIN
	RETURN CONVERT(VARCHAR(10),CONVERT (TIME(0), DATEADD(MINUTE, 15 * ROUND(DATEPART(Minute, @Time) / 15.0,0), DATEADD(HOUR, DATEPART(Hour, @Time), 0))))
END;
GO

Open in new window

Then tested it with this
--CREATE TABLE SomeTable ([Sunday Start Time] DATETIME);
--Inserted some data
SELECT dbo.SomeTable.[Sunday Start Time], 
		dbo.udf_Time15(dbo.SomeTable.[Sunday Start Time]) MyFn,
		case when substring(convert(varchar,[Sunday Start Time],108),4,2)>=0 and substring(convert(varchar,  [Sunday Start Time],108),4,2)<=7
							then left(convert(varchar, [Sunday Start Time],108),2)+':00'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>7 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=15
							then left(convert(varchar, [Sunday Start Time],108),2)+':15'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>15 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=22
							then left(convert(varchar, [Sunday Start Time],108),2)+':15'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>22 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=30
							then left(convert(varchar, [Sunday Start Time],108),2)+':30'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>30 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=37
							then left(convert(varchar, [Sunday Start Time],108),2)+':30'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>37 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=45
							then left(convert(varchar, [Sunday Start Time],108),2)+':45'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>45 and substring(convert(varchar, [Sunday Start Time],108),4,2)<=52
							then left(convert(varchar, [Sunday Start Time],108),2)+':45'
		when substring((convert(varchar, [Sunday Start Time],108)),4,2)>52 
							then left(convert(varchar, dateadd(hh,1,[Sunday Start Time]),108),2)+':00'
															 end SUN
FROM SomeTable;

Open in new window

Results
Sunday Start Time	MyFn	SUN
2013-10-03 17:46:32.140	17:45	17:45
2013-10-03 14:56:33.153	15:00	15:00
2013-10-03 17:27:34.020	17:30	17:30
2013-10-03 19:01:35.227	19:00	19:00
2013-10-03 22:50:38.897	22:45	22:45
2013-10-03 01:27:41.880	01:30	01:30
2013-10-03 10:18:57.440	10:15	10:15
2013-10-03 22:04:58.063	22:00	22:00
2013-10-03 12:08:14.567	12:15	12:15
2013-10-03 19:21:15.753	19:15	19:15
2013-10-03 07:32:16.463	07:30	07:30
2013-10-03 22:40:28.350	22:45	22:45
2013-10-03 08:13:59.223	08:15	08:15

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
no points please just a tiny suggestion:

CONVERT (varchar(5), DATEADD(MINUTE, 15 * ROUND(DATEPART(Minute, @Time) / 15.0,0), DATEADD(HOUR, DATEPART(Hour, @Time), 0)),8) --<< date style 8

one less function :)
date style 8 returns hh:mm:ss from a datetime
varchar(5) returns just hh:mm

just in case: are you using SQL 2012? (not available as topic choice)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.