Converting datetime to int

Expand Post »

This set of code below shows all SQL Server Agent jobs that have failed for the date 200.03.29. The run_date column is an integer, but I want to run the code in such a way that the resultset is automatically generated for the day before the current date.

The code that works is below:

MS SQL Syntax (Toggle Plain Text)
SELECT [name] AS [Job Name],[server] AS  [Server], step_id, step_name, [message] AS [Error], 
  run_date AS [Date Started], run_time AS [Time Started], run_duration AS [Duration] 
FROM sysjobs JOIN sysjobhistory ON sysjobs.job_id = sysjobhistory.job_id	
WHERE run_date = 20090329
AND run_status = 0
select [name] as [Job Name],[server] as  [Server], step_id, step_name, [message] as [Error], 
  run_date as [Date Started], run_time as [Time Started], run_duration as [Duration] 
from sysjobs join sysjobhistory on sysjobs.job_id = sysjobhistory.job_id	
where run_date = 20090329
and run_status = 0

How can I change this so that run_date will get the equivalent integer value representing the day before the current date?

Similar Threads

Error for UpdateQuery from Visual Studio 2008 in ASP.NET
indexing in C#

bajanpoet

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

96 posts
since Sep 2006

Permalink

Mar 31st, 2009

Re: Converting datetime to int

CONVERT (varchar, GETDATE() - 1, 112) will give you yesterdays date in that format

squarefish

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Mar 2009

Permalink

Mar 31st, 2009

Re: Converting datetime to int

Click to Expand / Collapse Quote originally posted by squarefish ...

CONVERT (varchar, GETDATE() - 1, 112) will give you yesterdays date in that format

Thanks, but I wanted it as an INTEGER, not a varchar... the script references the sysjobhistory table in the msdb database, and, in that table, the run_date column is an integer.

bajanpoet

Reputation Points: 10

Solved Threads: 0

Junior Poster in Training

Offline

96 posts
since Sep 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: How to add a computed field to a DAL table

Next Thread in MS SQL Forum Timeline: Help needed with UPDATE Trigger

DaniWeb Message

Cancel Changes

User Name
Password