June 8, 2011 at 2:09 am
Hiya,
I have a column with datatype numeric(19,4) which can have negative numbers. These are stored in H.M format so -0.5700 would represent -00:57:00 in time equivalent.
Is it possible to convert the numeric datatype into something which looks like time?
Thanks
June 8, 2011 at 4:27 am
See if this helps:
-- Source table with some sample values
DECLARE @source TABLE (
id int, -- to identify the converted values in the destination table
decCol decimal(19,4)
)
-- some sample values
INSERT INTO @source VALUES(1, -0.5700)
INSERT INTO @source VALUES(2, -3.5713)
INSERT INTO @source VALUES(3, 0.5700)
INSERT INTO @source VALUES(4, 3.5713)
-- destination table
DECLARE @destination TABLE (
id int,
timeCol time
)
-- CTE with dateparts from the numeric value
;WITH DateParts AS (
SELECT id,
hours = CAST(decCol AS int),
minutes = (decCol - CAST(decCol AS int)) * 100,
seconds = (decCol ) * 10000 -
CAST(((decCol - CAST(decCol AS int)) * 100) AS int) * 100 -
CAST(decCol AS int) * 10000
FROM @source
)
INSERT INTO @destination
SELECT id,
DATEADD(second, seconds,
DATEADD(minute, minutes,
DATEADD(hour, hours, 0)
)
)
FROM DateParts
-- select out converted values
SELECT * FROM @destination
-- Gianluca Sartori
June 8, 2011 at 5:51 am
Not quite. It converted my numeric value from -0.5700 to 23:03:00.
June 8, 2011 at 5:58 am
lanky_doodle (6/8/2011)
Not quite. It converted my numeric value from -0.5700 to 23:03:00.
That's what -00:57:00 looks to me.
What does a negative time mean to you?
-- Gianluca Sartori
June 8, 2011 at 9:03 am
D'oh. Of course. -0.5700 is 57 minutes short of 0.00 (midnight), which is where 23:03:00 comes from.
But that is not what I need. The value in the numeric column is basically the time they owe (negative) or are owed (positive). So my -0.5700 should be displayed as -00:57:00, not 0.00 minus 00:57:00 = 23:03:00
If someone is owed time, say 1.5800, that should be displayed as 01:58:00.
Make sense?
Thanks
June 8, 2011 at 9:17 am
I think it's just a display issue, then.
Try this:
-- Source table with some sample values
DECLARE @source TABLE (
id int, -- to identify the converted values in the destination table
decCol decimal(19,4)
)
-- some sample values
INSERT INTO @source VALUES(1, -0.5700)
INSERT INTO @source VALUES(2, -3.5713)
INSERT INTO @source VALUES(3, 0.5700)
INSERT INTO @source VALUES(4, 3.5713)
-- CTE with dateparts from the numeric value
;WITH DateParts AS (
SELECT id,
SGN = SIGN(decCol),
hours = ABS(CAST(decCol AS int)),
minutes = ABS((decCol - CAST(decCol AS int)) * 100),
seconds = ABS((decCol ) * 10000 -
CAST(((decCol - CAST(decCol AS int)) * 100) AS int) * 100 -
CAST(decCol AS int) * 10000)
FROM @source
)
SELECT CASE SGN WHEN -1 THEN '-' ELSE '' END +
REPLACE(STR(hours,2,0),' ','0') + ':' +
REPLACE(STR(minutes,2,0),' ','0') + ':' +
REPLACE(STR(seconds,2,0),' ','0')
FROM DateParts
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply