Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dmc421
Starting Member
3 Posts |
 Posted - 2013-09-13 : 09:26:42
|
| Hi there!I hope this is in the right forum area, apologies if not!Recently I've been investigating an issue with a stored procedure, which when ran results in the following error.Conversion failed when converting date and/or time from character string. [SQLSTATE 22007] (Error 241)I've had no luck in figuring out the problem so far (my SQL knowledge is very basic), so if anybody has any thoughts/ideas on how to resolve this, I'd be very grateful!The stored procedure can be viewed at the following address.http://www.scribd.com/doc/167877010/Sp-KCC-HolidayDaysTakenMuch appreciated, thank you!From a big SQL newbie |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-09-13 : 09:34:31
|
| That link is not opened....any way that will cause because of incorrect data for datefield...so better to have the following condition to check for valid date where isdate(mydatefield) = 0--Chandu |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-09-13 : 11:07:22
|
| There are several things you should do:1. Post the data types for the table(s) begin used. It's hard to tell if the string-date-concatenation is causing the issue or if the table contains dates in string format (and possible causing an issue).2. Use ANSI or ISO date formats when using date string.3. Avoid using date strings4. Create a Date/Calendar table so you can join to it if you need to know what days are Holidays, Weekends, etc.. You may already have this, hard to tell from the code.Here are some links on how to ask a database question. This will help you provide sample data and expected output so we can help you better. It would be nice if you can post sample data (in a consumable format) and tell us what the sproc supposed to do. I suspect that it can be done without looping, but I don't have time to read through all the code to figure out what the logic is supposed to be, assuming it is right.http://www.sqlservercentral.com/articles/Best+Practices/61537/http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-15 : 04:05:55
|
quote:
Originally posted by dmc421
Hi there!I hope this is in the right forum area, apologies if not!Recently I've been investigating an issue with a stored procedure, which when ran results in the following error.Conversion failed when converting date and/or time from character string. [SQLSTATE 22007] (Error 241)I've had no luck in figuring out the problem so far (my SQL knowledge is very basic), so if anybody has any thoughts/ideas on how to resolve this, I'd be very grateful!The stored procedure can be viewed at the following address.http://www.scribd.com/doc/167877010/Sp-KCC-HolidayDaysTakenMuch appreciated, thank you!From a big SQL newbie
this can be a causehttp://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-09-15 : 04:08:07
|
quote:
Originally posted by dmc421
Hi there!I hope this is in the right forum area, apologies if not!Recently I've been investigating an issue with a stored procedure, which when ran results in the following error.Conversion failed when converting date and/or time from character string. [SQLSTATE 22007] (Error 241)I've had no luck in figuring out the problem so far (my SQL knowledge is very basic), so if anybody has any thoughts/ideas on how to resolve this, I'd be very grateful!The stored procedure can be viewed at the following address.http://www.scribd.com/doc/167877010/Sp-KCC-HolidayDaysTakenMuch appreciated, thank you!From a big SQL newbie
this can be a causehttp://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
dmc421
Starting Member
3 Posts |
Posted - 2013-09-16 : 09:54:06
|
thank you for the advice! I'm currently following the suggestions. I'll update the thread with whether it was a success |
|
|
|
dmc421
Starting Member
3 Posts |
Posted - 2013-09-19 : 10:16:58
|
| The issue has been resolved, thank you!It seems somehow the stored procedure had become corrupted, so the errors were a red herring. After recompiling it, after a lot of investigation it started working again...Thank you for your help :) |
|
|
|
|
|
|
|
|
Trouble with stored procedure