Recently I was reading a discussion where someone was trying to write a query they'd been asked to write. While working on the query, they noticed that there was data listed with a date of 2/29/2007. The person realized this date was invalid, and likely it should be changed to 2/28/2007.
Or should it?
As a DBA, we are responsible for maintaining the integrity of data. We keep it secure from changes, we protect it, and we want to ensure that the data, which was put into the system, is what someone can retrieve later. However mistakes happen, and we do get bad data, so we also get charged with "cleaning" up data and fixing mistakes. That’s a dangerous combination in my mind.
So what's the mistake here? Obviously 2/29/2007 isn't a valid date and SQL Server is smart enough to know this. Trying to insert this into a date field returns an error. But what if this is in a character field? Is this supposed to be 2/28/2007 or is it 2/29/2008? Is the day wrong, or the year? It's not necessarily something you can easily figure out.
I'm sure none of you would store dates as characters, but it does happen. Or you might have other data that appears obviously wrong from your understanding of the information it represents. However I caution all DBAs and developers. Don't change data lightly. Check with someone that is responsible for the data, or its use, and confirm with them that you are making the correct changes.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.
You can also follow Steve Jones on Twitter:
or now on iTunes!
- Windows Media Podcast - 20.3MB WMV
- iPod Video Podcast - 15.3MB MP4
- MP3 Audio Podcast - 3.5MB MP3
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.