September 21, 2011 at 9:27 am
Hello all,
I have a 'TIME UPDATED' field I need to include into reporting but the value, eventhough it's a time reference, is stored as julian with a number value, i.e. 24701. I need to convert it to show as 11:53:46 but so far what I've tried hasn't worked. is there a way to convert these julian time values to conventional time formats?
Thanks,
John
September 21, 2011 at 10:53 am
What exactly do you mean by "julian time"?
I have never heard of a definition of Julian Date (or time) that would convert 24701 to 11:53:46.
Without know (or guessing) what unit of time 24701 represents there would be no way to convert it.
September 21, 2011 at 11:17 am
I may not have phrased it correctly, thus, I apologize. The field type is float and the value it has is 24701. What I am trying to do is display it in hh:mm:ss, but I figured it out. I'm using the code below and it works:
SUBSTRING(RIGHT('0'+CAST(sdtday AS VARCHAR(6)),6),1,2) + ':'
+ SUBSTRING(RIGHT('0'+CAST(sdtday AS VARCHAR(6)),6),3,2) + ':' +
SUBSTRING(RIGHT('0'+CAST(sdtday AS VARCHAR(6)),6),5,2)
September 21, 2011 at 12:11 pm
is the data integer data type or strings?
so the value 24701 is 2:47:01 (am?)
152205 would be a valid value and 15:22:05 or 3:22:05 pm?
something like this may be a bit faster for strings:
/*
--Results
sdtday Results
24701 2:47:01
152205 15:22:05
*/
With MySampleData
AS
(
SELECT '24701' AS sdtday UNION ALL
SELECT '152205' AS sdtday
)
SELECT sdtday,
CASE
WHEN LEN(sdtday) = 5
THEN STUFF(STUFF(sdtday,4,0,':'),2,0,':')
WHEN LEN(sdtday) = 6
THEN STUFF(STUFF(sdtday,5,0,':'),3,0,':')
END AS Results
from MySampleData
Lowell
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply