22007 Error 241
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About conversion failed when converting date and/or time from character string. sql server 2008 Us Learn more about Stack Overflow the company Business Learn more about hiring
Conversion Failed When Converting Date And Or Time From Character String Sql Server 2012
developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the convert datetime Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Server 2008 - error 241 convert sql server up vote 1 down vote favorite I have an error in my stored procedure on SQL Server 2008. I need to do stuff and insert data in my table only if the current element doesn't already exist. So I create a stored procedure: ALTER PROCEDURE [dbo].[upload_bank_file] @date varchar(10), @date_valeur varchar(10), @libelle varchar(255), @mode varchar(10), @code varchar(10), @date_effet varchar(10), @debit float, @credit float AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; print @credit; IF Not EXISTS (SELECT 1 FROM dbo.bank_import WHERE dbo.bank_import.Libelle = @libelle) --DROP PROCEDURE upload_bank_file; Begin IF (SUBSTRING(@date_effet, 4, 2) LIKE '01' AND SUBSTRING(@date, 1, 2) LIKE '12') SET @date_effet = CONVERT(varchar, (CONVERT(int, SUBSTRING(@date, 7, 2)) + 1)) + '-' + @date_effet; ELSE SET @date_effet = SUBSTRING(@date, 7, 2) + '-' + @date_effet; insert into dbo.bank_import ([Date], [Date_Valeur], [Libelle], [Mode], [Code], [Date_Effet], [Debit], [Credit]) VALUES (CONVERT(datetime, @date, 120), CONVERT(datetime, @date_valeur, 120), @libelle, @mode, @code, CONVERT(datetime, @date_effet, 120), @debit, @credit); END END When I test it via my php moulinette (to automatize file parsing and call to the stored procedure), I got the following error : [0] => 22007 [SQLSTATE] => 22007 [1] => 241 [code] => 241 [2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères. [message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Échec de la conversion de la date et/ou de l'heure à partir d'une chaîne de caractères. I'm passing date to the stored procedure via varchar, in yyyy/mm/dd format, and mm/dd
Join INTELLIGENT WORK FORUMSFOR COMPUTER PROFESSIONALS Log In Come Join Us! Are you aComputer / IT professional?Join Tek-Tips Forums! Talk With Other Members Be Notified Of ResponsesTo Your Posts Keyword Search One-Click Access To YourFavorite Forums Automated SignaturesOn Your Posts Best Of All, It's Free! Join Us! *Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting Guidelines Promoting, selling, recruiting, coursework and thesis posting is forbidden.Tek-Tips Posting Policies Jobs Jobs from Indeed What: Where: jobs http://stackoverflow.com/questions/24782199/sql-server-2008-error-241 by Link To This Forum! Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.Just copy and paste the BBCode HTML Markdown MediaWiki reStructuredText code below into your site. Microsoft SQL Server: Programming Forum at Tek-Tips HomeForumsProgrammersDBMS PackagesMicrosoft SQL Server: Programming Forum Error converting datetime in SQL Server job thread183-1460432 Forum Search FAQs Links http://www.tek-tips.com/viewthread.cfm?qid=1460432 MVPs Error converting datetime in SQL Server job Error converting datetime in SQL Server job flstffatboy (TechnicalUser) (OP) 23 Mar 08 06:21 Hello,I'm getting the following error when running this code within a SQL Server 2000 job.There code runs fine within query analyzer.Could anyone shed any light on this?Error - "Syntax error converting datetime from character string. [SQLSTATE 22007] (Error 241).The step failed."Code below:declare@PLineasVarchar(3)declare@BegDtasdatetimedeclare@EndDtasdatetimeset@PLine='230'set@BegDt=(select "Period Start Date Month"from cognos.dbo.Fydates2where datediff(day, "Date",getdate()) = 1)set@EndDt=getdate()"Period Start Date Month" and "Date" fields are both a smalldatetimeThanks,FLSTF RE: Error converting datetime in SQL Server job bborissov (Programmer) 23 Mar 08 06:38 Try:CODEselect @BegDt = [Period Start Date Month]from cognos.dbo.Fydates2where datediff(day, [Date], getdate()) = 1) Borislav BorissovVFP9 SP2, SQL Server 2000/2005.Microsoft MVP VFP RE: Error converting datetime in SQL Server job foxdev (Programmer) 24 Mar 08 14:00 What Borislav said.The reason it may run fine in Query Analyzer and not in your job is they differ in how their "set quoted identifier" setting is configured.When in doubt, you can always explicitly set it, but I think using the br
this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to http://www.dbforums.com/showthread.php?320826-Job-error-converting-datetime-from-char-string proceed. To start viewing messages, select the forum that you want to visit from the selection below. Results 1 to 2 of 2 Thread: Job error: converting datetime from char string? Tweet Thread Tools Show Printable Version Subscribe to this Thread… Search Thread Advanced Search Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode 03-13-02,12:41 #1 Spudhead View Profile View Forum Posts Registered User Join sql server 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 conversion failed when 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. Reply With Quote 03-13-02,12:59 #2 achorozy View Profile View Forum Posts Visit Homepage Registered User 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.