Transforming Weird Dates 2

[JohnBates]

Hi experts,


I guess we've all had a bad date or two

I have a 4 million row table, with dates in varchar for now.

Some are formatted 07/6/1999, others as 07/6/99 and I have even found a non-date 01/0/2000


**What is a good source of information, with lots of examples for transforming dates into a value that can be stored as datetime or smalldate ?**

Most books have a small section on this, but I think an entire book could be written on this subject.

Thanks, John

 

[JayKusch]

What is your earliest date you have, or can you detemine that?

Thanks

J. Kusch

 

[Krickles]

I don't know of a book that will help you, but you can find a lot about the conversion types through Query Analyzer's T-SQL help. Go to SEARCH, enter CAST CONVERT, sort alphabetically, and click on CAST and CONVERT. You'll see the options available.

To help you get started, try the following:

STEP 1

Use this to count the total number of invalid dates.

[tt]SELECT * from

where [field] like '%/0/%'[/tt]

Note the total records.

STEP 2

We'll use the REPLACE function to identify the field, identify what to replace, and indicate what to use as the replacement. Since we aren't updating, this gives you the chance to see what the outcome will look like.

[tt]SELECT REPLACE([field], '/0/', '/5/')
FROM

WHERE [field] like '%/0/%'[/tt]

The total returned should equal the total from step one. This will also let you see how the date will be changed for the invalid entries. Since you will have to make up a day anyway, you can stuff any number you want in place of the 5.

STEP 3

This performs the actual update to the table so we can handle the invalid date entries.

[tt]UPDATE

SET [field] = REPLACE([field], '/0/', '/5/')
FROM

WHERE [field] like '%/0/%'[/tt]

Be sure to update the day to the actual number you want as noted in step 2.

STEP 4

This updates the table by converting the strings to small datetimes.

[tt]UPDATE

SET [field] = cast([field] as smalldatetime)
FROM

[/tt]

I hope this helps get you going in the right direction.


Krickles | 1.6180

 

[Krickles]

Also check out:

http://www.tek-tips.com/faqs.cfm?spid=183&sfid=4191

Krickles | 1.6180

 

[JayKusch]

Hey John ... If you need a quick and dirty I have it for you. Have ran into similiar issues with bad dates in a ton of records. Last batch I had to do was over 500 Million records for a consolidates Player Tracking project I was on. We had everything from B-day, Anniversary, Drivers Lic renew, player last login/log out date and so on. Over 30 dates needed this clean up. Here is what I did.

First the table w/ the records needs an Unique Integer Key, which was on some tables but not others. The ones that did not have it, I create a field called ID and set it as an identity.

I then added another field at the end of the record called GoodRec as a bit. Now we create the dreaded WHILE loop to set the records w/ a good date and leave the bad ones w/ NULLs in the for easy identification.

For ease of explaining I am going to name the table MyTable and the DateTime field I am interogating called MyField.

DECLARE @NextRec Int
DECLARE @MaxRec	 Int

SET @NextRec	= 1
SET @MaxRec	= (SELECT MAX(ID) FROM MyTable)

WHILE @NextRec <= @MaxRec
  BEGIN
	UPDATE MyTable
		SET GoodRec = 1
	WHERE CONVERT(DateTime,MyField) > '1900-01-01'
	AND ID = @NextRec

	SET @NextRec = @NextRec + 1
  END

Bingo! All the bad dates have a NULL in the GoodRec field.

When you are done, drop the GoodRec field and maybe the ID field if you had to create one!

Enjoy!

Thanks

J. Kusch

 

[dbomrrsm]

J

Why do you need a WHILE Loop - wouldnt the below work as well ?

UPDATE MyTable
SET GoodRec = 1
WHERE CONVERT(DateTime,MyField) > '1900-01-01'

I ask out of curiosity rather than suggesting the WHILE Loop is not needed.

Cheers

DBomrrsm

 

[JayKusch]

I "think" when I tried it w/out the WHILE loop it failed the whole UPDATE statement because of the bad dates that were in the set I was working with. I may be wrong ....

Thanks

J. Kusch

 

[dbomrrsm]

J

You are correct - if you do not have the WHILE loop as soon as the update hits a bad date it returns:

Server: Msg 242, Level 16, State 3, Line 9
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

and the whole update fails - If you have the WHILE loop it just fails the row that is a bad date - giving the above message just for those rows.

Thanks

DBomrrsm