6 Replies Latest reply: Dec 15, 2014 5:49 AM by Saumya Shah RSS

    Date formate

      Hi All,

      i have a date formate of this type  CD(2014-06-10 00:07:51.63) . I wanted to know how do i get the month and year out of this .

      I tried with the following expression but its not working year(CD) as year .

       

       

      Thanks in Advance

        • Re: Date formate
          Enrique Colomer

          Try with subfield function or mid

           

          mid(Date, 1,4) as year

          mid(Date, 6,2) as month

          • Re: Date formate
            Anand Chouhan

            Hi,

             

            You can try some of this ways by subfield also but after this if the date format not match in the field then format date in the load script.

             

            Tab1:
            LOAD Datetime,
            Date(Date#(Mid(Datetime,4,4),'YYYY'),'YYYY') as Year,
            Date(Date#(Mid(Datetime,9,2),'MM'),'MM') as Month,
            Date(Date#(Mid(Datetime,12,2),'DD'),'DD') as Day,

             

            SubField( SubField(Datetime,'(',2),'-',1) as Yearchk,
            SubField( SubField(Datetime,'(',2),'-',2) as Monthchk,
            Right(SubField( SubField(Datetime,'(',2),' ',1),2) as Daychk;
            LOAD * INLINE [
            Datetime
            CD(2014-06-10 00:07:51.63)
            ];

             

            Regards

            Anand

            • Re: Date formate
              jagan mohan rao appala

              Hi,

               

              Try like this

               

              LOAD

              *,

              Year(FormattedDate) AS Year,

              Month(FormattedDate) AS Month;

              LOAD
              *,

              Date(Date#(Left(CD, 10),  'YYYY-MM-DD')) AS FormattedDate

              FROM DataSource;

               

               

              Hope this helps you.

               

              Regards,

              Jagan.

              • Re: Date formate
                Antonio Caria

                AVOID Dates.

                 

                Use only Numbers. Previous You must convert The Date to number.

                 

                Date(0) is 18991231.

                 

                A.Caria

                • Re: Date formate
                  Marco Wedel

                  Hi,

                   

                  supoosed your field literally contains the text 'CD(2014-06-10 00:07:51.63)' then one solution could be

                   

                  to extract the month:

                  =Month(Timestamp#(TextBetween('CD(2014-06-10 00:07:51.63) ','(',')'),'YYY-MM-DD hh:mm:ss.ff'))
                  
                  

                   

                  to extract the year:

                  =Year(Timestamp#(TextBetween('CD(2014-06-10 00:07:51.63) ','(',')'),'YYY-MM-DD hh:mm:ss.ff'))
                  
                  

                   

                  QlikCommunity_Thread_145533_Pic1.JPG

                   

                  You might as well extract the timestamp in the script and then get the year and month from it with a preceding load like:

                   

                  LOAD *,
                            Month (TimestampField) as MonthField,
                            Year(TimestampField) as YearField;
                  LOAD Timestamp#(TextBetween('CD(2014-06-10 00:07:51.63) ','(',')'),'YYY-MM-DD hh:mm:ss.ff') as TimestampField
                  FROM yoursource;
                  

                   

                   

                  hope this helps

                   

                  regards

                   

                  Marco