SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Date Result Issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nmm
Starting Member

Canada
3 Posts

Posted - 12/27/2012 :  15:34:02  Show Profile  Reply with Quote
I have a query in 2005 and 2008 which should give the start and completion time of a job for last 6 months. However, if it crosses midnight the dates shown are bad..

SELECT CONVERT(CHAR(10), start_time, 126) AS "DATE",
host_name,
job_name,
job_class,
operation,
status,
MIN(start_time) AS "START_TIME",
MAX(completion_time) AS "COMPLETION_TIME",
DATEDIFF(MINUTE, MIN(start_time), MAX(completion_time)) AS "DURATION"
FROM dasdb..batch_job_log
WHERE step_name IS NULL
AND job_class = 'HOUSEKEEPING'
AND status = 'SUCCEEDED'
AND start_time IS NOT NULL
AND completion_time IS NOT NULL
GROUP BY CONVERT(CHAR(10), start_time, 126),
host_name, job_name, job_class, operation, status
ORDER BY CONVERT(CHAR(10), start_time, 126) DESC
go

In houskeeping we have multiple steps with start/end time. However, i want just the start time (eg 11pm) and end time (eg 4am).

Please help me with the SQL.

Edited by - nmm on 12/27/2012 15:48:56

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/27/2012 :  17:21:40  Show Profile  Reply with Quote
Actually solution is possible until this statement :However, i want just the start time (eg 11pm) and end time (eg 4am).

Read this:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

nmm
Starting Member

Canada
3 Posts

Posted - 12/28/2012 :  09:04:06  Show Profile  Reply with Quote
Here is the Table Column's -

Login_id Log_date Job_name Step_name Job_Class Operation Status Start_Time Completion_time OS_Login
-------- ------------------------ --------- ------------- ------------ --------- --------- ------------------------- ------------------------- --------------
24 7/5/2012 11:53:22.647 PM housekeep [NULL] HOUSEKEEPING RUN FAILED 7/5/2012 11:53:22.647 PM 7/5/2012 11:53:26.267 PM reavssqladm_pr
25 7/5/2012 11:53:23.063 PM housekeep gen_dbcc HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:53:23.063 PM 7/5/2012 11:53:23.757 PM reavssqladm_pr
26 7/5/2012 11:53:23.930 PM housekeep gen_updt_stat HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:53:23.930 PM 7/5/2012 11:53:24.397 PM reavssqladm_pr
27 7/5/2012 11:53:24.570 PM housekeep gen_recompile HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:53:24.570 PM 7/5/2012 11:53:25.000 PM reavssqladm_pr
28 7/5/2012 11:53:25.177 PM housekeep gen_dump HOUSEKEEPING RUN FAILED 7/5/2012 11:53:25.177 PM 7/5/2012 11:53:26.190 PM reavssqladm_pr
29 7/5/2012 11:54:01.383 PM housekeep [NULL] HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:01.383 PM 7/5/2012 11:54:03.050 PM reavssqladm_pr
30 7/5/2012 11:54:01.833 PM housekeep gen_dbcc HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:01.833 PM 7/5/2012 11:54:02.430 PM reavssqladm_pr
37 7/5/2012 11:54:12.717 PM housekeep [NULL] HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:12.717 PM 7/5/2012 11:54:16.150 PM reavssqladm_pr
38 7/5/2012 11:54:13.127 PM housekeep gen_dbcc HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:13.127 PM 7/5/2012 11:54:13.717 PM reavssqladm_pr
39 7/5/2012 11:54:13.913 PM housekeep gen_updt_stat HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:13.913 PM 7/5/2012 11:54:14.320 PM reavssqladm_pr
40 7/5/2012 11:54:15.077 PM housekeep gen_dump HOUSEKEEPING RUN SUCCEEDED 7/5/2012 11:54:15.077 PM 7/5/2012 11:54:15.800 PM reavssqladm_pr
Go to Top of Page

nmm
Starting Member

Canada
3 Posts

Posted - 12/28/2012 :  09:05:21  Show Profile  Reply with Quote
Table DDL:
USE dasdb
go
CREATE TABLE dbo.batch_job_log
(
LOG_ID bigint NOT NULL,
LOG_DATE datetime CONSTRAINT DF__batch_job__LOG_D__0CBAE877 DEFAULT getdate() NULL,
HOST_NAME varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
DB_TYPE varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
INSTANCE_NAME varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
DB_NAME varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
JOB_NAME varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
STEP_NAME varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
JOB_CLASS varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
OPERATION varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
STATUS varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
START_TIME datetime NULL,
COMPLETION_TIME datetime NULL,
RUN_HOST varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
OS_LOGIN varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
CONSTRAINT batch_job_log_pk
PRIMARY KEY CLUSTERED (LOG_ID)
)
go
IF OBJECT_ID(N'dbo.batch_job_log') IS NOT NULL
PRINT N'<<< CREATED TABLE dbo.batch_job_log >>>'
ELSE
PRINT N'<<< FAILED CREATING TABLE dbo.batch_job_log >>>'
go
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/28/2012 :  09:16:56  Show Profile  Reply with Quote
What is expected output and initial post is still not clear
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000