date converstion question

[analytics2006]

i have a field setup by my co-worker with type = varchar(5000). This is supposed to be a date field .

I tried to convert it to smalldatetime [cast (rcvdate as smalldatetime)] and got the error message "syntax error converting character string to smalldatetime data type.
how can i fix this? Any help will be greatly appreciated.

thanks

current output:

rcvddate

13261

 

[AlexCuse]

Run this query and see what you get:

select rcvdate from myTable where IsDate(rcvDate) = 0

My hunch is you have some data in there that is not in a format that can be converted to date/time.

If this is not it, perhaps you should try datetime data type, as this has a wider range of acceptable values.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

13261???

What date should that be?

And, why on earth are you using a varchar(5000). Don't you know that a field this wide will kill your performance?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[analytics2006]

I tried the isdate(rcvdate)=0 and it did return my record and I tried to convert to datatime instead of smalldatetime and I am still getting syntax message "Syntax error converting datetime from character string". I didn't set up this table and I have no idea why this person would set up as varchar 5000. that's why I am trying to fixed it. I have tied to changed the type in the design mode but still didn't work. Can anybody help??
thanks

 

[monksnake]

Is there only 1 row in that table?

<.

 

[monksnake]

When isdate returns 0 it's basically saying

"false", the item supplied is not a date.


You may want to reformat your "date" data.

<.

 

[AlexCuse]

If you can't get rid of this row, or replace the value with NULL (and then change the column to a date/time column) you should at least change it to a varchar(30) or something (I'm not sure what the character length is of the longest date format).

Varchar(5000) is just insane!

Ignorance of certain subjects is a great part of wisdom

 

[SQLSister]

These are the steps

1.Identify which records do not contain valid dates

2. Either fix to have valid dates or null out those records in the date field or if the field is required and there is no valid date you may need to delete the record. This must be handled on a case by case basis after you see the junk data you have in the field.(some you may be able to fix with code, some may require research into the paperwork or whatever).

3.Once all the data is fixed, immediately change the data type to date time to prevent a future occurrance. It is critical that fields containing date data are datetime datatypes if you ever need to do any reporting where you need to do date math.

4. If you cannot get anyone to allow you to change the data type, then you must add a trigger on the table that will not insert the record unless the value for that field would be a valid date. Otherwise the problem will re-occur everytime you correct the data. As Alex said if you can't change the data type, at least try to get them to limit the size. And make sure you request the change to the correct data type in writing. If they won't change it you want them to say so formally so that you won't get blamed when the system gets really slow later on. Or when reports aren't accurate because non-dates are being entered.


Questions about posting. See faq183-874

 

[Lamprey13]

FYI: SELECT DATEADD(DAY, 0, 13261) = 1936-04-23 00:00:00.000