>>>>>We've got a data file that has dates in 'dd-mm-yyyy (time)' format (and coming in as a string) and need them in 'standard amurican' 'mm-dd-yyyy (time)'.
>>>>>
>>>>>What's the best way to do this conversion?
>>>>
>>>>Are you looking to do the conversion in SSIS or something? How is the file getting loaded?
>>>>
>>>>Or is it stored in the table as a string in that format?
>>>
>>>Stored as a string
>>
>>Try
>>
>>
>>DECLARE @t TABLE (dt VARCHAR(30))
>>
>>INSERT INTO @t (dt)
>>VALUES ('30-10-2014')
>> ,('13-12-2013')
>>
>>SELECT dt
>> ,convert(DATE, dt, 105) AS [Real Date]
>>FROM @t
>
>Same "date and/or time from character string" error
Post all samples in that column and change it to be
case when ISDATE(dt) = 0 then NULL else convert(date, dt, 105) end as [Real Date]
The above assumes consistent format dd-mm-yyyy
If it's not broken, fix it until it is.
My Blog