3 Replies Latest reply: May 20, 2013 12:36 PM by sree anaarasi RSS

    conversion of dates

    sree anaarasi

      Hi  i  have a date field as  Date  as

       

      Apr 24 2013 12:00:00:000AM

      Feb  1 2013 12:00:00:000AM

      May  6 2013 12:00:00:000AM

      May  7 2013 12:00:00:000AM

      May  8 2013 12:00:00:000AM

      May  9 2013 12:00:00:000AM

      May 10 2013 12:00:00:000AM

      May 13 2013 12:00:00:000AM

      May 14 2013 12:00:00:000AM

      May 15 2013 12:00:00:000AM

         so how to convert it into the 'MM/DD/YYYY' format or nummerical value..

        • Re: conversion of dates
          Alan Farrell

          Try

           

          DATE (DATE#(Date,'MMM DD YYYY'),'MM/DD/YYYY') AS Date

          • Re: conversion of dates
            Ralf Becher

            Hi,

             

            this is one solutions:

             

            raw_data:
            LOAD * INLINE [
                raw_date
                Apr 24 2013 12:00:00:000AM   
                Feb  1 2013 12:00:00:000AM
                May  6 2013 12:00:00:000AM
                May  7 2013 12:00:00:000AM
                May  8 2013 12:00:00:000AM    
                May  9 2013 12:00:00:000AM    
                May 10 2013 12:00:00:000AM    
                May 13 2013 12:00:00:000AM
                May 14 2013 12:00:00:000AM
                May 15 2013 12:00:00:000AM
            ];
            
            result:
            LOAD 
            date(date#(left(replace(raw_date, '  ', ' '), index(replace(raw_date, '  ', ' '), ' ', 3) -1), 'MMM DD YYYY'), 'MM/DD/YYYY') as the_date
            resident raw_data;
            

             

            - Ralf