14 Replies Latest reply: Sep 23, 2014 11:57 AM by Marco Wedel RSS

    Date convertion

      Hi,


      I have a date format like MM/DD/YYYY,I would like to convert it to YYYY Q   format.

       

       

      Thanks

        • Re: Date convertion
          Pradip Sen

          Try like:

           

          =Year([Your Field]) & ' Q' & ceil(Month([Your Field])/3)

            • Re: Date convertion
              Marco Wedel

              Dual(Year([Your Field]) & ' Q' & ceil(Month([Your Field])/3), QuarterStart([Your Field]))


              To get an underlying numerical value that enables you to perform date calculations with the newly created field.


              Regards


              Marco

            • Re: Date convertion
              Avinash R

              Hi Rajesh,

              Try like this:

              Date(Ceil(datefield),'YYYY') &'Q' as newfiled

              • Re: Date convertion

                Base:
                LOAD EMPNO,
                    ENAME,
                    JOB,
                    MGR,
                    date(HIREDATE,'DD/MM/YYYY') as HIREDATE,
                    SAL,
                    COMM,
                    DEPTNO;
                SQL SELECT *
                FROM SCOTT.EMP;


                minMaxdate:
                Load min(HIREDATE,'DD/MM/YYYY') as minDate, max(HIREDATE,'DD/MM/YYYY') as maxDate Resident Base;

                Let vminDate = num(peek('minDate',0,'minDate'));
                Let vmaxDate = num(peek('maxDate',0,'maxDate'));

                cal1:
                load
                    IterNo() as num1,
                    $(vminDate) + IterNo() - 1 as Num,
                    date($(vminDate) + IterNo()-1) as TempDate
                AutoGenerate 1 While
                $(vminDate)+IterNo()-1 <= $(vmaxDate);

                cal2:
                load
                    Num as DateSeq,
                    TempDate as TheDate,
                    Month(TempDate) as month,
                    num(Month(TempDate)) as MonthSeq,
                    Year(TempDate) as yearSeq,
                    day(TempDate) as DaySeq
                Resident cal1
                order by TempDate ASC;

                //drop table cal1;

                cal3:
                LOAD
                    DateSeq,
                    TheDate as HIREDATE,
                    yearSeq,
                    month,
                    MonthSeq,
                    DaySeq,
                    MonthSeq + (yearSeq - 1) *12 as MonthSeq1,
                    Ceil(MonthSeq/3) as quarter,
                    'Q'&     Ceil(MonthSeq/3) as quarter1,
                    WeekDay(TheDate) as DayName
                Resident cal2
                order by TheDate ASC;

                • Re: Date convertion
                  Sathish G

                  Hi,

                  Try this

                  year([Bookings Date (2)]) & 'Q' & CEIL(NUM(month([Bookings Date (2)]))/3)

                   

                  -Sathish