SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trouble with stored procedure
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dmc421
Starting Member

3 Posts

Posted - 09/13/2013 :  09:26:42  Show Profile  Reply with Quote
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-HolidayDaysTaken

Much appreciated, thank you!
From a big SQL newbie

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 09/13/2013 :  09:34:31  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/13/2013 :  11:07:22  Show Profile  Reply with Quote
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 strings
4. 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

Edited by - Lamprey on 09/13/2013 11:08:29
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/15/2013 :  04:05:55  Show Profile  Reply with Quote
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-HolidayDaysTaken

Much appreciated, thank you!
From a big SQL newbie


this can be a cause

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/15/2013 :  04:08:07  Show Profile  Reply with Quote
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-HolidayDaysTaken

Much appreciated, thank you!
From a big SQL newbie



this can be a cause
http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

dmc421
Starting Member

3 Posts

Posted - 09/16/2013 :  09:54:06  Show Profile  Reply with Quote
thank you for the advice! I'm currently following the suggestions. I'll update the thread with whether it was a success
Go to Top of Page

dmc421
Starting Member

3 Posts

Posted - 09/19/2013 :  10:16:58  Show Profile  Reply with Quote
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 :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000