date string conversion...
-
"I want to point out that run_time is an int and when a job runs one second after midnight the value will be 1 in the column causing the sql to break."
good eye Kory!
_________________________
-
I tested the following script without any error.
SELECT DATENAME(Year, CONVERT(DATETIME,'20060318', 102))
I believe that we may have to convert the integer to string.
-
I know the original post is bulky, but it works perfect and is large due to the formating requirments. I recommend either puting this in a function or use a temp table to land the data in a datetime field and them use formating on that single column, which would make the sql perform 6X faster.
-
check out CONVERT in BOL - see if any of those formats would work for you!
-
Here's a script that installs two functions in MSDB that I wrote when I got very, very tired of working with these idiot columns. The one is used to generate a datetime value: dbo.FixJobTime(run_date, run_time)
...and the other converts RunDuration into the number of seconds: dbo.FixJobDuration(run_duration). They're all math-based, and so run pretty quickly. Enjoy!
Philip
-----------------------------------------------------
USE msdb
IF objectproperty(object_id('FixJobTime'), 'isScalarFunction') = 1
DROP FUNCTION dbo.FixJobTime
IF objectproperty(object_id('FixJobDuration'), 'isScalarFunction') = 1
DROP FUNCTION dbo.FixJobDuration
GO
/*
Version 1: Function to convert crappy msdb job dates and times to something that can be used
(in this case, a datetime value)
PHK, Mar 10 2006
*/
CREATE FUNCTION dbo.FixJobTime
(
@JobDate int
,@JobTime int = 0
)
RETURNS datetime
AS BEGIN
RETURN cast( cast((@JobDate % 10000) / 100 as varchar(10))
+ '/' + cast(@JobDate % 100 as varchar(10))
+ '/' + cast(@JobDate / 10000 as varchar(10))
+ ' ' + cast(@JobTime / 10000 as varchar(10))
+ ':' + cast((@JobTime % 10000) / 100 as varchar(10))
+ ':' + cast(@JobTime % 100 as varchar(10))
as datetime)
END
GO
/*
Version 1: Function to convert crappy msdb job durations into something that can be used
(in this case, total seconds as an integer)
PHK, Mar 10 2006
*/
CREATE FUNCTION dbo.FixJobDuration
(
@JobDuration int
)
RETURNS int
AS BEGIN
RETURN (@JobDuration/10000)*3600 + ((@JobDuration%10000)/100)*60 + (@JobDuration%100)
END
GO
-
Here is what we are currently doing to convert run_date and run_time to a datetime field:
SELECT substring(CONVERT(VARCHAR, sjh.run_date),5,2) + '/' + substring(CONVERT(VARCHAR, sjh.run_date),7,2) + '/' +
substring(CONVERT(VARCHAR, sjh.run_date),1,4) + ' ' + substring(Right('000000' + CONVERT(VARCHAR, sjh.run_time),6),1,2) +
':' + substring(Right('000000' + CONVERT(VARCHAR, sjh.run_time),6),3,2) +
':' + substring(Right('000000' + CONVERT(VARCHAR, sjh.run_time),6),5,2) as RunDateTime
FROM msdb..sysjobhistory sjh
-
I think this conversion method is a little shorter:
select run_date, run_time, [Job Run Date/Time] = -- Convert run_date and run_time to datetime dateadd(ss,((run_time/10000)*3600)+(((run_time/100)%100)*60)+(run_time%100),right(run_date,8)) from ( -- Select sample of 20 jobs select top 20 * from sysjobhistory order by newid() ) a order by 3
Results: run_date run_time Job Run Date/Time ----------- ----------- ------------------------------------------------------ 20050218 172345 2005-02-18 17:23:45.000 20060820 100000 2006-08-20 10:00:00.000 20061105 200 2006-11-05 00:02:00.000 20061119 13000 2006-11-19 01:30:00.000 20061224 200 2006-12-24 00:02:00.000 20070114 200 2007-01-14 00:02:00.000 20070114 100000 2007-01-14 10:00:00.000 20070523 23000 2007-05-23 02:30:00.000 20070526 20000 2007-05-26 02:00:00.000 20070527 230001 2007-05-27 23:00:01.000 20070528 230001 2007-05-28 23:00:01.000 20070531 153000 2007-05-31 15:30:00.000 20070602 20000 2007-06-02 02:00:00.000 20070604 23000 2007-06-04 02:30:00.000 20070605 0 2007-06-05 00:00:00.000 20070606 0 2007-06-06 00:00:00.000 20070606 230001 2007-06-06 23:00:01.000 20070608 230001 2007-06-08 23:00:01.000 20070619 23002 2007-06-19 02:30:02.000 20070710 30600 2007-07-10 03:06:00.000
(20 row(s) affected)
-
Sorry... got carried away having fun
For the format similar to what you ultimately requested... feel free to tweek it... includes a column with a "normalized" DATETIME and a couple of other handy conversions so that if you ever need to run stats (ie. how many jobs failed yesterday), you can do so.
SELECT d.Instance_ID, d.RunStatus, fRunDate = CAST( RIGHT(DATENAME(yy,d.RunDate),2) + ' ' --2 digit year with zero fill + LEFT(DATENAME(mm,d.RunDate),3) + ' ' --3 character month spelled out + REPLACE(STR(DAY(d.RunDate),2),' ','0') + ' ' --2 digit day with zero fill + LEFT(DATENAME(dw,d.RunDate),3) + ' ' --3 character day of week spelled out + REPLACE(RIGHT(CONVERT(VARCHAR(30),d.RunDate,100),7),' ','0') --hh:mmAM(PM) with zero fill AS CHAR(21)), fRunDuration = CONVERT(CHAR(8),d.RunDuration,108), RunDurSeconds = CAST(CAST(RunDuration AS FLOAT)*24*60*60 AS INT), d.RunDate FROM (--Derived table converts int columns of Run_Date and Run_Time to single datetime column SELECT Instance_ID, RunDate = CAST(STR(Run_Date) + ' ' + STUFF(STUFF(REPLACE(STR(Run_Time,6),' ','0'),5,0,':'),3,0,':') AS DATETIME), RunStatus = Run_Status, RunDuration = CAST(STUFF(STUFF(REPLACE(STR(Run_Duration,6),' ','0'),5,0,':'),3,0,':') AS DATETIME) FROM MSDB.dbo.SysJobHistory ) d
Instance_ID RunStatus fRunDate fRunDuration RunDurSeconds RunDate ----------- ----------- --------------------- ------------ ------------- ------------------------------------------------------ 129 0 06 Jul 30 Sun 01:00AM 00:00:19 19 2006-07-30 01:00:00.000 130 0 06 Jul 30 Sun 01:00AM 00:00:19 19 2006-07-30 01:00:00.000 131 0 06 Aug 06 Sun 12:00AM 00:02:26 146 2006-08-06 00:00:00.000 132 0 06 Aug 06 Sun 12:00AM 00:02:26 146 2006-08-06 00:00:00.000 133 1 06 Aug 06 Sun 01:00AM 00:03:45 225 2006-08-06 01:00:00.000 134 1 06 Aug 06 Sun 01:00AM 00:03:45 225 2006-08-06 01:00:00.000 135 0 06 Aug 13 Sun 12:00AM 00:02:24 144 2006-08-13 00:00:01.000 136 0 06 Aug 13 Sun 12:00AM 00:02:25 145 2006-08-13 00:00:00.000 137 1 06 Aug 13 Sun 01:00AM 00:03:47 227 2006-08-13 01:00:00.000 138 1 06 Aug 13 Sun 01:00AM 00:03:47 227 2006-08-13 01:00:00.000 139 0 06 Aug 20 Sun 12:00AM 00:02:29 149 2006-08-20 00:00:01.000 140 0 06 Aug 20 Sun 12:00AM 00:02:30 150 2006-08-20 00:00:00.000 141 1 06 Aug 20 Sun 01:00AM 00:04:23 263 2006-08-20 01:00:01.000 142 1 06 Aug 20 Sun 01:00AM 00:04:24 264 2006-08-20 01:00:00.000 143 0 06 Aug 27 Sun 12:00AM 00:02:13 133 2006-08-27 00:00:01.000 144 0 06 Aug 27 Sun 12:00AM 00:02:14 133 2006-08-27 00:00:00.000 145 1 06 Aug 27 Sun 01:00AM 00:03:41 221 2006-08-27 01:00:00.000 146 1 06 Aug 27 Sun 01:00AM 00:03:41 221 2006-08-27 01:00:00.000 147 0 06 Sep 03 Sun 12:00AM 00:00:05 5 2006-09-03 00:00:00.000 148 0 06 Sep 03 Sun 12:00AM 00:00:05 5 2006-09-03 00:00:00.000 149 1 06 Sep 03 Sun 01:00AM 00:04:02 242 2006-09-03 01:00:00.000 150 1 06 Sep 03 Sun 01:00AM 00:04:02 242 2006-09-03 01:00:00.000 151 0 06 Sep 10 Sun 12:00AM 00:02:25 145 2006-09-10 00:00:00.000 152 0 06 Sep 10 Sun 12:00AM 00:02:25 145 2006-09-10 00:00:00.000 153 1 06 Sep 10 Sun 01:00AM 00:08:00 480 2006-09-10 01:00:01.000 154 1 06 Sep 10 Sun 01:00AM 00:08:01 480 2006-09-10 01:00:00.000 155 0 06 Sep 17 Sun 12:00AM 00:02:33 153 2006-09-17 00:00:04.000 156 0 06 Sep 17 Sun 12:00AM 00:02:38 158 2006-09-17 00:00:00.000 157 1 06 Sep 17 Sun 01:00AM 00:07:22 442 2006-09-17 01:00:01.000 158 1 06 Sep 17 Sun 01:00AM 00:07:23 443 2006-09-17 01:00:00.000 159 0 06 Sep 24 Sun 12:00AM 00:02:54 174 2006-09-24 00:00:00.000 160 0 06 Sep 24 Sun 12:00AM 00:02:55 174 2006-09-24 00:00:00.000 161 1 06 Sep 24 Sun 01:00AM 00:04:04 244 2006-09-24 01:00:00.000 162 1 06 Sep 24 Sun 01:00AM 00:04:04 244 2006-09-24 01:00:00.000 163 0 06 Oct 01 Sun 12:00AM 00:02:49 169 2006-10-01 00:00:01.000 164 0 06 Oct 01 Sun 12:00AM 00:02:50 169 2006-10-01 00:00:00.000 165 1 06 Oct 01 Sun 01:00AM 00:03:39 219 2006-10-01 01:00:01.000 166 1 06 Oct 01 Sun 01:00AM 00:03:40 220 2006-10-01 01:00:00.000 167 0 06 Oct 08 Sun 12:00AM 00:01:14 74 2006-10-08 00:00:01.000 168 0 06 Oct 08 Sun 12:00AM 00:01:16 76 2006-10-08 00:00:00.000 169 1 06 Oct 08 Sun 01:00AM 00:03:04 184 2006-10-08 01:00:00.000 170 1 06 Oct 08 Sun 01:00AM 00:03:04 184 2006-10-08 01:00:00.000 171 0 06 Oct 15 Sun 12:00AM 00:00:12 11 2006-10-15 00:00:00.000 172 0 06 Oct 15 Sun 12:00AM 00:00:12 11 2006-10-15 00:00:00.000 173 0 06 Oct 15 Sun 01:00AM 00:00:26 26 2006-10-15 01:00:00.000 174 0 06 Oct 15 Sun 01:00AM 00:00:26 26 2006-10-15 01:00:00.000 175 0 06 Oct 22 Sun 12:00AM 00:00:12 11 2006-10-22 00:00:00.000 176 0 06 Oct 22 Sun 12:00AM 00:00:12 11 2006-10-22 00:00:00.000 177 1 06 Oct 22 Sun 01:00AM 00:00:36 36 2006-10-22 01:00:00.000 178 1 06 Oct 22 Sun 01:00AM 00:00:36 36 2006-10-22 01:00:00.000 179 0 06 Oct 29 Sun 12:00AM 00:00:05 5 2006-10-29 00:00:01.000 180 0 06 Oct 29 Sun 12:00AM 00:00:06 5 2006-10-29 00:00:00.000 181 1 06 Oct 29 Sun 01:00AM 00:00:40 40 2006-10-29 01:00:00.000 182 1 06 Oct 29 Sun 01:00AM 00:00:41 41 2006-10-29 01:00:00.000 183 0 06 Nov 05 Sun 12:00AM 00:00:09 9 2006-11-05 00:00:01.000 184 0 06 Nov 05 Sun 12:00AM 00:00:10 10 2006-11-05 00:00:00.000 185 1 06 Nov 05 Sun 01:00AM 00:00:26 26 2006-11-05 01:00:00.000 186 1 06 Nov 05 Sun 01:00AM 00:00:26 26 2006-11-05 01:00:00.000 187 0 06 Nov 12 Sun 12:00AM 00:00:08 8 2006-11-12 00:00:01.000 188 0 06 Nov 12 Sun 12:00AM 00:00:09 9 2006-11-12 00:00:00.000 189 1 06 Nov 12 Sun 01:00AM 00:00:24 23 2006-11-12 01:00:00.000 190 1 06 Nov 12 Sun 01:00AM 00:00:24 23 2006-11-12 01:00:00.000 191 0 06 Nov 19 Sun 12:00AM 00:00:11 11 2006-11-19 00:00:01.000 192 0 06 Nov 19 Sun 12:00AM 00:00:12 11 2006-11-19 00:00:00.000 193 1 06 Nov 19 Sun 01:00AM 00:16:55 1015 2006-11-19 01:00:01.000 194 1 06 Nov 19 Sun 01:00AM 00:16:56 1016 2006-11-19 01:00:00.000 195 0 06 Nov 26 Sun 12:00AM 00:02:33 153 2006-11-26 00:00:01.000 196 0 06 Nov 26 Sun 12:00AM 00:02:34 154 2006-11-26 00:00:00.000 197 1 06 Nov 26 Sun 01:00AM 00:20:25 1225 2006-11-26 01:00:01.000 198 1 06 Nov 26 Sun 01:00AM 00:20:26 1226 2006-11-26 01:00:00.000 199 0 06 Dec 03 Sun 12:00AM 00:02:38 158 2006-12-03 00:00:01.000 200 0 06 Dec 03 Sun 12:00AM 00:02:40 160 2006-12-03 00:00:00.000 201 1 06 Dec 03 Sun 01:00AM 00:20:43 1243 2006-12-03 01:00:00.000 202 1 06 Dec 03 Sun 01:00AM 00:20:43 1243 2006-12-03 01:00:00.000 203 0 06 Dec 10 Sun 12:00AM 00:02:34 154 2006-12-10 00:00:00.000
--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) -
wow... what a great post!
thanks !
_________________________
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply