Topic
  • 11 replies
  • Latest Post - ‏2011-10-13T21:19:38Z by SystemAdmin
SystemAdmin
SystemAdmin
2105 Posts

Pinned topic Convert YYYYMMDD to DATE

‏2004-12-22T10:33:56Z |
I'm loading from a staging table (all varchar2 columns) and need to certain fields into an Oracle date

I've seen the StringToTimestamp function but I cant seem to set it right and cant find any info.

Any help, I may also have some YYYYMMDD HH24:MI text fields too, but I guess once I know how to work it for YYYYMMDD, I'll be able to figure the rest out.
Updated on 2011-10-13T21:19:38Z at 2011-10-13T21:19:38Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Convert YYYYMMDD to DATE

    ‏2004-12-22T11:08:38Z  
    Well I cracked it tried every combination of the format string part and came up with:

    StringToTimestamp(DSLink4.text)

    Job now validates, but when I peek at the data (not tried against Oracle table I see "**************")

    Hopefully it'll work when I try to load into a table.
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Convert YYYYMMDD to DATE

    ‏2004-12-22T11:18:41Z  
    Well the row still does not load into the Oracle database.

    Any help on what I've done wrong will be appreciated!

    The error in the log file is

    ORA-01841: (full) year must be between -4713 and +9999, and not be 0

    The one record text field is 20041215

    So I'm lost as to whats happening

    The entry in the bad file is:

    A *******************

    The A being a value into a VARCHAR2 field so thats okay. Why do I only get stars?
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Convert YYYYMMDD to DATE

    ‏2004-12-22T11:40:16Z  
    I would bet it wants the dates in YYYY-MM-DD format. The simplest way to get that would be to substring your original field apart and put it back together with the appropriate delimiter:

    code:1:68dd93da17YourField[1,4]:"-":YourField[5,2]:"-":YourField[7,2][/code:1:68dd93da17]
    Of course, that's a Server answer and may not be appropriate in a PX job.
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Convert YYYYMMDD to DATE

    ‏2004-12-22T11:54:09Z  
    Nope tried that and it still has the same issue with the * symbol instead of my date timestamp.
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Convert YYYYMMDD to DATE

    ‏2004-12-22T14:42:38Z  
    Jonathan,

    My recommendation is that you get a native insert to work in ORACLE (like through SQL+Plus or TOAD), then use what you learn to determine what you need to pass to the Oracle stage for insert/ update.

    For instance, if you get an insert similar to this to work:

    insert into MYTABLE(INTKEY, DATE_ONE, TIMESTAMP_ONE)
    values
    (12345678901234567890,
    TO_DATE('2004-1-1 0:0:0', 'YYYY-MM-DD HH24:MI:SS'),
    TO_DATE( '2004-1-18 18:41:48', 'YYYY-MM-DD HH24:MI:SS'));

    Then you know what format the column data should be in when it leaves the transformer and enters the OraOCI stage.

    From there you should be able to let the tool generate an insert, then change it to a "user defined SQL" and modify it similar to the following using the to_date Oracle function and substitute the actual reference to the variable instead of the literal date value:

    insert into MYTABLE(INTKEY, DATE_ONE, TIMESTAMP_ONE)
    values
    (12345678901234567890,
    TO_DATE(link1.columnname1, 'YYYY-MM-DD HH24:MI:SS'),
    TO_DATE( link1.columnname2', 'YYYY-MM-DD HH24:MI:SS'))

    Obviously this is just one suggestion that should get you close. (I'm sure the posters who want to drive up their "number of posts" and promote their businesses will have to respond to this with a much better solution to further prove how superior their intelligence is to the rest of us, but hey, if you get something out of this that helps you be successful, that's all that really matters!)

    Good luck and Merry Christmas!
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Convert YYYYMMDD to DATE

    ‏2004-12-22T15:28:45Z  
    Obviously this is just one suggestion that should get you close. (I'm sure the posters who want to drive up their "number of posts" and promote their businesses will have to respond to this with a much better solution to further prove how superior their intelligence is to the rest of us, but hey, if you get something out of this that helps you be successful, that's all that really matters!)[/quote:b8df533956]
    That's an... interesting... comment to make.

    Jonathan, it would really help to know what stage you are using and if this is a Server or PX job. If you are using an b:b8df533956OCI[/b:b8df533956] stage then there is a specific format the stage is expecting Date or Timestamp data types to be in and that is documented in the pdfs that come with the product - for example, the b:b8df533956Oraoci9.pdf[/b:b8df533956] for the OCI9 stage.

    The OCI stages automatically use the b:b8df533956TO_DATE[/b:b8df533956] function with the format that Rick mentions, which is why the data needs to be preformatted properly.

    I'd also be curious exactly i:b8df533956where[/i:b8df533956] you are seeing these stars in your field - when doing a 'View Data' in DataStage or is this in something like SQL*Plus or TOAD? It sounds like you may not have your displayed field size set high enough to display the entire contents of the field and there may not actually be anything wrong with your data.
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Convert YYYYMMDD to DATE

    ‏2004-12-23T03:51:50Z  
    Okay this is a P/X job I'm looking at, I've also created a second P/X job to tinker with on this problem which has the same logic but on the output I have a peek, dataset as well as trying to load into the table.

    If I look in the DataStage log I see this line:

    Peek_2,0: text:******************* row_id: 1.

    If I look in the log.bad file on the Server I see a very similar error

    Now I have my target field as TimeStamp Length 38 as this is what the field is set to when you import the definations from Oracle using the Manager tool.

    To answer your suggestion Rick of just using TO_DATE in the user-defined SQL. I have thought of that, but it's my last resort, its not really my optimum solution, as I'm trying to validate the data prior to the load stage, but I'll keep it in mind.
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Convert YYYYMMDD to DATE

    ‏2005-01-04T15:05:04Z  
    To successfully convert to a timestamp you must provide a TIME portion as well as a date portion. You can provide a literal for the time if you don't have a 'real' one, I usually use midnight.

    HTH,
    D
    ************************************************************************
    • Danny Owen * E-Mail: patrick.owen@ascentialsoftware.com *
    • 5817 Southwind Dr * Title: Consultant *
    • NLR, AR 72118 * WWW: www.ascentialsoftware.com *
    • * Phone: (248) 346-8867 (Mobile) *
    ************************************************************************
    #include<stdio.h>#define WQ fprintf(ptr,"%s",a) main(){int **ptr;char a[
    100]={109,97,105,110,40,41,123,99,104,97,114,32,42,99,61,34,109,97,105,1
    10,40,41,123,99,104,97,114,32,42,99,61,37,99,37,115,37,99,59,112,114,105
    ,110,116,102,40,99,44,51,52,44,99,44,51,52,41,59,125,34,59,112,114,105,1
    10,116,102,40,99,44,51,52,44,99,44,51,52,41,59,125,10,0};ptr=stderr;WQ;}
    ************************************************************************
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Convert YYYYMMDD to DATE

    ‏2005-01-12T04:17:35Z  
    Thanks for the answer, sorry I havent checked back since after Xmas. Eventually came up with that same answer, glad its the correct way.
  • nima511
    nima511
    1 Post

    Re: Convert YYYYMMDD to DATE

    ‏2010-05-06T03:21:59Z  
    Thanks for the answer, sorry I havent checked back since after Xmas. Eventually came up with that same answer, glad its the correct way.
    I am getting the same error. I have an input of Varchar and I need the output to be a timestamp. My output is a Sequential File and I am seeing "*******************".

    How did you resolve it?
  • SystemAdmin
    SystemAdmin
    2105 Posts

    Re: Convert YYYYMMDD to DATE

    ‏2011-10-13T21:19:38Z  
    • nima511
    • ‏2010-05-06T03:21:59Z
    I am getting the same error. I have an input of Varchar and I need the output to be a timestamp. My output is a Sequential File and I am seeing "*******************".

    How did you resolve it?
    hi All,

    I have source record is as mm-dd-yyyy hh:nn:ss AM as varchar. I wan to load it into SQL DB as yyyy-mm-dd hh:nn:ss as timestamp...when i am trying **************** is loading into the target .Also I placed peak stage. In that too I am not able to see record rather I am able to see ***************.

    Regards
    Abinash