April 24, 2009 at 5:00 am
Hi All,
I have a question in date conversion is ssis. I am newbie in ssis. My task is, do insert the data to sql table from the flat file source. i have a datetime field in that flatfile. I need to insert that field as datepart. How do i do this. I dont know how to convert the datetime to datepart. Whether is this possible?
Thanks in advance
prasanna
April 24, 2009 at 5:42 am
Please post a sample of the file.
May your fish always be bigger
Than the holes on your net.
April 26, 2009 at 6:07 am
You may be able to use the Data Conversion transformation to write an expression that gets you the datepart of the date. Or failing that, how strong is your .NET? Has you could use the Script Component transformation to write your own function that does this for you.
April 28, 2009 at 12:03 am
Sorry i'm not well in .Net. Could you help me to solve this issue.
April 28, 2009 at 12:06 am
Unless you post sample data, as ruizeddy already requested above, no one will be able to give you detailed help.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 28, 2009 at 12:21 am
id name datefield
1001 USA 2004-09-03 12:00:18
1002 SIN 2004-05-03 11:00:18
1003 CAN 2004-03-05 11:06:27
1004 HONG 2004-08-06 02:03:58
1005 IND 2004-09-07 09:00:29
Now i would like to convert the date format into
datefield
Sep 03 2004
May 03 2004
March 03 2004
August 06 2004
Sep 07 2004
could anyone help me to resolve this?
April 28, 2009 at 1:00 am
If your flat file date format is already in the format
YYYY-MM-DD HH:MM
it is in perfect shape for loading direct into a SQL Server datetime field and you should be able to stuff it straight in there, losing the time component en route, as the following code shows:
declare @strDate as varchar(20), @Date as datetime
Set @strDate = '2004-09-03 12:00:18'
Set @Date = Left(@strDate, 11) + '00:00:00.000'
Select @strDate [StringDate], @Date [Date]
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 28, 2009 at 1:31 am
Where do i put this code? whether data conversion or derived column please let me clear
April 28, 2009 at 1:55 am
Derived column works well.
Create a new column with a formula like this:
[SourceTextDateTimeField]
and give it a datatype of DT_DBDATE. This casts the string to a date and strips away the time component all in one go.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 28, 2009 at 11:34 am
Another option is to execute the data flow task without any conversions, then execute an update afterwards, to update the data as such:
update MYTABLE set [DATE_FIELD] = CONVERT(VARCHAR(12), [DATE_FIELD], 107)
This would work, if the data type for the date field is not a date type.
May your fish always be bigger
Than the holes on your net.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply