Convert varchar(20) to a date

Whah
Whah used Ask the Experts™
on
Hi All,

Totally new with MSSQL...I have to make a change to 1.3 million records. I need to change field2 which is a varchar(20), to a date field which should look like this mm/dd/yyyy.

I looked on different sites, but I'm not entirely sure "exactly" how to do this.

I've attached a file. Any help would be greatly appreciated.
sqlscreenshot.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brian CroweDatabase Administrator
Top Expert 2005
Commented:
The first thing you need to understand is that dates aren't stored as "mm/dd/yyyy".  They are stored as a couple of integers where one represents the number of days from a certain date and the other represents the amount of time since midnight (in .003 second intervals).

That date should convert implicitly to a date if they are all formated mm/dd/yy.

ALTER TABLE <your table name>
ADD field2date DATETIME NULL

UPDATE <your table name>
SET field2date = field2

If you get an error then you have some mis-formatted field2 data

As a matter of fact if all of the field2 values are formatted as shown then you can just change the type of the column and SQL will take care of the conversion itself.

ALTER TABLE <your table name>
ALTER COLUMN field2 DATETIME

Author

Commented:
Yes, I am getting errors. Not knowing how something is "mis-formatted" is part of my problem.

Thank you.
Database Administrator
Top Expert 2005
Commented:
SELECT field2
FROM myTable
WHERE ISDATE(field2) = 0

Author

Commented:
Thank you BriCrowe.
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
ALTER myTable ADD filed2date DATETIME;

UPDATE myTable SET filed2date = CAST(filed2 as datetime);

ALTER myTable DROP COLUMN filed2;

ALTER myTable ADD filed2 DATETIME;

UPDATE myTable SET filed2 = filed2date;

ALTER myTable DROP COLUMN filed2date;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial