• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

SQL Stored Procedures

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
ccptechs
Asked:
ccptechs
1 Solution
 
Ephraim WangoyaCommented:

Try using the format YYYYMMDD
ie
'20110218'
0
 
ccptechsAuthor Commented:
The Set DateFormat YYYYMMDD is invalid
0
 
David ToddSenior DBACommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ccptechsAuthor Commented:
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
 
waltersnowslinarnoldCommented:
Use the following sample, it'll help ypi..
SELECT @glEffectiveDate = CONVERT(DATETIME,'Feb 18 2011 12:00:00:000AM')
0
 
ccptechsAuthor Commented:
found the solution and corrected
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now