asked on

Catch invalid values when converting number to date

I am using SQL Server 2008

I have a numeric field in the following format: yyyymmdd

I wish to convert to date in this format: mm/dd/yyyy.

However, sometimes the field contains values that cannot be converted to dates. (examples: 0, 99999999). I wish to have these values come through as empty value.

How can I accomplish this?

Thanks for any help.

Vitor Montalvão

You can use ISDATE to verify if the value is a date:

UPDATE YourTableName
SET NumericFieldName = NULL
WHERE ISDATE(CAST(NumericFieldName AS VARCHAR))=0

Open in new window

mak345

ASKER

I don't want to update the table, I just want to create a query that will result in an empty value where applicable. The value in the table is ok.

Vitor Montalvão

Use the same idea:

SELECT Field1, Field2, FieldN, 
                         CASE ISDATE(CAST(NumericFieldName AS VARCHAR))
				WHEN 0 THEN ''
				ELSE CAST(CAST(NumericFieldName  AS VARCHAR) AS DATE)
			END MyDate
FROM YourTableName

Open in new window

mak345

ASKER

Thanks, but I pasted your code in and it still results in 1900-01-01, and not empty value.

CASE ISDATE(CAST(MaturityDate AS VARCHAR)) WHEN 0 THEN '' ELSE CAST(CAST(MaturityDate AS VARCHAR) AS DATE) END

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mak345

ASKER

PERFECT!!!

THANKS!