Convert CHAR to smalldatetime

[lyudmila]

I have problem to convert CHAR data to smalldatetime.
My data value in table in format like "09-AUG-2000", this field size is CHAR(11). When I run ISDATE function to determine what's wrong, i have result 0, nothing wrong. I was able to convert to DATETIME after running same procedure for a few times. But I was not able to convert from datetime to smalldatetime not using alter table and do select into new table with CONVERT function. We keep dates in smalldateformat, it's a company policy. Is any trick I need to now. I have tried both functions: CONVERT and CAST. Please help!!!!!!!!

 

[SQLBill]

You need to check the BOL. ISDATE returns a 1 when it's a valid date.

-SQLBill

 

[SQLBill]

What does your script look like?

I tried this:

SELECT CONVERT(SMALLDATETIME, 09-AUG-2000)

and the result was:

2000-08-09 00:00:00

which is what it's supposed to be.

I'm thinking that either your script is incorrect or some of your rows contain invalid data (such as NULL).

-SQLBill

 

[lyudmila]

I run ISDATE(invdt)=0 to to determine invalid date - problem rows

 

[SQLBill]

The proper syntax for ISDATE is:

select isdate(invdt)
from yourtablename

I don't believe you can assign it anything like you are trying to do. I've checked the BOL and can't find anything like that.

The above syntax will return a 1 for a valid date and a 0 for invalid date.

-SQLBill

 

[tlbroadbent]

The following conversion works.

select NewDateTime = convert(smalldatetime, '09-aug-2002')

You can add a smalldatetiem column to the table and update it with valid dates from the character column.

Update Table1
Set NewDateCol=convert(smalldatetime, OldDateCol)
Where isdate(olddatecol)=1

You can identify values that are not numeric.

Select * from Table1
Where isdate(olddatecol)=0 If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[lyudmila]

Update on table with over 6 million rows will run long time!
Here is my syntax:
select
INV_NBR,
VND_NBR,
case when isdate(PYMRECN)=1 then cast(PYMRECNDT as smalldatetime)
else null
end as PYMRECNDT,
case when isdate(INVDT)=1 then cast(INVDT as smalldatetime)
else null
end as INVDT
into test
from MyTable
This syntax worked for fine for first date, does not work for INVDT. and I cannot find reason why?

 

[lyudmila]

Error message I get "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated"

 

[SQLSister]

That means you have one or more values stored in the field that are not really dates or had a valid date that is not in the date range covered by smalldatetime (only accepts between January 1, 1900 through June 6, 2079). People put dumb stuff in date fields when they are character fields, such as typing errors like 02/30/2002 or ASAP. You probably need to clean up your data first. Terry gave you the code to find the records which can't convert.

 

[lyudmila]

Thanks to everyone who responded to my post. Pitifully SQL server has not 100% control over data. Isdate() function did not show any problem with data. I have spent few hours to figure out what is wrong. Apparently 24 rows of data had year "8999" in its value. Even when I was able to convert to datetime and have ran Isdate() function on this table, result was always 1. I had other field, which I new had problem in some rows - truncated string like "12-JAN-200" This value was converted properly with help Isdate() function (put null when date is invalid).