date and time function?

[ravi]

Hi,
As we know all the job system tables store date(run_date) and time(run_time) seperate feild.
I have the situation compare this run_date & run_time with another date feild column.
my datefield = 2003-02-27 08:02:01.000

and my msdb system table has
run_date =20030227
run_time =60002

How I can compare this two value?.
Anybody has any advice about this?.
Thanks,
Ravi

[MAK]

Try this

select left(convert(varchar,run_date),4)+"-"+
substring(convert(varchar,run_date),5,2)+"-"+
right(convert(varchar,run_date),2) +" "+
case when len(convert(varchar,run_time)) =5 then
"0"+left(convert(varchar,run_time),1)+":"+
substring(convert(varchar,run_time),2,2)+":"+
right(convert(varchar,run_time),2)+":000" else
left(convert(varchar,run_time),2)+":"+
substring(convert(varchar,run_time),3,2)+":"+
right(convert(varchar,run_time),2)+":000" end as mydate
from sysjobhistory


for comparison try this
-----------------------

select *
from sysjobhistory where
left(convert(varchar,run_date),4)+"-"+
substring(convert(varchar,run_date),5,2)+"-"+
right(convert(varchar,run_date),2) +" "+
case when len(convert(varchar,run_time)) =5 then
"0"+left(convert(varchar,run_time),1)+":"+
substring(convert(varchar,run_time),2,2)+":"+
right(convert(varchar,run_time),2)+":000" else
left(convert(varchar,run_time),2)+":"+
substring(convert(varchar,run_time),3,2)+":"+
right(convert(varchar,run_time),2)+":000" end <=getdate()

[ravi]

Mak,
Thanks for your help.
Your query is helping me.
Thanks,
Ravi

[ravinder_k]

Couple of more suggestions.
a) handle datalength of 4 in the case statement for run_time. Because between 12:00AM and 1:00AM the length would be 4 characters.
b) the result run_time will be in 24 hour format.

[MAK]

yep thats true.

add another case statement for handling 4 character "time"