SSIS Date Conversion

• A Little Help Please

  SSCarpal Tunnel

  Points: 4764

  More actions

  November 25, 2008 at 9:07 am

  #132519

  Environment:

  SQL 2005 DB

  SSIS Package

  Flat file (csv)

  Situation:

  FlatFile example row:

  "LocalSales","S","N.L","24 NOV 2008"

  Problem:

  The problem is with the date column, as you can see the date in the file is "24 NOV 2008", the database column is set as (Smalldatetime,Null).

  My other file which has the date as 24-NOV-08 works file with the same column type.

  Therefore the only differance I can see is that its missing the - between the dates.

  Do I need to use Derived Column to convert the column?

  Any help is appreciated!!!!!!!!!!!!!!!!

• shmuel_v

  Old Hand

  Points: 354

  More actions

  November 27, 2008 at 9:00 am

  #904387

  Hi,

  The space in the date value is not the problem

  You just need to convert the date to smalldatetime.

  example :

  create table #tmp(

  a varchar(20),

  b char(1),

  c varchar(3),

  d smalldatetime)

  insert into #tmp

  values ('LocalSales','S','N.L',

  convert(smalldatetime,'24 NOV 2008',103))