Solved

Date conversion error in SQL Server stored procedure

Posted on 2012-04-03
7
264 Views
Last Modified: 2012-06-21
I have a stored procedure that is throwing the following error when I run it "Msg 241, Level 16, State 1, Procedure sp_Matl_OpenPO_Amt_Adhoc, Line 54
Conversion failed when converting date and/or time from character string." I am using '2012001' for @begdate value and '20120131' for @enddate value when running the stored procedure.

I can't figure out why it is giving me this error.  If I pull out the section of the stored procedure that includes the date reference and run it as a query it runs fine.  Can someone take a look at my procedure and see if anything stands out to you?  

I am using SQL Server 2008.
matlOpenstoredprocedure.sql
0
Comment
Question by:imstac73
  • 4
  • 2
7 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 37802330
2012001 isn't a valid date. I assume this is a copy and paste error?
0
 

Author Comment

by:imstac73
ID: 37802335
Yes sorry.  It should be '20120101'
0
 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 150 total points
ID: 37802387
Instead of declaring them as date types, declare them as nvarchar(8) and then cast them as a date in the query.

So...

 not between cast(@begdate as date) and cast(@enddate as date)
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 15

Accepted Solution

by:
gplana earned 150 total points
ID: 37802444
I think you should use "convert" sqlserver function for converting a varchar to datetime or viceversa. Please see this link:
http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 

Author Comment

by:imstac73
ID: 37802617
Do I need to change my conversion on my field?

replace(CONVERT(date,CONVERT(char,ph.IDTRV),112),'-','')
0
 

Author Comment

by:imstac73
ID: 37802628
gplana...are you referring to adding a convert to the parameters?
0
 

Author Comment

by:imstac73
ID: 37802666
I got it working.  What I did was made the parameters data type decimal(8,0) then I used the following conversion in my case statement
CONVERT(char, @begdate, 112) and CONVERT(char, @enddate, 112).

Thanks for your help.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now