Date transformation for null or NON date values 1

[gaperry]

I am trying to convert a text field to a date field based on the below structure. I have several dates that are null which I think I can deal with but I get failures for dates that are stored as NON date values. Example:99999999,etc

I would like to be able to convert the below dates and set the NON date values to NULL or something. I have tried several methods but can't seem to find the correct combination. Thanks in advance.



Order Date Request Date
1152002
2122002 4112002
2052002 7052002
1182001
3132002
3082002 4092002
11181998 9221999
11181998 5082000
11181998 2171999
11181998 7132000
11181998 11111999
11181998 6252001
11181998 11111998
11181998 7132000
11181998 11111998
11181998 11111999
11181998 5082000

 

[tlbroadbent]

Have you created a query, function or stored procedure to perform the transformation? Will you be updating the table with dates or simply selecting data and converting in the select query? Have you considered using the IsDate function to test if the char data is a valid date? It won't work for the date format you posted but you could test after adding hyphens or slashes to the strings.

IsDate('5082000') will return 0 - Is not a date
IsDate('5/08/2000') will return 1 - Is a date

You've indicated that you've handled most of the conversion thus far so you must have a proper date format. You just need to include IsDate in the query. A Case statement is probably needed.

Example: Assumes the date strings have been converted to mm/dd/yyyy format.

Select
OrderDate=
Case
When IsDate(OrderDateString)=1
Then Convert(datetime, OrderDateString, 101)
Else Null
End,
RequestDate=
Case
When IsDate(RequestDateString)=1
Then Convert(datetime, RequestDateString, 101)
Else Null
End
From YourTable

Example: Convert date strings from original format to datetime. This one does it all! Ugly isn't it.

Select
OrderDate=
Case
When IsDate(OrderDate)=1
Then Convert(datetime, OrderDate)
Else Null End,
RequestDate=
Case
When IsDate(RequestDate)=1
Then Convert(datetime, RequestDate)
Else Null End

From
(Select
--Surely someone has a simpler way to do this
OrderDate=stuff(stuff(right('00000000'+isnull(OrderDate,''),8),5,0,'/'),3,0,'/'),
RequestDate=stuff(stuff(right('00000000'+isnull(RequestDate,''),8),5,0,'/'),3,0,'/')
From YourTable) qry

If you have SQL 2000, you could write a function to perform the conversion. That would make the query much simpler.

I've hesitated to post all of this SQL because you may not need any of it. You didn't provide much info about the current process. It would have been helpful to see a query. You can use or discard any of the SQL code I've posted. Let me know if it is of any help. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[gaperry]

Terry,

Thanks for the quick response. I have been sick this last week or I would have responded sooner. The reason I did not supply you with more info is because by the time I was putting this out on the wire, well, I had tried so many things that everything was a blur. Your "UGLY" code as you called it turned out to be exactly what I was looking for. I was able to do the cleanup and nullify where needed and was then able to take the rest of the code and convert dates cleanly via ActiveX and ISDate function in my DTS package. Thanks so much for the help and next time I will include more info.

George