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.
mak345Asked:
Who is Participating?
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
That's because the column now is date. What about returning NULL instead?
SELECT Field1, Field2, FieldN, 
                         CASE ISDATE(CAST(NumericFieldName AS VARCHAR))
				WHEN 0 THEN NULL
				ELSE CAST(CAST(NumericFieldName  AS VARCHAR) AS DATE)
			END MyDate
FROM YourTableName

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
mak345Author Commented:
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.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
mak345Author Commented:
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
0
 
mak345Author Commented:
PERFECT!!!

THANKS!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.