Formatting Dates

I imported data from a text file, where dates are currently in the format: 050729 to represent July 29, 2005.

How could I write a sql statment to put make this show up as 07/29/05?

Thanks

How did you do the import? What is the datatype you currently have for the column?|||It's a varchar now. I tried to convert to a date, but there must be fields somewhere with bad data because it won't let me. The problem is though that this table has over 6 million rows so it's not easy to figure out where the problem is.|||

Your value of 050729 is not clear as to which part of it is month and which part of it is day.

A quick SELECT CONVER(Datetime, '050729') returns 2005-07-2900:00:00.000.Not sure if that is what you want?

|||

The select you showed me is what I was looking for. Could I then do something like this?

update RetailSalesConvert(DateTime,InvoiceDate)

|||

First, is InvoiceDate varchar? Then it makes no sense. You could add another column with Datetime data type (Set it to null initially). Update that row with the converted value from InvoiceDate, then drop the existing column, rename the new column to old.

Here's a series of steps to follow:

--Add a new columnALTER Table RetailSalesADD COLUMN InvoiceDate2DatetimeNULLGo--Update the column with the values converted to datetimeupdate RetailSalesSet InvoiceDate2 =Convert(DateTime,InvoiceDate)GO--Drop the existing InvoiceDateALTER TABLe RetailSalesDROP COLUMN InvoiceDateGo--Rename the new column to InvoiceDatesp_Rename'InvoiceDate2','InvoiceDate'