March 26, 2009 at 1:36 pm
Hi guys,
I have date and time column in a table in this format: date - 20090326 and time 43000 or 113000.
I want to check the time in the table if it is more than an hour with the current time.
Thanks,
March 26, 2009 at 1:51 pm
What's the 43000 mean? What does that measure?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2009 at 1:53 pm
43000 is 4:30 and 113000 is 11:30
Thanks,
March 26, 2009 at 1:57 pm
Divide the time by 10,000, to get the hour. Compare that to datepart(hour, getdate()), and you can get whether it's in whatever range you're looking for.
Will that do what you need?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2009 at 2:24 pm
Hi GS,
Declare @run_time char(8)
declare @run_date char(8)
set @run_time = (REPLACE(CONVERT(VARCHAR(10), GETDATE(), 108), ':', '') /10000)
set @run_date = CONVERT(VARCHAR(8), GETDATE(), 112)
SELECT DISTINCT NAME,SJS.LAST_RUN_TIME,sjs.last_run_date FROM dbo.sysjobs sj inner join dbo.sysjobhistory sjh
on sj.job_id = sjh.job_id INNER JOIN DBO.SYSJOBSERVERS SJS
ON SJS.JOB_ID = SJH.JOB_ID
WHERE NAME LIKE 'DBA%' and run_date = @run_date and ( condition )
So, in the above case I have some jobs starting with dba right. Suppose it runs every hour but somebody disabled it and it is not running. So the next hour it should alert me saying this job is not running for an hour. So, how can I use datepart in the condition portion of the above query to full fill the requirement. run_time should not be more than an hour to the current time.
Thanks,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply