DateTime Stored as Varchar50 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

DateTime Stored as Varchar50

Author

Topic

wcodydecker
Starting Member

3 Posts

Posted - 2012-08-23 : 19:03:43

Hello,

I have a database table with approximately 10,000,000 records.

I have a column titled [Date] within a table titled [Person].

The [Date] datatype is varchar50.

Sample data from the [Date] column is below:
8/19/2005 0:00:00
8/19/2005 0:00:00
8/19/2005 0:00:00
8/19/2005 0:00:00
8/19/2005 0:00:00
8/22/2005 0:00:00
8/30/2005 0:00:00
8/31/2005 0:00:00
9/2/2005 0:00:00
9/6/2005 0:00:00

I wish to convert this column to a datatype that will allow me to query in the following format:

SELECT [DATE]
FROM [PERSON]
WHERE [DATE] <= '10/01/2012'

I have tried the following command:

ALTER table PERSON ALTER column [DATE] datetime

However, I have insufficient hard disk space to perform the operation. Outside of additional additional hard drive space, do you have any recommendations?

Thanks in advance!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-23 : 19:29:21

Do you not have enough disk space because of the data and log file? You might try selecting this table into a new table with the proper data types. The right answer is to use the proper data type. But, if you want to do a date comparision in your predicate; you might be able to CAST the column as DATE or DATETIME in the predicate. However, that is going to be very slow.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-08-24 : 12:01:36

Option 1: add a computed column to the table - won't take up any space unless you persist the column. Since you want to search on this column you would want it persisted though. Probably will run into the same issue.

Option 2: add a new column with a better name (Date is a reserved word and shouldn't be used as a column name). Once the column is added, you can then update the column values in a batch process that allows for reuse of the log file.

Example:

Declare @rowCount int = 1;
While @rowCount > 0
Begin
Update Top(20000) dbo.Person
Set DateColumn = Cast([Date] As DATE)
Where [Date] Is Null;
Set @rowCount = @@rowcount;

-- backup the log file here, or if simple recovery model issue a checkpoint
End

The issue with this option is that you would have to run this to update the column values on a regular basis unless you change your system to use the new column. You could also implement a trigger that updates the column on insert or update.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources