August 19, 2014 at 5:07 am
Good day.
How do I convert decimal(8,0) value to a time value.
TransactionDate 12310476 should read 12h31 pm
August 19, 2014 at 5:12 am
Cast as char(4), put a colon in the middle, then cast as time.
John
August 19, 2014 at 5:30 am
The question is, what's the meaning of the last 4 characters. Yes, you could throw them away, but if they hold meaning, that's not usually a good idea. To put the suggested solution into actual code:
DECLARE @TV AS decimal(8,0) = 12310476;
SELECT CAST(LEFT(STUFF(CAST(@TV AS varchar(9)), 3, 0, ':'), 5) AS time) AS TIME_VALUE
OR MAYBE ...
DECLARE @TV AS decimal(8,0) = 12310476;
SELECT CAST(STUFF(STUFF(STUFF(CAST(@TV AS varchar(9)), 3, 0, ':'), 6, 0, ':'), 9, 0, '.') AS time) AS TIME_VALUE
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 19, 2014 at 5:33 am
Quick suggestion, skip the string manipulation, use math and then format to taste.
😎
DECLARE @TIMEDECIMAL decimal(8,0) = 12310476;
SELECT
CONVERT(VARCHAR(5),DATEADD(MINUTE,(FLOOR((@TIMEDECIMAL / 10000) / 100) * 60)
+ FLOOR((@TIMEDECIMAL / 10000) % 100),CAST('00:00' AS TIME)),108)
Result
12:31
August 19, 2014 at 5:41 am
sgmunson (8/19/2014)
The question is, what's the meaning of the last 4 characters. Yes, you could throw them away, but if they hold meaning, that's not usually a good idea. To put the suggested solution into actual code:
DECLARE @TV AS decimal(8,0) = 12310476;
SELECT CAST(LEFT(STUFF(CAST(@TV AS varchar(9)), 3, 0, ':'), 5) AS time) AS TIME_VALUE
OR MAYBE ...
DECLARE @TV AS decimal(8,0) = 12310476;
SELECT CAST(STUFF(STUFF(STUFF(CAST(@TV AS varchar(9)), 3, 0, ':'), 6, 0, ':'), 9, 0, '.') AS time) AS TIME_VALUE
This solution will not work in all cases such as one digit hour etc.
😎
DECLARE @TIMEDECIMAL decimal(8,0) = 12310476;
SELECT CONVERT(VARCHAR(5),DATEADD(MINUTE,(FLOOR((@TIMEDECIMAL / 10000) / 100) * 60) + FLOOR((@TIMEDECIMAL / 10000) % 100),CAST('00:00' AS TIME)),108)
SELECT CAST(LEFT(STUFF(CAST(@TIMEDECIMAL AS varchar(9)), 3, 0, ':'), 5) AS time) AS TIME_VALUE
SELECT CAST(STUFF(STUFF(STUFF(CAST(@TIMEDECIMAL AS varchar(9)), 3, 0, ':'), 6, 0, ':'), 9, 0, '.') AS time) AS TIME_VALUE
SET @TIMEDECIMAL = 2310476
SELECT CONVERT(VARCHAR(5),DATEADD(MINUTE,(FLOOR((@TIMEDECIMAL / 10000) / 100) * 60) + FLOOR((@TIMEDECIMAL / 10000) % 100),CAST('00:00' AS TIME)),108)
SELECT CAST(LEFT(STUFF(CAST(@TIMEDECIMAL AS varchar(9)), 3, 0, ':'), 5) AS time) AS TIME_VALUE
SELECT CAST(STUFF(STUFF(STUFF(CAST(@TIMEDECIMAL AS varchar(9)), 3, 0, ':'), 6, 0, ':'), 9, 0, '.') AS time) AS TIME_VALUE
SET @TIMEDECIMAL = 310476
SELECT CONVERT(VARCHAR(5),DATEADD(MINUTE,(FLOOR((@TIMEDECIMAL / 10000) / 100) * 60) + FLOOR((@TIMEDECIMAL / 10000) % 100),CAST('00:00' AS TIME)),108)
SELECT CAST(LEFT(STUFF(CAST(@TIMEDECIMAL AS varchar(9)), 3, 0, ':'), 5) AS time) AS TIME_VALUE
SELECT CAST(STUFF(STUFF(STUFF(CAST(@TIMEDECIMAL AS varchar(9)), 3, 0, ':'), 6, 0, ':'), 9, 0, '.') AS time) AS TIME_VALUE
SET @TIMEDECIMAL = 2000476
SELECT CONVERT(VARCHAR(5),DATEADD(MINUTE,(FLOOR((@TIMEDECIMAL / 10000) / 100) * 60) + FLOOR((@TIMEDECIMAL / 10000) % 100),CAST('00:00' AS TIME)),108)
SELECT CAST(LEFT(STUFF(CAST(@TIMEDECIMAL AS varchar(9)), 3, 0, ':'), 5) AS time) AS TIME_VALUE
SELECT CAST(STUFF(STUFF(STUFF(CAST(@TIMEDECIMAL AS varchar(9)), 3, 0, ':'), 6, 0, ':'), 9, 0, '.') AS time) AS TIME_VALUE
August 19, 2014 at 5:51 am
Eirikur makes a good point. An hour designation that doesn't justify a 2nd digit will need a leading zero.
Here are the corrected code samples:
DECLARE @TV AS decimal(8,0) = 12310476;
SELECT CAST(LEFT(STUFF(RIGHT('0' + CAST(@TV AS varchar(8)), 8), 3, 0, ':'), 5) AS time) AS TIME_VALUE
OR MAYBE ...
DECLARE @TV AS decimal(8,0) = 12310476;
SELECT CAST(STUFF(STUFF(STUFF(RIGHT('0' + CAST(@TV AS varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, '.') AS time) AS TIME_VALUE
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 19, 2014 at 8:45 am
Eirikur Eiriksson (8/19/2014)
Quick suggestion, skip the string manipulation, use math and then format to taste.😎
Normally, I'd agree you. On this occasion, however, the string manipulation appears to beat the date arithmetic for performance.
Here's my test harness:
CREATE TABLE #john (TimeDecimal decimal(8,0))
INSERT INTO #john
SELECT TOP 1000000 CAST(RTRIM(CAST(ABS(CHECKSUM(NEWID()))%24 AS varchar(2)))
+ RIGHT(RTRIM('0'+ CAST(ABS(CHECKSUM(NEWID()))%60 AS char(2))),2)
+ RIGHT(RTRIM('000'+CAST(ABS(CHECKSUM(NEWID()))%10000 AS char(4))),4) AS decimal(8,0))
FROM sys.columns a
CROSS JOIN sys.columns b
I set the statistics on:
SET STATISTICS TIME ON
SET STATISTICS IO ON
I executed the two queries ten times each:
-- String manipulation
SELECT
STUFF(LEFT(RIGHT('00' + CAST(TimeDecimal AS varchar(8)),8),4),3,0,':')
FROM
#john
-- Date arithmetic
SELECT
CONVERT(VARCHAR(5),DATEADD(MINUTE,(FLOOR((TimeDecimal / 10000) / 100) * 60)
+ FLOOR((TimeDecimal / 10000) % 100),CAST('00:00' AS TIME)),108)
FROM
#john
On each execution of each query, the IO statistics were as follows, so we can conclude there was no reading from disk to slow us down:
Scan count 1, logical reads 1731, physical reads 0
Here are the results for the string manipulation query:
CPU time = 485 ms, elapsed time = 2855 ms
CPU time = 500 ms, elapsed time = 2969 ms
CPU time = 516 ms, elapsed time = 2880 ms
CPU time = 546 ms, elapsed time = 3059 ms
CPU time = 454 ms, elapsed time = 2920 ms
CPU time = 547 ms, elapsed time = 2934 ms
CPU time = 469 ms, elapsed time = 2996 ms
CPU time = 437 ms, elapsed time = 2882 ms
CPU time = 485 ms, elapsed time = 2880 ms
CPU time = 516 ms, elapsed time = 2850 ms
And here are the results for the date arithmetic query:
CPU time = 1141 ms, elapsed time = 2989 ms
CPU time = 1156 ms, elapsed time = 3434 ms
CPU time = 1156 ms, elapsed time = 3149 ms
CPU time = 1203 ms, elapsed time = 3044 ms
CPU time = 1219 ms, elapsed time = 3167 ms
CPU time = 1156 ms, elapsed time = 3180 ms
CPU time = 1172 ms, elapsed time = 2868 ms
CPU time = 1172 ms, elapsed time = 2929 ms
CPU time = 1157 ms, elapsed time = 2897 ms
CPU time = 1234 ms, elapsed time = 3707 ms
Some details of the server:
ProductVersion11.0.3393.0
LanguageEnglish (United States)
PlatformNT x64
FileVersion2011.0110.3393.00 ((SQL11_SP1_QFE-CU).131025-1850 )
WindowsVersion6.2 (9200)
ProcessorCount8
ProcessorActiveMask ff
ProcessorType8664
PhysicalMemory16384 (17179398144)
John
August 19, 2014 at 9:19 am
And I have to wonder if your sample size is large enough. While the CPU time values for the string manipulation are less than half those of the date math, what happens when you scale up the number of executions to a million or more? I ask this because I noticed that the elapsed time values weren't all that far apart, which suggests that some other factor was at play and was statistically significant enough to nearly offset the entire difference.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 19, 2014 at 9:44 am
sgmunson (8/19/2014)
And I have to wonder if your sample size is large enough. While the CPU time values for the string manipulation are less than half those of the date math, what happens when you scale up the number of executions to a million or more? I ask this because I noticed that the elapsed time values weren't all that far apart, which suggests that some other factor was at play and was statistically significant enough to nearly offset the entire difference.
Steve
One million rows is a fairly large sample size. It's easy to change it, though. All the code is there, so feel free to play. A million executions, though - that's a lot of times to press F5!
The elapsed times are much closer together than the CPU times. Maybe someone more knowledgeable than me will speculate on why that is. For the elapsed times, the string manipulation is 7.3% faster on average, and for CPU time the slowest string manipulation is more than twice as fast as the fastest date arithmetic.
John
August 19, 2014 at 9:58 am
Yes, a million executions is a large number, but imagine trying to perform this manipulation in a data warehouse, where maybe you have billions of rows. That's why you devise a SELECT statement that repeats that basic manipulation on a million random rows and see what happens to the CPU time as well as the elapsed time. Using F5 is obviously impractical for that kind of testing. The hardest part of testing this is deriving the million rows of values that will actually successfully convert to a time value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 19, 2014 at 10:01 am
Steve
Yes, I was being facetious about F5! But if it's the number of rows you're worried about, it's very easy to modify my test harness. I'm sure the OP will do all due diligence himself before deciding which to use.
John
August 19, 2014 at 10:19 am
John Mitchell-245523 (8/19/2014)
Eirikur Eiriksson (8/19/2014)
Quick suggestion, skip the string manipulation, use math and then format to taste.😎
Normally, I'd agree you. On this occasion, however, the string manipulation appears to beat the date arithmetic for performance.
The arithmetic method is slower, give or take 40% when compared to a simple string manipulation such as this, but it returns the correct result for any TimeDecimal value and has a built in time validation by using the TIME data type. The string manipulation need to be enhanced substantially logic wise to always produce the correct results. As they say, it doesn't matter how fast your code is if it doesn't work;-)
😎
Using your test harness:
--BitBuckets
SET NOCOUNT ON;
DECLARE @TIMEBUCKET TIME;
DECLARE @CHARBUCKET VARCHAR(10);
--Look for small TimeDecimal values, these will be wrongly converted using string manipulation.
SELECT
TimeDecimal
,STUFF(LEFT(RIGHT('00' + CAST(TimeDecimal AS varchar(8)),8),4),3,0,':')
,DATEADD(MINUTE,(FLOOR((TimeDecimal / 10000) / 100) * 60) + FLOOR((TimeDecimal / 10000) % 100),CAST('00:00' AS TIME))
FROM #john
SET STATISTICS TIME ON;
SELECT
@CHARBUCKET = STUFF(LEFT(RIGHT('00' + CAST(TimeDecimal AS varchar(8)),8),4),3,0,':')
FROM #john
SET STATISTICS TIME OFF;
SET STATISTICS TIME ON;
SELECT
@TIMEBUCKET = DATEADD(MINUTE,(FLOOR((TimeDecimal / 10000) / 100) * 60) + FLOOR((TimeDecimal / 10000) % 100),CAST('00:00' AS TIME))
FROM #john
SET STATISTICS TIME OFF;
August 19, 2014 at 10:59 pm
Thx for all the feedback, its highly appreciated. My Daily,Weekly,Monthly Shift Report is running and operational.
August 20, 2014 at 8:58 am
Eirikur
Good points there. Hence, my new improved version 2. I've padded out TimeDecimal with another two zeros (that deals with the small numbers), then converted to time and back to char(5) (that checks that all times are valid). The latest date arithmetic expression that you posted runs quicker than the string manipulation, but the original one (which returns the data in the format requested by the OP) runs slower, so it's the conversion to varchar that chews up the CPU.
Strangely, if I change one of the TimeDecimal values to 25611322 (which is clearly going to give an invalid time), the date arithmetic produces the seemingly random result '02:01:00.0000000'. The string manipulation produces an error, which is what you'd expect.
Steve, using Eirikur's technique of selecting into a variable takes the cost of dragging the result set across the network out of the equation and means that the size of the gap between the elapsed times is closer to the size of the gap between the CPU times.
Here's the code (the test harness is the same as before).
SET STATISTICS TIME ON
SET STATISTICS IO ON
-- String manipulation
DECLARE @CHARBUCKET char(5);
SELECT
@CHARBUCKET = CAST(CAST(STUFF(LEFT(RIGHT('0000' + CAST(TimeDecimal AS varchar(8)),8),4),3,0,':') AS time) AS char(5))
FROM
#john;
-- Date arithmetic without conversion
DECLARE @TIMEBUCKET TIME;
SELECT
@TIMEBUCKET = DATEADD(MINUTE,(FLOOR((TimeDecimal / 10000) / 100) * 60) + FLOOR((TimeDecimal / 10000) % 100),CAST('00:00' AS TIME))
FROM
#john;
-- Date arithmetic with conversion
DECLARE @CHARTIMEBUCKET char(5)
SELECT
@CHARTIMEBUCKET = CONVERT(VARCHAR(5),DATEADD(MINUTE,(FLOOR((TimeDecimal / 10000) / 100) * 60) + FLOOR((TimeDecimal / 10000) % 100),CAST('00:00' AS TIME)),108)
FROM
#john;
And here are the timings in the same order, after ten executions of each
CPU time = 1109 ms, elapsed time = 1222 ms
CPU time = 1047 ms, elapsed time = 1197 ms
CPU time = 1094 ms, elapsed time = 1208 ms
CPU time = 1000 ms, elapsed time = 1197 ms
CPU time = 1063 ms, elapsed time = 1212 ms
CPU time = 1109 ms, elapsed time = 1303 ms
CPU time = 1172 ms, elapsed time = 1318 ms
CPU time = 1172 ms, elapsed time = 1266 ms
CPU time = 1016 ms, elapsed time = 1270 ms
CPU time = 1156 ms, elapsed time = 1278 ms
CPU time = 860 ms, elapsed time = 1032 ms
CPU time = 860 ms, elapsed time = 993 ms
CPU time = 890 ms, elapsed time = 968 ms
CPU time = 938 ms, elapsed time = 971 ms
CPU time = 875 ms, elapsed time = 1014 ms
CPU time = 953 ms, elapsed time = 1118 ms
CPU time = 891 ms, elapsed time = 1003 ms
CPU time = 828 ms, elapsed time = 1002 ms
CPU time = 797 ms, elapsed time = 967 ms
CPU time = 1187 ms, elapsed time = 1400 ms
CPU time = 1234 ms, elapsed time = 1371 ms
CPU time = 1328 ms, elapsed time = 1353 ms
CPU time = 1141 ms, elapsed time = 1348 ms
CPU time = 1312 ms, elapsed time = 1460 ms
CPU time = 1547 ms, elapsed time = 1665 ms
CPU time = 1344 ms, elapsed time = 1380 ms
CPU time = 1296 ms, elapsed time = 1356 ms
CPU time = 1344 ms, elapsed time = 1487 ms
CPU time = 1234 ms, elapsed time = 1355 ms
CPU time = 1235 ms, elapsed time = 1359 ms
John
August 20, 2014 at 9:12 am
John Mitchell-245523 (8/20/2014)
EirikurGood points there. Hence, my new improved version 2. I've padded out TimeDecimal with another two zeros (that deals with the small numbers), then converted to time and back to char(5) (that checks that all times are valid). The latest date arithmetic expression that you posted runs quicker than the string manipulation, but the original one (which returns the data in the format requested by the OP) runs slower, so it's the conversion to varchar that chews up the CPU.
Good job John, now it's only to find whether improving the conversion from time to char(5) is an option.
Strangely, if I change one of the TimeDecimal values to 25611322 (which is clearly going to give an invalid time), the date arithmetic produces the seemingly random result '02:01:00.0000000'. The string manipulation produces an error, which is what you'd expect.
The result '02:01:00.0000000' could be valid as the TimeDecimal 25611322 is 25 hours 61 minute which in a 24 hour format translates to 02:01.
😎
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply