convert excel data from ddmmmyyyy to yyyymmdd in sql table using ssis.

http://msdn.microsoft.com/en-us/library/w2sa9yss.aspx

DateTime.ParseExact(theString, "ddMMMyyy", CultureInfo.InvariantCulture)

Thanks but I appreciate if u provide any sql or t-sql expression.

please, oh please, understand that a datetime field is NOT STORED IN A FORMAT
a datetime field is actually stored as 2 integers
(1 integer for the whole day and the other for the time of day).

it's only us humans who need to see dates in a format

for the ddMMMyyy data, is the language English? ( i.e. always Jan Feb Mar  as the MMM )

try this example on your data perhaps?

declare @dstring as varchar(20) = '01Jan2013' -- ddMMMyyyy no spaces

select convert(datetime, @dstring ,106)

I dont want to do that. The problem here is when I load data from this excel format to my sql table it is stored in wrong format. See below

Excel = 15/008/2012.(ddmmmyyyy). It goes in table = 2012-08-15 00:00:00.000 (yyyymmdd)
Above is fine but
Excel = 03/008/2012.(ddmmmyyyy). It goes in table = 2012-03-08 00:00:00.000 (yyyymmdd)
is not fine because my month as per excel = 08 and as per table its 03 which is wrong.

I can not change this format in Excel as lots of data
So I want to use some derived column expression in SSIS that converts ddmmmyyyy format to yyyymmdd format.

OK
i's the first time you have provided data to look at....
(I've never seen dates in this strange format before)

dd/mmm/yyyy (with zero padded numeric mmm)

weird

back soon I hope

correction coming

sorry, spotted some ommissions
this result:

IMPORTDTSTR DAYINT  MONTHINT YEARINT DATE_DISPLAY   TRUEDT
15/008/2012 15      8        2012    2012-08-15    August, 15 2012 00:00:00+0000
03/008/2012 3       8        2012    2012-08-03    August, 03 2012 00:00:00+0000

Select allOpen in new window

from this query:

select
        ImportDtStr
      , dayint
      , monthint
      , yearint
      , convert(varchar(10),trueDt,121) as date_display
      , truedt
from table1
cross apply (
              select convert(int,left(ImportDtStr,2))
                   , convert(int,substring(ImportDtStr,4,3))
                   , convert(int,right(ImportDtStr,4))
            ) ca1 (dayint,monthint,yearint)
cross apply (
              select dateadd(day,dayint-1,dateadd(month,monthint-1,dateadd(year,yearint-1900,0)))
            ) ca2 (trueDt)

Select allOpen in new window

see it at: http://sqlfiddle.com/#!3/41553/11

Thanks a lot for quick responses.
So you are saying that I have to Insert data of my Excel to sql table and then use select query so that I can see data in my desired format.
  That might be other way of doing it but front end wants to read from sql table directly without using or running any query and thats why I am insisting on inserting correct data in sql table at first, using any method. So I hope I am making my points clear. Thanks

>>... but I appreciate if u provide any sql or t-sql expression.
that's what I've done at your request

The core problem here is that the date string is "weird", you have to perform some special handling on it. The following "components" will provide a method. The first step is to divide the string into parts and make each an integer, then using dateadd convert those 3 integers to a datetime

   convert(int,left(ImportDtStr,2))
 , convert(int,substring(ImportDtStr,4,3))
 , convert(int,right(ImportDtStr,4))

 dateadd(day,dayint-1,dateadd(month,monthint-1,dateadd(year,yearint-1900,0)))

these can be "combined" into a longer single statement if required.

exactly where/how you choose to use this is up to you really.

Thanks not really what I want but i can not ask other question if I dont close this so closing this.