September 23, 2008 at 12:06 pm
I have a table with an excess of 100million records. There are a number of date fields that are 'varchar' datatype. How hard is it to convert them to 'datetime'.
1.What is the process of doing it?
2.When it is done what happens to the existing yyyymmdd format data already in the table?
3.During the proces is the data accessible or should it be done in the maintenance window?
Thanks in Advance...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 23, 2008 at 1:38 pm
There are several ways to do this, depending on your exact needs.
On the format, datetime data is stored as a pair of 4-byte integers, one for the day, one for the milliseconds (approximately), so the format goes away, but it doesn't matter.
Not only would I do this "during a maintenance window", but I'd do it as I would any other code roll-out. I'd do it in a dev environment first, find out how well it goes, then test the heck out of it with the applications that depend on it, then roll it to QA for further testing, then, finally, I'd take it to production during one of my usual releases.
The place I'd start is by querying for rows where a simple conversion can't be done. Use IsDate() to find those. (Books Online has details on the IsDate() function.) What you do next depends on what that finds.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 23, 2008 at 1:48 pm
The need is felt with an upcoming application that needs the date fields to be datetime.
Now when I check the table proprties there are many a date fields that are stored as varchar(8) datatype.
There has been a talk if we could revert all those varchars to datetime. For me to answer this, I should know what all the things SQL does when we change the data type.
One thing I know is when you said that the format would go, but how does this affect performance wise.
If this is something that does not harm such a big table then we do have dev,qa and test environments we could start off with it and then do it on production with a release.
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 23, 2008 at 2:00 pm
I see that the isDate() when ran checks for each and every record on that column and comes back with either a 1 or a 0.
In my case most of them are 1 which is good.
But if I were to modify the column datatype, will the data be accessible while it is being done. Because when I go to designer mode it says 'saving changes to tables with large data takes time during which data in the table will not be accessible'
What is the sensible way of doing it then? Or should it be left as it is..........
Thanks Again!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 24, 2008 at 1:17 am
"In my case most of them are 1 which is good."
I think you will have to make sure that ALL the dates are valid before proceeding.
September 24, 2008 at 1:43 am
Also refer http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx
Failing to plan is Planning to fail
September 24, 2008 at 5:02 am
You can add a column of type DateTime to the table. Try to convert the dates from the varchar column to dates in the datetime column. You can do this with small batches if it's not allowed to hit overall performance.
Check for any failed conversions and adjust them manually. Finally (during a maintenance window) remove the varchar column and rename the datetime column to the same name as the original varchar column.
September 24, 2008 at 6:52 am
Adding a datetime column and doing the convert and checking for failures sounds really good. But when I have to do it for 8 columns I cant really say what the effect is going to be because they hold humongous legay data and all the sites are currently active. If at all this is the way, can I propose it to be done, if not what is the level of risk we are talking about here?
Thanks!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 24, 2008 at 7:35 am
If you have to change several columns, try to split up the changes in portions you can handle. Begin with adding 1 (empty) column. Fill this column with converted datetime values in batches of 1000 rows (or how many you system can handle).
After converting all values from 1 column to a new column, change the name of the original column to {name}-old and the new column to {name}. If it doesn't work rename the column back to what they were and no harm done. If it does work, you can remove the original column.
And then start again for the conversion of the next column. This is giving you a lot of work, but the risk is minimized.
Of course: best way to do this is to do it on a test-environment, like all other changes. In a test-environment you can change everything at once and start testing. In a life-environment is data-loss far more important compared to extra work for the DBA.
September 24, 2008 at 7:48 am
The_SQL_DBA (9/24/2008) if not what is the level of risk we are talking about here?
Your environment is working at this moment, so you could leave it as is. But invalid datetime values can be entered in a varchar type. Especially if the application is not checking the input. When trying to enter an invalid datetime in a datetime type an error is generated.
In the end your business requirements has to tell you what is allowed and what the risks are.
September 24, 2008 at 7:56 am
There is another side to this. If the date fields are currently varchar(8) with the dates formatted yyyymmdd, what effect will changing the data type to datetime have on the application? If it is expecting character data, this change will also affect the application as well.
😎
September 24, 2008 at 8:13 am
Lynn Pettis (9/24/2008)
There is another side to this. If the date fields are currently varchar(8) with the dates formatted yyyymmdd, what effect will changing the data type to datetime have on the application? If it is expecting character data, this change will also affect the application as well.😎
Dates should be stored in proper DATETIME datatype
If application expects character dates, it should be changed to accept proper dates 🙂
Failing to plan is Planning to fail
September 24, 2008 at 8:32 am
Madhivanan (9/24/2008)
Lynn Pettis (9/24/2008)
There is another side to this. If the date fields are currently varchar(8) with the dates formatted yyyymmdd, what effect will changing the data type to datetime have on the application? If it is expecting character data, this change will also affect the application as well.😎
Dates should be stored in proper DATETIME datatype
If application expects character dates, it should be changed to accept proper dates 🙂
I agree, but it needs to be considered along with the change to the database.
😎
September 24, 2008 at 8:57 am
Lynn is right about applications. Another thing to consider is not just front-end code, but procs. Are they doing string manipulations on the varchar dates, and will fail when they try to do the same on datetime? There's a big difference between "substring(MyDateString, 6, 2)" and "datepart(day, MyDateDatetime)".
This is a potentially major change in the database, and needs to be approached carefully.
The first thing to do is work out how to fix all the rows that have 0 for IsDate(). Can that be done by some sort of pattern-matching code, or will it have to be fixed one-at-a-time by a DBA? You'll need to look at them and see. If, for example, they can't be converted implicitly, because of something that can be fixed with a single update statement, you're fine. If there are rows that have "Lunch on Tuesday" instead of a date, you may not be able to fix that programmatically.
Untill you've assessed (a) what effects this change will have on all code that touches these columns, and (b) how much effort it will take to clean up the non-date data, you really can't even begin to plan the minor details of this project.
Do those two things first.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 29, 2008 at 8:36 pm
When I put forth the idea and the risk involved in doing this conversion to management, they said they were better off without doing it. The reason being the hundreds of client side applications that hit the DB use the current format of date. They cannot bring it all together and be assured what the change they are trying to make will keep things in place...so we have scrapped this idea for we don't know what the effect could be....
thanks again guys!!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply