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 Result Issue

Author  Topic 

nmm
Starting Member

3 Posts

Posted - 2012-12-27 : 15:34:02
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.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-27 : 17:21:40
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

3 Posts

Posted - 2012-12-28 : 09:04:06
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

3 Posts

Posted - 2012-12-28 : 09:05:21
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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-28 : 09:16:56
What is expected output and initial post is still not clear
Go to Top of Page
   

- Advertisement -