CAST Issue

Question

CAST Issue

cafemar1

SSCrazy

Points: 2405
More actions
August 14, 2002 at 4:46 am

#158631

Hello all. I know that I read something about this conversion issue a few weeks back, but couldn't find it today.
This is the case. I created a store proc to run a monthly report. So far fine, now one of my users requested to add a field in the report which calculates a date plus 90 days. I tested the cast statement for one predetermined record and it did what I needed to do, but when I run it as part of the store proc it came back with an error message. I took the date field from my store proc and it did work OK again.
I don't want to bother you guys with the whole statement, which is kind of complex, so I am just showing a simple query I ran against one of the tables with the same result. Any suggestions? As always, all your help is very welcome. By the way, it does not matter if I use char, varchar, or datetime it comes back with the same message, Thanks
SELECT CASE_NO,
CAST(DATEADD([day], 90, FIRST_M_DATE) AS varchar (12)) AS 'dbar'
FROM CASE_TAB
WHERE TR_CODE = 'XXX'
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted
in an out-of-range datetime value.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Andy Jones, DBA Hall of Fame Points: 3641 More actions · Answer 1

It looks like FIRST_M_DATE is of char datatype and contains records that cannot be cast to dates and therefore dateadd fails.

Regards,

Andy Jones

.

cafemar1 SSCrazy Points: 2405 More actions · Answer 2

Thanks Andy. I did check everything, but the data type in my original table. For some reason who ever designed this db, used char for the dates instead of datetime.

I will recreate the table to a sample db, chage the data type and then run the dateadd again.

I am sure this will solve the problem. Thanks again

cafemar1 SSCrazy Points: 2405 More actions · Answer 3

Back again. I did check the table design and what happened is this:

The data comes from a system called BBX and the date data type in that system is character. When import those fields into SQL Server, SQL Server cannot recognize the filed as date due to the date format used in BBX.

Then, I tried this conversion:

declare @date datetime

select @date = getdate()

select convert(varchar(12), @date, 110)AS 'DATE',

CAST(DATEADD([day], 90, @date) AS varchar(12)) AS 'dbar'

FROM case_tab

This works for the system date, but no for the date field I used to get the data.

declare @date datetime

select @date = first_m_Date

select convert(varchar(12), @date, 110)AS 'DATE',

CAST(DATEADD([day], 90, @date) AS varchar(12)) AS 'dbar'

FROM case_tab

even though I am using the right table and right coulmn name, QA came back with the following message:

Server: Msg 207, Level 16, State 3, Line 2

Invalid column name 'first_m_Date'.

Any suggestions? I am doing the right conversion? Where is my error? Please!!

Thank you

sarmistead SSCommitted Points: 1671 More actions · Answer 4

Your select, setting @date equal to first_m_Date, is not part of the actual "select ... from" of the query. You really don't need to set up a variable to hold the date value. Try this:

select convert(varchar(12), first_m_Date, 110)AS 'DATE',

CAST(DATEADD([day], 90, @date) AS varchar(12)) AS 'dbar'

FROM case_tab

If this doesn't work you'll need to look at the incoming data - some of it must be unrecognizable as something that can convert to a date field by SQL Server.

Steve Armistead,

Database Administration

Panther Systems Northwest, Inc.

Vancouver, WA

Steve

cafemar1 SSCrazy Points: 2405 More actions · Answer 5

Thanks Steve. I already used that one. My problem is that the field first_m_date is not a datetime field in SQL Server due to the BBX setting. We get the data from clients around the country using that old system and the dates are not recognized by SQL Server as date data type.

When I use the statement as you said, it came back with the initial error message I described earlier today.

I know, for sure, that the problem is data type related, but I cannot change my db's because of the clients system. I did create a test table with those fields as datetime data type and everything worked perfectly. But, you know real world is different than the one we always wanted. LOL

Now, I am using @date to see if setting a variable I was able to work around the issue and added to the select statement to compare date values. Thanks for youe help.

sarmistead SSCommitted Points: 1671 More actions · Answer 6

Can you provide a few samples of the actual data coming in from the BBX system? I'm sure you can do some string manipulation work on the quasi-date field to make it something SQL can understand.

Steve Armistead,

Database Administration

Panther Systems Northwest, Inc.

Vancouver, WA

Steve

cafemar1 SSCrazy Points: 2405 More actions · Answer 7

Thanks Steve.

I did work yesterday a little bit more and so far I got SQL to use date be able to use dateadd function.

Still need to work more because the calculation is not quite ready yet.

It is giving me a date in the past. Is like use the 90 days in my statement as months and substract those months from my first_m_date.

SQLBill SSC Guru Points: 51440 More actions · Answer 8

I looked through BOL and it appears to me that when you set the variable using a table name, you must have a FROM.

So it should be:

select @date = first_m_Date

FROM case_tab

I think the missing FROM is why you are getting the error for the invalid column.

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 9

OOPPPSSS, I meant 'column name' and not 'table name' in the first line of my response.

-SQLBill