Need to Add year using Dateadd function

• r_prasanna82

  Mr or Mrs. 500

  Points: 516

  More actions

  September 15, 2009 at 11:47 am

  #135023

  Hi All,

  I am transferring data from Oracle to sybase using ssis. For one specific column i am facing issue. i.e. In front end users entered the date as 22/10/05 (mm/dd/yyyy - instead of typing 2005 they have typed 05) in oracle the data has been entered as 22/10/0005. So when i transfer data from oracle to sybe its not accepting the year.

  So wat i am planning is to add 2000 with the year alone. I am not sure whether it works. But i need your suggestions.

  I tried with teh derived column in ssis between the source (oracle) and destination (Sybase) and tried teh dateadd function for that specific column. But somehow i ma missing something in the expression so i am not able to get it correct.

  The format from the source is mm/dd/yyyy time.

  Please find the error out i am facing.

  "Error occurred column_date =10/26/0005 12:00:00.000 AM"

  Please do the needful.

  Kindly let me know how to go about the expression to add 2000 with this. So that i can proceed further.

  Thanks in advance

  Thanks!

• J-F Bergeron

  SSCrazy Eights

  Points: 8593

  More actions

  September 15, 2009 at 11:55 am

  #1054008

  What I would do is add a derived column using only the 2 digits of the year.

  In example the dates 22/10/2005 or 22/10/0005 would be trimmed as 22/10/05.

  Then would be able to cast the output string as datetime, and handle both scenarios. SQL Server will handle the 2 digits date, and interpret it as 2005.

  Because, if you do add 2000 to the year everytime, it might result in having a date as of 22/10/4005...

  This is what I would do. There might be better options.

  Cheers,

  J-F

• r_prasanna82

  Mr or Mrs. 500

  Points: 516

  More actions

  September 15, 2009 at 11:58 am

  #1054013

  Thanks for your reply.

  This one sounds better than mine. Is there any specific function in ssis where i can change this yyyy to yy. Sorry for being so dump. I am new to this area. Could you please help me in this regard.

  Thanks

• J-F Bergeron

  SSCrazy Eights

  Points: 8593

  More actions

  September 15, 2009 at 12:04 pm

  #1054017

  You can use the Derived column transformation, and use a substring function with your date field.

  Something like this

  substring([DateString],1,6) + substring([DateString],9,2)

  Add it as a new column to check the output, and do the data validation, then you can cast it as a Datetime value [DT_Date], or something like that.

  When you're sure your data is correct, you can replace the Date Column.

  Hope that helps,

  Edit: Removed the select because that is not TSQL :hehe:

  Cheers,

  J-F

• r_prasanna82

  Mr or Mrs. 500

  Points: 516

  More actions

  September 15, 2009 at 12:51 pm

  #1054044

  Thank you so much for the reply. I will work on it and will let you know the output.

  If you have some other option also please let me know.

  Thanks

• r_prasanna82

  Mr or Mrs. 500

  Points: 516

  More actions

  September 15, 2009 at 1:35 pm

  #1054079

  Hi

  I tried the same but my bad its not working. It says substring does not support the data type DT_TIMESTAMP.

  I tried like

  (DB_DATE)(SUBSTRING(columnname, 6,4) + '-' + SUBSTRING(columnname, 4,2) + '-' + SUBSTRING(columnname, 1,2) )

  Please correct me if i ma wrong.

  Thanks

• mcha7628

  SSC Veteran

  Points: 265

  More actions

  September 15, 2009 at 1:56 pm

  #1054096

  r_prasanna82 (9/15/2009)

  ---

  The format from the source is mm/dd/yyyy time.

  YYYY-MM-DD

  (DB_DATE)(SUBSTRING(columnname, 7,4) + '-' + SUBSTRING(columnname, 1,2) + '-' + SUBSTRING(columnname, 4,2) )

• J-F Bergeron

  SSCrazy Eights

  Points: 8593

  More actions

  September 16, 2009 at 7:50 am

  #1054439

  r_prasanna82 (9/15/2009)

  ---

  Hi

  I tried the same but my bad its not working. It says substring does not support the data type DT_TIMESTAMP.

  I tried like

  (DB_DATE)(SUBSTRING(columnname, 6,4) + '-' + SUBSTRING(columnname, 4,2) + '-' + SUBSTRING(columnname, 1,2) )

  Please correct me if i ma wrong.

  Thanks

  Is it possible your columnName is already in a DT_TimeStamp format? Substring is used on a string (varchar). That might explain why you are getting this error.

  Cheers,

  J-F

• r_prasanna82

  Mr or Mrs. 500

  Points: 516

  More actions

  September 16, 2009 at 9:22 am

  #1054533

  Hi All,

  First of all for the question yes already its in DT_TimeStamp format

  Thanks for the info. Actually i tried with a CAST query and it worked for me.

  The query i used was TO_DATE REPLACE(CASTcolname as char, '000', '200'))

  This one changed the year 000 to 200 and as of now the issue got resolved. Now i will try your solution since that is the best one for long term go. I spent too much time writing the query using the DATEADD hence i tried the other way around.

  Thanks anyway will try ur idea as well and will let u know for any help.

  Thanks so much for all ur help.

  Thanks