Date Time issue.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Time issue..

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-09-08 : 09:21:39

Again am stuck in some date time problem... i have a column int type which stores date infor in this way 20090908 today date.. i want to make it like proper date ... or refer to
select last_run_date,last_run_time from msdb..sysjobsteps
i want ouput combined like this

08/09/2009 11:29:19 mean in proper datetime format using these two columns from MSDB?

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 09:25:09

how your time is saved in int column (format)?

Rahul Shinde

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 09:28:38

SELECT CONVERT(DATETIME,convert(VARCHAR,20090908),112)

Rahul Shinde

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-09-08 : 09:32:02

its same as in msdb column see and run this

select last_run_date,last_run_time from msdb..sysjobsteps

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-08 : 09:43:24

select last_run_date,last_run_time,dateadd(millisecond, last_run_time,convert(datetime,cast(nullif(last_run_date,0) as varchar(10))))
from msdb..sysjobsteps

Madhivanan

Failing to plan is Planning to fail

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 09:44:57

SELECT CAST(CONVERT(VARCHAR,CONVERT(DATETIME,convert(VARCHAR,last_run_date) ,112),106)
+ ' ' + substring(convert(VARCHAR,last_run_time),1,2)
+ ':' + substring(convert(VARCHAR,last_run_time),3,2)
+ ':' + substring(convert(VARCHAR,last_run_time),5,2) AS DATETIME)
from sysjobsteps
WHERE last_run_date <> 0

Rahul Shinde

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-09-08 : 09:49:59

Thanks Madhivanan Sir as always you are a life saver........
Thanks

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 09:54:23

quote:
Originally posted by madhivanan

select last_run_date,last_run_time,dateadd(millisecond, last_run_time,convert(datetime,cast(nullif(last_run_date,0) as varchar(10))))
from msdb..sysjobsteps

Madhivanan

Failing to plan is Planning to fail

I think time part is coming wrong in this one.

Rahul Shinde

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-08 : 09:55:07

quote:
Originally posted by ashishashish

Thanks Madhivanan Sir as always you are a life saver........
Thanks

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

You are welcome

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-08 : 09:55:47

quote:
Originally posted by ra.shinde

quote:
Originally posted by madhivanan

select last_run_date,last_run_time,dateadd(millisecond, last_run_time,convert(datetime,cast(nullif(last_run_date,0) as varchar(10))))
from msdb..sysjobsteps

Madhivanan

Failing to plan is Planning to fail

I think time part is coming wrong in this one.

Rahul Shinde

Where is wrong?

Madhivanan

Failing to plan is Planning to fail

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 09:57:45

your result
20080910, 123411, '9/10/2008 12:02:03 AM'
20090320, 105000, '3/20/2009 12:01:45 AM'

expected result
20080910, 123411, '9/10/2008 12:34:11 PM'
20090320, 105000, '3/20/2009 10:50:00 AM'

Rahul Shinde

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-09-08 : 10:00:08

In last_run_time, time saved as hh24mmss

Rahul Shinde

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-09-08 : 10:07:31

yes time part is wrong in this i tested it and it throwing wrong details... just wait for me i will be back in a couple of minutes that where it is wrong..

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 10:35:45

you may want to look at this topic as well:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111677

EDIT:
And this is a similar solution I had saved off at some point:


select	startTime = 
	convert(smalldatetime, 
	substring(run_date, 1,4) 
	+ '-' + substring(run_date, 5,2) 
	+ '-' + substring(run_date, 7,2) 
	+ ' ' + stuff(stuff(run_time, 3,0,':'), 6, 0, ':'))

	,durationSeconds = 
	convert(int,substring(run_duration,1,2)) * 60 * 60
	+ convert(int,substring(run_duration,3,2)) * 60
	+ convert(int,substring(run_duration,5,2))
from	(
	select	convert(varchar, run_date) run_date
		,replicate('0', 6-len(convert(varchar, run_time)))+convert(varchar, run_time) run_time
		,replicate('0', 6-len(convert(varchar, run_duration)))+convert(varchar, run_duration) run_duration
	from	sysjobHistory
	) a

Be One with the Optimizer
TG

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-09-08 : 15:34:24

quote:

Originally posted by TG

you may want to look at this topic as well:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111677

EDIT:
And this is a similar solution I had saved off at some point:


select	startTime = 
	convert(smalldatetime, 
	substring(run_date, 1,4) 
	+ '-' + substring(run_date, 5,2) 
	+ '-' + substring(run_date, 7,2) 
	+ ' ' + stuff(stuff(run_time, 3,0,':'), 6, 0, ':'))

	,durationSeconds = 
	convert(int,substring(run_duration,1,2)) * 60 * 60
	+ convert(int,substring(run_duration,3,2)) * 60
	+ convert(int,substring(run_duration,5,2))
from	(
	select	convert(varchar, run_date) run_date
		,replicate('0', 6-len(convert(varchar, run_time)))+convert(varchar, run_time) run_time
		,replicate('0', 6-len(convert(varchar, run_duration)))+convert(varchar, run_duration) run_duration
	from	sysjobHistory
	) a

Be One with the Optimizer
TG

hi TG thanks 4 ur solution this one is great but it doesn't suit my requirements even i also make same kind of logic for that but its really not good there is many drawbacks for this... if somebody try to rectify the mistake made by Madhivanana's solution then this is really great his solution is good enough i think if somebody try to correct the time part then this will be really good i also give it a try in morning..
Good Nite.....

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-08 : 15:56:05

quote:
thanks 4 ur solution this one is great

quote:
its really not good there is many drawbacks for this

Not sure what you mean by these two apparently conflicting statements?

quote:
but it doesn't suit my requirements

Isn't it as simple as removing [durationSeconds] output column, changing the table from sysJobHistory to sysJobSteps, and changing the column from run_time to last_run_time ???

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-09 : 02:15:04

[code]
select
last_run_date,
last_run_time,
cast(cast(nullif(last_run_date,0) as varchar(8)) as datetime)
+
stuff(stuff(right('00000'+cast(last_run_time as varchar(6)),6),3,0,':'),6,0,':') as full_date
from msdb..sysjobsteps
[/code]

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-09-09 : 07:01:58

quote:
Originally posted by TG

quote:
thanks 4 ur solution this one is great
quote:
its really not good there is many drawbacks for this
Not sure what you mean by these two apparently conflicting statements?

quote:
but it doesn't suit my requirements
Isn't it as simple as removing [durationSeconds] output column, changing the table from sysJobHistory to sysJobSteps, and changing the column from run_time to last_run_time ???

Be One with the Optimizer
TG

Again many thanks for your reply TG.. i already come up with solution here it is...
select last_executed_step_date from sysjobactivity
and i come up with solution before asking it but it's all about learning i asked here because i want to learn something more about datetime formatting ...

Here the two statements because i really appreciate that you spare some time for this and send a working solution and the drawback's include some sorting wise and many other.
So i asked you because i want to learn some decent solution for this.
Well again thanks for your time.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources