6 Replies Latest reply: May 20, 2010 7:01 AM by Sri24784 RSS

    Transform datefield

      Buildning my first application and have run into problems with an excelfield that i have as source data. The format is yyyy-mm-dd hh.mm.ss and i want to transform it into year ([Entrydate]) as year in the Load statement but dont get no data from it (also the same tranformation with month).

      How can i transform it, either in the load or in the designer?

        • Transform datefield
          Sri24784

          Hi,

          Can you pl upload your sample data here, so that it will be easy for us to suggest a solution.

          -Peterson

          • Transform datefield

            Source field looks like this:

             

            2010-01-12 10:22:51
            2010-01-19 10:24:55
            2010-01-15 11:59:26


            Load statement looks like this:

             

             

             

             



             

             

             

             

             

             

             

             

             

             

             

             

             

             

             

             

             

             



             

             

            LOAD

             

             

            Company,
            [Reported by],
            [Call id],
            [External id],
            Status,
            Priority,
            [Int. Ref. Id],
            Closed,
            [Enter Hours],
            Left ([Enter Date], 10),
            Year ([Enter Date]) as År,
            Month ([Enter Date]) as Månad,
            [Work Hours]
            FROM
            [\\xd40201.u-dom1.u-ssi.net\sema20106user$\H15992\work\QV\DATA\Logicauppföljning\E ON Jan 2010.xls]
            (biff, embedded labels, table is [FU 1001$])
            Where [Company] = 'E.ON ELNÄT SVERIGE AB';



              • Transform datefield
                Anatoly Pyatygo

                Hello try this:

                LOAD ...

                Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss') as EnterDate,

                year(Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss')) as Year

                FROm...

                  • Transform datefield

                    Thanks, didn't help though. Mayby I took you too literate?

                    pasted in error statement that showed while loading:

                    Okänd sats <=Unknown ?
                    Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss') as EnterDate,

                    year(Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss')) as Year


                    Januari:
                    LOAD Company,
                    [Reported by],
                    [Call id],
                    [External id],
                    Status,
                    Priority,
                    [Int. Ref. Id],
                    Closed,
                    [Enter Hours],
                    Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss') as EnterDate,
                    Year (Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss')) as Year,
                    Month (Date#([Enter Date], 'YYYY-MM-DD hh:mm:ss')) as Month,

                    [Work Hours]
                    FROM
                    [\\xd40201.u-dom1.u-ssi.net\sema20106user$\H15992\work\QV\DATA\Logicauppföljning\E ON Jan 2010.xls]
                    (biff, embedded labels, table is [FU 1001$])
                    Where [Company] = 'E.ON ELNÄT SVERIGE AB'