Hi,

I have a field in my table called reportdate varchar (50). I import a csv file to that table that receives dates in 2 forms for example
09122005 and 9122005. I need to convert Reportdate to numeric and then to date in the following format : 09/12/2005.
I am not sure how to accomplisht that.
Any help would be appreciated.
Thanks

Not clear on objective to convert date into NUMERIC first and then in the format dd/mm/yyyy.......why not directly to dd/mm/yyyy format?

This may be the solution you are looking for.....

1. Import the raw data as it is in the table.

2. UPDATE <TableName>
   SET ReportDate='0'+ReportDate
   WHERE LEN(REportDate)=7

3. UPDATE <TableName>
   SET ReportDate = LEFT(ReportDate,2)+'/'+SUBSTRING(ReportDate,3,2)+'/'+RIGHT(ReportDate,4)

Cheers,
Pooja Falor

Thanks, that worked great.

I tried this with a text field that I wanted to convert to YYYYMMDD format and got the message: String or binary data would be truncated. Any clue why?

Used the same code as above except that the year portion is LEFT instead of RIGHT.

you will need to look at your data and see the different types of dates that people entered.

For example if you have the following
YYYYMMDD
DDMMYYYY
MMDDYYYY
all in the same field you can't fix it easily with one statement.

Try changing the data a few rows at a time till you come across the field that has the issue. So for example only do it where the first four characters are 2000 so you know thats the year, then piece by piece do the rest. You probably have some bad data that will error out with the valid commands given to you earlier.