Displaying varchar as time

Question

Post reply

Displaying varchar as time

malavika.ramanathan

SSCrazy

Points: 2790
More actions
April 23, 2010 at 3:17 am

#156900

Hi All,
I have a DURATION column in one of my tables which is in nvarchar.
that column shows the duration alloted to a praticular task.
The data we get is in the form 10,20, 60 etc.
I want to store as 00:10 if it is 10 mins....
if it is 60 it must be 01:00.
How to do this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18843 More actions · Answer 1

-- *** Test Data ***

DECLARE @t TABLE

(

minutes varchar(3) NOT NULL

)

INSERT INTO @t

SELECT '10'

UNION ALL SELECT '20'

UNION ALL SELECT '60'

-- *** End Test Data ***

SELECT REPLACE(STR(minutes/60, 2), ' ', '0')

+ ':'

+ REPLACE(STR(minutes%60, 2), ' ', '0') AS Duration

FROM @t

malavika.ramanathan SSCrazy Points: 2790 More actions · Answer 2

The client DB has that column in INT.

We must store as NVARCHAR.

So, the INT value 10 should be stored as NVARCHAR "00:10"

WayneS SSC Guru Points: 95460 More actions · Answer 3

malavika.ramanathan (4/23/2010)
The client DB has that column in INT.
We must store as NVARCHAR.
So, the INT value 10 should be stored as NVARCHAR "00:10"

Taking Ken's fine code, changing the source to be an INT, and the result to be a NVARCHAR, we end up with:-- *** Test Data ***

DECLARE @t TABLE

(

minutes int NOT NULL

)

INSERT INTO @t

SELECT 10

UNION ALL SELECT 20

UNION ALL SELECT 60

-- *** End Test Data ***

SELECT CONVERT(NVARCHAR(5), REPLACE(STR(minutes/60, 2), ' ', '0')

+ ':'

+ REPLACE(STR(minutes%60, 2), ' ', '0')) AS Duration

FROM @t

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

malavika.ramanathan (4/23/2010)
Hi All,
I have a DURATION column in one of my tables which is in nvarchar.
that column shows the duration alloted to a praticular task.
The data we get is in the form 10,20, 60 etc.
I want to store as 00:10 if it is 10 mins....
if it is 60 it must be 01:00.
How to do this?

Will there be any durations >= 1440???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)