MS SQL

Converting datetime to int

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2006

Posts: 90

Reputation: bajanpoet is an unknown quantity at this point

Solved Threads: 0

bajanpoet

Offline

Junior Poster in Training

Converting datetime to int

Mar 30th, 2009

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:

 Help with Code Tags 
 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?

•

Join Date: Mar 2009

Posts: 1

Reputation: squarefish is an unknown quantity at this point

Solved Threads: 0

squarefish squarefish is offline

Offline

Newbie Poster

Re: Converting datetime to int

Mar 31st, 2009

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

•

Join Date: Sep 2006

Posts: 90

Reputation: bajanpoet is an unknown quantity at this point

Solved Threads: 0

bajanpoet

Offline

Junior Poster in Training

Re: Converting datetime to int

Mar 31st, 2009

•

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.