8 Replies Latest reply: Mar 8, 2012 7:18 AM by Magnus ÅVITSLAND RSS

    Date; From 'DD-MMM-YYYY hh:mm:ss TT'  to DD-MM-YYYY

      Hello,

       

      I have a database with the date put in as: 'DD-MMM-YYYY hh:mm:ss TT',

       

      I only want to have the DD-MMM-YYYY, the time I don't care about. So I want to lose the time (hh:mm:ss)

       

      It's probably a simple solution, but I couldn't find it.

       

      thanks in advance

        • Date; From 'DD-MMM-YYYY hh:mm:ss TT'  to DD-MM-YYYY
          Marcos Gomez

          Hi,

           

          this expressions could help you

           

          table:

          Load

          DateField,

          Date(Floor(DateField)) as NewDate,

          Time(Frac(DateField)) as NewHour

          From tab1

          ;

          • Date; From 'DD-MMM-YYYY hh:mm:ss TT'  to DD-MM-YYYY
            Celambarasan Adhimulam

            Hi,

                 Try with this

                 Date(Date#(DateField,'DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY')

             

            Celambarasan

            • Date; From 'DD-MMM-YYYY hh:mm:ss TT'  to DD-MM-YYYY
              Henco van Ee

              Hi Gerbrand,

               

              That will be:

               

              date(FIELD, 'DD-MMM-YYYY')

               

              Good luck.

               

              Regards,

              Henco

              • Date; From 'DD-MMM-YYYY hh:mm:ss TT'  to DD-MM-YYYY

                thanks for the fast reactions, but I still didn't get it to work.. I get errors...

                 

                I'll post my script here (what I had originaly) and maybe some1 can tell where to add the new part, your help is much appreciated.

                 

                 

                 

                 

                ODBC CONNECT TO [MS Access Database;DBQ=GO_SALES\go_sales.accdb];

                 

                RETURN_DATE:

                SQL SELECT DISTINCT

                 

                `RETURN_DATE`        as            RETURN_DATE_date

                 

                FROM `RETURNED_ITEM`;

                 

                    

                Join (RETURN_DATE)

                LOAD

                    RETURN_DATE_date,

                    Year(RETURN_DATE_date)                as        RETURN_DATE_YEAR_nr,

                    Trim(Month(RETURN_DATE_date))         as         RETURN_DATE_MONTH_name

                Resident RETURN_DATE;

                 

                Join (RETURN_DATE)

                LOAD * INLINE [

                    RETURN_DATE_MONTH_name, RETURN_DATE_MONTH_nr

                    jan, 1

                    feb, 2

                    mrt, 3

                    apr, 4

                    mei, 5

                    jun, 6

                    jul, 7

                    aug, 8

                    sep, 9

                    okt, 10

                    nov, 11

                    dec, 12

                ];

                  • Date; From 'DD-MMM-YYYY hh:mm:ss TT'  to DD-MM-YYYY
                    jagan mohan rao appala

                    Hi,

                     

                    Try this script.

                     

                    RETURN_DATE:

                    LOAD

                         *,

                         Month(RETURN_DATE_date) AS RETURN_DATE_MONTH_name,

                         Month(RETURN_DATE_date) * 1 ASRETURN_DATE_MONTH_nr,

                         Year(RETURN_DATE_date) AS RETURN_DATE_YEAR_nr;

                    SQL SELECT DISTINCT

                    Date(Date#('RETURN_DATE','DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY') AS RETURN_DATE_date

                    FROM `RETURNED_ITEM`;

                     

                    Hope this helps you.

                     

                    Regards,

                    Jagan.

                    • Date; From 'DD-MMM-YYYY hh:mm:ss TT'  to DD-MM-YYYY
                      Henco van Ee

                      Hi,

                       

                      RETURN_DATE

                      LOAD *,

                           date(RETURN_DATE, 'DD-MM-YYYY') as RETURN_DATE

                      FROM RETURNED_ITEM;

                       

                      Kind regards,

                      Henco

                        • Re: Date; From 'DD-MMM-YYYY hh:mm:ss TT'  to DD-MM-YYYY
                          Magnus ÅVITSLAND

                          Hi gerbrand, hencovanee and Celambarasan.

                           

                          "

                          RETURN_DATE:

                          LOAD *,

                              date(RETURN_DATE, 'DD-MM-YYYY') as RETURN_DATE

                          FROM RETURNED_ITEM;

                          "

                          Unfortunately the above lines of code won't give you the date part of the field without the hh:mm part.

                          If the date field contains the hh:mm part, date(RETURN_DATE, 'DD-MM-YYYY') will still contain the hh:mm part in the field.

                           

                          The field will be displayed without the hh:mm part, but it still contains hh:mm.

                          The correct thing is either floor or left(5).

                           

                          My sample code:

                          original:

                          LOAD * INLINE [

                              original

                              2012-02-02 08:32:21

                              2012-02-01 09:35:52

                          ];

                          yyyymmdd:

                          load

                          date(original, 'YYYY-MM-DD') as original_yyyymmdd

                          ,date(floor(original), 'YYYY-MM-DD') as original_yyyymmdd_floor

                          ,date(left(num(original), 5), 'YYYY-MM-DD') as original_yyyymmdd_left

                          resident original;

                           

                          Then throw a listbox for each field and set the number format to Timestamp.

                          Then you clearly see that date(original, 'YYYY-MM-DD') still keeps the hh:mm information.

                           

                           

                          Kind regards

                           

                          Magnus Åvitsland

                          BI Consultant

                          Framsteg Business Intelligence Corp.

                        • Re: Date; From 'DD-MMM-YYYY hh:mm:ss TT'  to DD-MM-YYYY
                          Celambarasan Adhimulam

                          Hi,

                               Check with this

                           

                          ODBC CONNECT TO [MS Access Database;DBQ=GO_SALES\go_sales.accdb];

                           

                          RETURN_DATE:

                          Load Distinct

                               Date(Date#(RETURN_DATE_date,'DD-MM-YYYY hh:mm:ss TT'),'DD-MM-YYYY') as RETURN_DATE,

                               Year(RETURN_DATE_date)                as        RETURN_DATE_YEAR_nr,

                               Month(RETURN_DATE_date)*1         as         RETURN_DATE_MONTH_nr;

                          SQL SELECT DISTINCT

                           

                          `RETURN_DATE`        as            RETURN_DATE_date

                           

                          FROM `RETURNED_ITEM`;

                           

                          LOAD * INLINE [

                              RETURN_DATE_MONTH_name, RETURN_DATE_MONTH_nr

                              jan, 1

                              feb, 2

                              mrt, 3

                              apr, 4

                              mei, 5

                              jun, 6

                              jul, 7

                              aug, 8

                              sep, 9

                              okt, 10

                              nov, 11

                              dec, 12

                          ];

                           

                           

                          /*    This all don't need

                          Join (RETURN_DATE)

                          LOAD

                              RETURN_DATE_date,

                              Year(RETURN_DATE_date)                as        RETURN_DATE_YEAR_nr,

                              Trim(Month(RETURN_DATE_date))         as         RETURN_DATE_MONTH_name

                          Resident RETURN_DATE;*/

                           

                           

                          Hope it helps

                          Celambarasan