how to convert number to minute

Question

how to convert number to minute

MAK-1128556

SSCarpal Tunnel

Points: 4594
More actions
September 4, 2009 at 3:22 pm

#204342

I have a numeric filed and want to convert into minute and see.

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

You need two functions. One converting the numerical number into standard datetime type, then use DATEPARTfunction to get time.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

I wouldn't use DATEPART to get the time... you'd have to use it more than once depending on what the expected return would be.

Of course, we don't actually know what the numeric "field" contains nor what the format of the expected time result is, so it's all pure speculation on our part.

MAK... can you be a little bit more clear on the gazinta's?

--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)

MAK-1128556 SSCarpal Tunnel Points: 4594 More actions · Answer 3

I am a numeric filed,

column I want to change to time

0 0:00:00

900000000 0:15:00:00

1800000000 0:30:00:00

2700000000 0:45:00:00

After that I want add these into date and get date and time combine.

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

Replace the ",0" in the DATEADD with the WHOLE date you want to add the time to.

SELECT DATEADD(mi,d.NumericTime/3600000000*60,0)

FROM --This must makes the test data

(SELECT 0 AS NumericTime UNION ALL

SELECT 900000000 UNION ALL

SELECT 1800000000 UNION ALL

SELECT 2700000000) d

--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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Ummm... your original request said "minutes", but the following will give you seconds, as well.

SELECT DATEADD(ss,d.NumericTime/3600000000.0*3600,0)

FROM --This must makes the test data

(SELECT 0 AS NumericTime UNION ALL

SELECT 900000000 UNION ALL

SELECT 1800000000 UNION ALL

SELECT 2710000000) d

--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)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

And... don't use the following unless you can tolerate up to a second of error...

SELECT DATEADD(ss,d.NumericTime/1000000.0,0)

FROM --This must makes the test data

(SELECT 0 AS NumericTime UNION ALL

SELECT 900000000 UNION ALL

SELECT 1800000000 UNION ALL

SELECT 2710000000) d

--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)