Format a number to two places in SQL statement

I have the below in a SQL select statement.  I want to return a TimeLength column in the format of 5:08 minutes, but it returns 5:8.  How do I forma the second part to return two places so the 5:08 is reflected correctly, no as 5:8?

      (CAST(t.duration/60 As varchar(5)) +':' + CAST(t.duration%60 AS varchar(5))) As TimeLength

Sandra
Sandra SmithRetiredAsked:
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Right( 'add a zero, then' + the second value, 2) guarantees a zero-padded two character value.

(CAST(RIGHT('0' + t.duration/60,2) As varchar(5)) +':' + 
CAST(RIGHT('0' + t.duration%60,2) AS varchar(5))) As TimeLength

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<correction, you might have to play with this a little>
(
RIGHT('0' + CAST(t.duration/60 as varchar(2)),2) + ':' + 
RIGHT('0' + CAST(t.duration%60 as varchar(2)),2) ) As TimeLength

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
Sandra SmithRetiredAuthor Commented:
Let me this, I also need it to correctly display if the number is, say 3:20.  Will get back to you.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Sandra SmithRetiredAuthor Commented:
Thank you, presents the data just as I want.

Sandra
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with the formatting.  -Jim
0
Sandra SmithRetiredAuthor Commented:
Jim, I want to extend my sincere appreciation for your help.  I have been out of work for a bit and have a chance for an SQL position doing a lot of querying against the servers, something I have not done for years as I have been mostly doing ACCESS development.  Am trying to cram a lot of information in my head this weekend, so keep on the look out for more questions!  I try to figure them out myself first, but rely on EE when I am stumped.  Again, thank you.

Sandra
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the compliment Sandra.  I am also an Access convert, and once I learned that you can take an Access query, daisy-chain them in the same script, use variables throughout, use temp tables throughout, and add code comments, I was hooked.

I think the gig that got me started was an Access reporting project where the back-end was in SQL, and instead of linking tables I learned to create my own objects in SQL, and link to them.

Good luck with your skillset migration.
Jim
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 Development

From novice to tech pro — start learning today.