?
Solved

SQL Stored Procedures

Posted on 2011-05-05
6
Medium Priority
?
226 Views
Last Modified: 2012-05-11
I have a stored procedure that performs an insert into a single table and in the table are several date fields. In one database the sp inserts fine but in another database the same data will get kicked back for a "Syntax error converting datetime from character string." error because the incoming date columns are getting populated with  a formated date of "@glEffectiveDate = 'Feb 18 2011 12:00:00:000AM',". If I manually change the date to '02/18/2011' it works fine. I have tried to use the SET DATEFORMAT mdy but it doesnot seem to help.

What am I missing?
0
Comment
Question by:ccptechs
6 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35702641

Try using the format YYYYMMDD
ie
'20110218'
0
 

Author Comment

by:ccptechs
ID: 35703344
The Set DateFormat YYYYMMDD is invalid
0
 
LVL 35

Expert Comment

by:David Todd
ID: 35704407
Hi,

>>
Try using the format YYYYMMDD
ie
'20110218'
<< 

This is the format of the string you are attempting to convert to datetime, not what to set the dateformat to.

I'd suggest leaving the dateformat alone and using the format parameter in convert()

HTH
  David
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Accepted Solution

by:
ccptechs earned 0 total points
ID: 35706220
The stored proc where actually slightly different between the two databases in that the on sp erroring out had the parameters as  "@glEffectiveDate = 'default'" and the sp that worked used "@glEffectiveDate = null". Once I changed the non-working sp it worked.

Thanks
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 35706246
Use the following sample, it'll help ypi..
SELECT @glEffectiveDate = CONVERT(DATETIME,'Feb 18 2011 12:00:00:000AM')
0
 

Author Closing Comment

by:ccptechs
ID: 35735877
found the solution and corrected
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question