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.
| Author |
Topic |
|
filf
Yak Posting Veteran
67 Posts |
 Posted - 2001-06-26 : 08:20:02
|
| Why are dates always a problem.I always seem to struggle with them. I am investigating data held within msdb.dbo.sysjobs and msdb.dbo.sysjobhistory.Using a web assist job I pull the job_id of the job based on its name from sysjobs, then I try to access all specific columns within sysjobhistory based on job_id and run_date.However the column run_date is stored as integer [WHY?]. I tried a number of things:a:SELECT step_name,run_status,run_date,run_duration FROM sysJobHistory where job_id = @jobId and run_date = getdate()Result: Disallowed implicit conversion from data type datetime to data type int, table 'msdb.dbo.sysjobhistory', column 'run_date'. Use the CONVERT function to run this query.Okay so I try the convert function:b:SELECT step_name,run_status,run_date,run_duration FROM sysJobHistory where job_id = @jobId and run_date = CONVERT(integer,getdate(),101) Result: it pulls back nothing.Try printing the converted date.c: print CONVERT(integer,getdate(),101) Result: 37067Try the statement with no date restrictiond: SELECT step_name,run_status,run_date,run_duration FROM sysJobHistory where job_id = @jobId Result: See all data for all steps related to that job id. The dates are displayed in the format:- yyyymmdd Tried thise:SELECT step_name,run_status,run_date,run_duration FROM sysJobHistory where job_id = @jobId and run_date = 20010626Result: See all data for all steps related to that jobid for that date.I am confused. How can I perform a select on this table based on date? why store dates as integers in system tables? I essentially want to pull dynamically the integer in the last sql statement based on the current day, it is beyond me how I do this.Thanks in advance for any help provided.Edited by - filf on 06/26/2001 08:31:11 |
|
|
|
|
|
|
|
Weirdness with SQL Server sys table dates