August 23, 2008 at 4:39 am
Duplicate post.
Replies to the following thread please:
http://www.sqlservercentral.com/Forums/Topic557734-338-1.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2008 at 5:52 am
For table msdb.dbo.sysjobhistory, since some @#*& idiot at MS has columns run_date , run_time and run_duration using the integer datatype with an internal format of YYYYMMDD or HHMMSS, I have written two user defined functions to convert to the more appropriate data types.
In your case, as SQL Server 2005 does not support a time data type, do you want to convert to a character datatype with colon separator ?
declare @hhmmss integer
set @hhmmss = 123456
select STUFF(STUFF(RIGHT('000000' + CAST ( @hhmmss as VARCHAR(6 ) ) ,6),5,0,':'),3,0,':')
For duration, you need to decide the precision needed and the below udf uses a precision of seconds. Alternatives are hours, minutes, milliseconds or nanoseconds.
create FUNCTION dbo.HHMMSS_to_Seconds
( @HHMMSSinteger
)
RETURNS integer
AS
BEGIN
RETURN(3600 * ( @HHMMSS / 10000 )
+ 60 * ( ( @HHMMSS
- ( ( @HHMMSS / 10000 ) * 10000 )
- (@HHMMSS % 100 ) ) / 100
)
+(@HHMMSS % 100 )
)
END
create FUNCTION dbo.YYMMDD_HHMMSS_to_TS
( @YYYYMMDDinteger
, @HHMMSSinteger
)
RETURNS datetime
AS
BEGIN
RETURN(( CAST (
( CAST ( @YYYYMMDD as VARCHAR(8) ) + ' '
+ STUFF(STUFF(RIGHT('000000'
+ CAST ( @HHMMSS as VARCHAR(6 ) ) ,6),5,0,':'),3,0,':')
) as datetime ))
)
END
SQL = Scarcely Qualifies as a Language
August 25, 2008 at 1:40 am
PLEASE DON'T DOUBLE POST...
I thought I replied to this earlier but my reply suddenly disappeared...
Ok, here's a simpler way... only one select statement for this...
by the way... the reason why my code is long because I included different formats that you might like, hope it helps also 😀
--13100 <----- I intenionally changed the value for testing ^__^
--220000
--230000
--500000
DECLARE @Table TABLE(TimeNumber INT)
INSERT INTO @Table
SELECT '13100'
UNION
SELECT '220000'
UNION
SELECT '230000'
UNION
SELECT '500000'
SELECT RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6)
FROM @Table
-- VARCHAR FORMAT
-- this is recomended for one that records the timer types which exceed 24 hours
-- SAMPLE #1
SELECT LEFT(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),2) + ':' + SUBSTRING(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),3,2) + ':' + RIGHT(TimeNumber,2)
FROM @Table
-- SAMPLE #2
SELECT LEFT(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),2) + ':' + SUBSTRING(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),3,2)
FROM @Table
-- DATETIME FORMAT
-- this is NOT applicable for one that records the timer types which exceed 24 hours because its in Datetime format
--SELECT CONVERT(DATETIME,CAST('11:00' AS VARCHAR(MAX)),8)
-- SAMPLE #3
SELECT CONVERT(DATETIME,CAST(LEFT(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),2) + ':' + SUBSTRING(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),3,2) + ':' + RIGHT(TimeNumber,2)AS VARCHAR(8)),14)
FROM @Table
WHERE TimeNumber<=240000
-- SAMPLE #4
SELECT CONVERT(DATETIME,CAST(LEFT(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),2) + ':' + SUBSTRING(RIGHT('00'+CAST(TimeNumber AS VARCHAR(MAX)),6),3,2)AS VARCHAR(8)),14)
FROM @Table
WHERE TimeNumber<=240000
Hope it still helps...
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 25, 2008 at 2:26 am
Hey Carl Federl nice one... your code is way better than mine... 🙂 i realy like it... forgot about that one...
Hey AShehzad ... Uhmm I have a question... what time do you want to get with this integer
707070?
71:11:10 or an error? since minutes and seconds only has 60
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
August 26, 2008 at 8:03 am
One needs to be specific when refering to "time" as this can mean either a "point in time" or a "duration of time" An example is a meeting that starts at 3:30 (a "point in time") and runs for 3:30 (a "duration of time" ). Points and Durations are two different datatypes with two different sets of rules.
A description of the ISO standard for international standard for date and time representations can be found on WikiPedia at http://en.wikipedia.org/wiki/ISO_8601
The "point in time" can have a range of "00:00:00" to "24:00:00" but the standard display format of "duration of time" is entirely different and is specified as "P#Y#M#DT#H#M#S" where # is a number. For example, "P3Y6M4DT12H30M5S" represents a duration of
"three years, six months, four days, twelve hours, thirty minutes, and five seconds".
Here is a UDF that converts from time duration in seconds to the ISO display format:
CREATE FUNCTION dbo.DurationSeconds_to_ISO
( @Duration_Secondsinteger
)
RETURNS varchar(255)
/*
ISO STANDARD 8601 is used for output format
-- http://en.wikipedia.org/wiki/ISO_8601
-- "P3Y6M4DT12H30M5S" represents a duration of
-- "three years, six months, four days, twelve hours, thirty minutes, and five seconds".
*/
AS
BEGIN
/*
Testing:
declare @Duration_Secondsinteger
set@Duration_Seconds
= ( 24 * 60 * 60) * 3 -- 3 days
+ ( 4 * 60 * 60 )-- 4 hours
+ ( 5 * 60 )-- 5 minutes
+ 6-- 6 seconds
SELECTdbo.DurationSeconds_to_ISO(@Duration_Seconds)
*/
declare@secondsinteger
,@minutesinteger
,@hoursinteger
,@daysinteger
--Seconds in a time unit
,@MinuteSecondsinteger
,@HourSecondsinteger
,@DaySecondsinteger
set@MinuteSeconds= 60
set@HourSeconds= (60 * 60 )
set@DaySeconds= (24 * 60 * 60)
set@seconds= @Duration_Seconds % @MinuteSeconds
set@days= @Duration_Seconds / @DaySeconds
set@minutes= ( ( @Duration_Seconds - @seconds ) / @MinuteSeconds ) % @MinuteSeconds
set@hours= ( @Duration_Seconds - @seconds - ( @minutes * @MinuteSeconds ) - ( @days * @DaySeconds)) / @HourSeconds
RETURN( 'P'
+ cast(@days as varchar(4)) + 'DT'
+ CAST(@hours as varchar(2)) + 'H'
+ CAST(@minutes as varchar(2)) + 'M'
+ CAST(@secondsas varchar(2)) + 'S'
)
end
SQL = Scarcely Qualifies as a Language
August 28, 2008 at 1:59 am
:D:P:D:P:D:P
LOL!
Hehehe Thanks! 🙂
We'll I hope my post helped in string manipulation hehehehe
By the way, those codes were very useful... I copied it, might be useful in future projects.
_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply