Convert Error

I am trying to convert a varchar(10) into a smalldatetime field in a view.

The table holds the date information in the format dd/mm/yyyy in a
varchar(10).

I can convert most fields in this format in my view using;

CONVERT (smalldatetime, ActualInDate, 103)

However one field fails, I have some dates as 02/01/0000, this is because it is a blank system date which is output from my mainframe job to extract data from another server.

Can I convert these blank dates to sql blank dates in my convert statement, ie replace these dates from 02/01/0000 to 01/01/1900.

The error I get is the error below;

Database Server: Microsoft SQL Server
Version 08.00.0760
Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime datatype resulted in an out-of-range small datetime value.

Cheers,

JonMy guess would be that you are having problems with different data rows than you think are causing the problems. Use the IsDate() function to show you the "problem children" in your data. I suspect that your server is interpreting the dates as MM/DD/YYYY instead of dd/mm/yyyy as you expect. If that is the case, I'd convert the whole bunch to ISO standard format of YYYY-MM-DD (like 1900-01-01), which is a lot harder to misinterpret!

-PatP|||This appears to work:

CREATE TABLE MyTable
(
ActualInDate varchar(20)
)

INSERT INTO MyTable(ActualInDate)
VALUES ('02/01/0000')
INSERT INTO MyTable(ActualInDate)
VALUES ('13/02/1975')

SELECT CONVERT (smalldatetime, (CASE WHEN ActualInDate = '02/01/0000' THEN '01/01/1900' ELSE ActualInDate END), 103)
FROM MyTable|||Yup, it appears, but appearances are misleading...

SELECT CONVERT (smalldatetime, (
case
when charindex('0000', ActualInDate) > 0 then '01/01/1900'
else case
when isdate(ActualInDate) = 0 then '01/01/1900'
else ActualInDate end
end), 103)
FROM MyTable|||Thanks for the ideas but I just used this to do it and it seems fine:

CONVERT (smalldatetime, REPLACE(ActualIn, '02/01/0000', '01/01/1900'), 103)

Cheers|||rdjabarov,
do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.

He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.|||rdjabarov,
do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.

He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.I don't think that's his sole purpose in life, but rdjabarov often comes across that way. I've just had to develop thicker skin where his posts are concerned.

He really does know what he's doing (at least as often as most of us do anyway :)), but he comes across pretty abrasively at time.

-PatP|||Well there's the other side of the coin as well...you have to take everything with a grain of salt...because

IT JUST DOESN'T MATTER

Speaking of which...where's Rudy?|||I did not say he did'nt know what he was talking about. I was objecting to the snide remarks

I am sorry.
I am cranky ass.
Quitting smoking.
Unrealistic deadline.
Little sleep.
Stressed from a couple job interviews.
Too easily offended from my hot southern celtic blood.
I apologize.|||Speaking of which...where's Rudy?speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood

;)|||i was actually enjoying the intellectual dialogue in this thread

I'm still looking...|||speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood

;)Yeah, but it does help to put things into context!

-PatP|||Pat does contexts ;)