data conversion

19 Apr 2007 8:48 AM

Tiffany

Hi

i have a problem converting a column which contains the date to the date
type i want ie mm/dd/yyyy

csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
from the csv source file to the table in sql, it automatically tranformed the
data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
mm/dd/yyyy format.

Kindly advise

19 Apr 2007 9:05 AM

Uri Dimant

Tiffany
declare @dt varchar(20)
set @dt='Feb-06'

select convert(varchar(20),dt,101)
from
(
select cast('20'+right(@dt,2)+case when left(@dt,3)='Feb' then '02' end+'01'
as datetime)as dt
) as d

Show quoteHide quote

"Tiffany" <Tiff***@discussions.microsoft.com> wrote in message
news:1E870F52-8CCB-44DF-B619-BD68B0C49AA0@microsoft.com...
> Hi
>
> i have a problem converting a column which contains the date to the date
> type i want ie mm/dd/yyyy
>
> csv source file is y-MMM (e.g. 6-Feb is Feb-2006). When i import the data
> from the csv source file to the table in sql, it automatically tranformed
> the
> data to Feb-06. The data type used is varchar. How can i convert Feb-06 to
> mm/dd/yyyy format.
>
> Kindly advise
>
>