Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Job error: converting datetime from char string?

    Hello,

    I'm trying to set up a Job in SQL Server 2000 that emails company employees if they haven't completed a timesheet in the last 24 hrs.

    The only job step I have looks like:
    Code:
    declare
    @em varchar(100),
    @flname varchar(50),
    @ls datetime,
    @bdy varchar(500)
    
    declare ts_cursor cursor for
    	SELECT FL_EMAIL, FL_NAME, LASTSHEET FROM F_AND_L WHERE dateadd(dd,1,LASTSHEET)>getdate()
    open ts_cursor
    fetch next from ts_cursor
    into @em, @flname, @ls
    
    while @@FETCH_STATUS=0
    	begin
    		select @bdy='Dear ' +@flname+ ','+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+
    			'Hello, this is an automatic email. The timesheet records seem to indicate'+
    			' that the last time you completed a timesheet was '+@ls+ ' .'+CHAR(13)+CHAR(10)+
    			'Could you please complete one? Thanks :)'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+
    			'Regards,'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+
    			'The Intranet'
    		exec master.dbo.sendmail @em,@bdy,@subject='Timesheets'
    		fetch next from ts_cursor
    		into @em, @flname, @ls
    	end
    close ts_cursor
    deallocate ts_cursor
    (I should add that this was cannibalised from an existing job and I've never done this before).

    At the moment it returns an error:

    "Executed as user: NT AUTHORITY\SYSTEM. Syntax error converting datetime from character string. [SQLSTATE 22007] (Error 241). The step failed."

    What does this mean, and how do I fix it? I've run the select query in Query Analyser and it runs fine. My only thought is that it's the date format that's screwing it but I don't know how to check.

    Any suggestions?

    Ta,

    Spud.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    The error is in the formating of your message string, you can only concatenate string variables.

    select @bdy='Dear ' +@flname+ ','+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+
    'Hello, this is an automatic email. The timesheet records seem to indicate'+
    ' that the last time you completed a timesheet was '+@ls+ ' .'+CHAR(13)+CHAR(10)+
    'Could you please complete one? Thanks '+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+
    'Regards,'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+

    Test this, it will error out
    Code:
    declare @ls datetime
    
    set @ls = getdate()
    
    print 'The time is ' + @ls
    However either of these will work

    Code:
    declare @ls varchar(30)
    
    set @ls = getdate()
    
    print 'The time is ' + @ls
    
    
    ** OR **
    
    
    declare @ls datetime
    
    set @ls = getdate()
    
    print 'The time is ' + CONVERT(VARCHAR(30),@ls)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •