Error converting datetime from character string

Question

Error converting datetime from character string

Gert de Swardt

Mr or Mrs. 500

Points: 541
More actions
September 24, 2003 at 8:48 am

#81158

I have come across a strange issue that I don't know how to solve. Maybe one of you could perhaps help with this.
When I execute the following querry it works 100%
declare @date int
select @date = 20030924
select convert(varchar(17),convert(datetime,convert(nvarchar(8),@date)))
When I run the next query it doesn't work
select convert(varchar(17),convert(datetime,convert(nvarchar(8),last_run_date)))
from msdb..sysjobservers
It comes up with the following error msg:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
But when I change the query to this:
select convert(varchar(17),convert(datetime,convert(nvarchar(8),last_run_date)))
from msdb..sysjobservers
where last_run_duration = 7
It works fine.
Any suggestions?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

That column isn't a datetime data type, it is an integer. If the job hasn't run, the column is zero, rather than a date, and therefore conversion fails.

With the last_run_duration > 7, it works because there will be a valid date in the column.

AFPeterson SSCommitted Points: 1580 More actions · Answer 2

At a technical level, the datetime field is two separate integers. The first is the number of days starting from the data Jan 1, 1900. The second integer part of datetime is the number of milliseconds from midnight.

It's interesting that sysjobservers splits out the time and date into separate integer fields. Anyway, grab the integer for last_run_date and then use the system function date DATEADD ( datepart , number, date ). For date part, use day, for number, use the field value, and for date, use the fixed value January 1, 1900. You will get the date. This will return a datetime value.

What's the business problem you're trying to solve?

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
At a technical level, the datetime field is two separate integers. The first is the number of days starting from the data Jan 1, 1900. The second integer part of datetime is the number of milliseconds from midnight.

At a more technical level, the datetime data type is eight bytes where the left four bytes store the number of days before or after 1900-01-01 and the right four bytes store the number of "clock ticks" since midnight. The binary representation of each four byte piece is indeed the same as used for the integer data type. There are 300 ticks in one second (which, strangely, is about an order of magnitude more precise than @@TIMETICKS). Try this:

DECLARE @FirstTime datetime, @LastTime datetime

SET @FirstTime = '2003-09-24 23:59:59.999'

SET @LastTime = '2003-09-25 00:00:00.001'

SELECT @FirstTime, @LastTime, CASE WHEN @FirstTime = @LastTime THEN 'Same time' END

(I hope that's a meaningful example of why you may need to know this.)

--Jonathan