Convert Error in Stored Procedure

Question

Convert Error in Stored Procedure

bwillyerd

Right there with Babe

Points: 730
More actions
August 20, 2004 at 10:36 am

#86291

Howdy,
Here is my proc:
CREATE PROCEDURE Find_ArchComment
(
@IVDNO [int]
)
AS
SET NOCOUNT ON
DECLARE @ThruDate smalldatetime -- Floating Date Variable
SET @ThruDate = 'SELECT ThruDate FROM SEMS.dbo.ArchiveControl WHERE AreaName = ''SECC'''
SELECT CC_Type, CC_Date, CC_EmpNo, CC_Cmnt
FROM SEMS.dbo.SECC
WHERE ((CC_IVDnbr = @IVDNO) AND (CC_Date < CONVERT(SmallDateTime, @ThruDate, 101)))
ORDER BY CC_Seq DESC
GO
------------
I get this:
Server: Msg 295, Level 16, State 3, Procedure Find_ArchComment, Line 8
Syntax error converting character string to smalldatetime data type.
The SECC tables CC_Date is smalldatetime. The ArchiveControl ThruDate is a varchar(12). The ArchiveControl ThruDate has inputs from different areas and the date format is not consistent so the field is varchar.
I am hoping the CONVERT (or maybe I should be using CAST?) is just not in the correct place and someone can correct my attempt.
Thanks in advance!
Bill

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

You say the dates aren't consistant. There's your problem. SQL Server has to know how to interpret the date values. It doesn't know what 01/02/2004 is. Is is Jan 02 or 1 Feb? SET DATEFORMAT dmy or SET DATEFORMAT mdy will tell SQL Server how to interpret it. BUT that doesn't help if both types of values are used.

Another thing is, do you have dates prior to Jan 1 1900? If so you can't use smalldatetime.

-SQLBill

bwillyerd Right there with Babe Points: 730 More actions · Answer 2

Sorry to be so gray about the ArchiveControl tables values

Here is an example of the db:

AreaName Char(4),

ArchivedData bit,

ThruDate Char(12)

PassOffCode Char(2)

Data:

SEGC,1,20011231,FC

SEAS,0,<<NULL>>,AH

SECC,1,12/31/2001

So the field data for this particular proc will always be in the mm/dd/yyyy format.

But other data in the ArchiveControl table will not be.

I hope this helps?

Bill

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Add the SET DATEFORMAT command to your script.

SQL Server doesn't know that 12/31/2001 is December 31, 2001. You and I know it can't be anything else, but SQL Server has to be TOLD how to interpret it.

Use

SET DATEFORMAT mdy

to tell SQL Server the format the data is in. Then follow that with the SELECT or INSERT commands.

SQL Server prefers yyyymmdd format as it never seems to misinterpret that.

-SQLBill

bwillyerd Right there with Babe Points: 730 More actions · Answer 4

Thanks SQLBill for your assistance.

I am sure they made a differnce, but one of the main problems with the proc was the SET @ThruDate line. I needed the SELECT stmt to be incased in ()'s instead of '''s to get the value from the ArchiveControl table. The reason for the error was that the variable contained 12 characters of the SELECT stmt 'SELECT CC_DA' cannot be converted to a date no matter how you mush it or swish it around.

Thanks bunches!