CODEWITHSUNDEEP
sql-servert-sql
AznDevil92
AznDevil92

Reputation: 554

Convert INT to DATE in WHERE clause - TSQL

Working with sysjobs and sysjobhistory on SQL Server and I have been googling around but I can't seem to find a direct answer to this question. I think it is because I'm trying to do this in a WHERE clause.

So basically trying to select a job that has failed on the same day that it ran (basically today's date), since the current code is selecting all of the existing jobs that failed.

Below is my code: 

SELECT DISTINCT 
    sj.name,
    COUNT(sjh.instance_id) AS errors
FROM 
    msdb..sysjobhistory sjh 
JOIN 
    msdb.dbo.sysjobs sj ON sj.job_id = sjh.job_id
WHERE 
    name IN ('SQLAgentJob1', 'SQLAgentJob2')
    AND sjh.run_status = 1
    AND (SELECT CONVERT(DATE, CONVERT(INT, sjh.run_date), 112)) = (SELECT CONVERT(DATE, GETDATE(), 112))
GROUP BY 
    sj.name

I keep getting an error stating:

Msg 529, Level 16, State 2, Line 13
Explicit conversion from data type int to date is not allowed.

Any ideas?

Upvotes: 2

Views: 5787

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131729

You don't have to do all those conversions, just convert your parameter (ie the current date) to the unseparated date format with 112:

SELECT DISTINCT sj.name, COUNT(sjh.instance_id) AS errors
FROM msdb..sysjobhistory sjh 
JOIN msdb.dbo.sysjobs sj ON sj.job_id = sjh.job_id
WHERE name IN ('SQLAgentJob1','SQLAgentJob2')
    AND sjh.run_status = 1
    AND sjh.run_date= CONVERT(nvarchar(8), GETDATE(), 112)
GROUP BY sj.name

There is no integer to date conversion because there is no integer representation for dates.

The run_date column stores the YYYYMMDD format as an integer, perhaps to reduce space consumption (4 bytes instead of 8), perhaps for some other reason.

To compare against it, just create the equivalent string with CONVERT(nvarchar(8), GETDATE(), 112). The conversion from string to integer is implicit

Upvotes: 5

jhilden
jhilden

Reputation: 12459

This should work.

SELECT DISTINCT sj.name,
    COUNT(sjh.instance_id) AS errors
FROM msdb..sysjobhistory sjh 
JOIN msdb.dbo.sysjobs sj ON sj.job_id = sjh.job_id
WHERE name IN ('SQLAgentJob1',
               'SQLAgentJob2')
            AND sjh.run_status = 1
            AND CAST(CONVERT(datetime, CAST(SJH.[run_date] AS CHAR(8))) as DATE) = CAST(GETDATE() as DATE)
            GROUP BY sj.name

Explanation

  • First convert from int to datetime
  • Cast down from datetime to date (drop the time)
  • Compare against today's date (with the time dropped)

Upvotes: 2

Related Questions