"DD MMM HH:MM" TO "DD/MM/YYYY HH:MM"

[#1]

I HAVE A DATE FIELD, AND I NEED TO CONVERT IT.

fOR EXAMPLE I HAVE "27 FEB 10:54" EXPECTED RESULT will be"27/02/2015 10:54"

AND I NEED TO CONSIDER THAT IF THIS YEAR OR YEAR-1

EXAMPLE. IF I HAVE "27 DEC 10:10" I EXPECTED RESULT will be"27/12/2014 10:10"

ONLY ONE YEAR LESS.

Thansk

[saurabh5]

hi Federica,

you can use the date function to change the format like date(field,'DD/MM/YYYY HH:MM')
but i dont see year in your example?,So how would you derive it?

Regards

Saurabh

[JonnyPoole · Accepted solution]

See date() and date#() to read a specific input and create a specific output.

The big question here though is how do you define if year = this year or year is last year.  Do you only have 12 months of data and any month later  than today's month counts toward last year ?  If that is the case the following should work although you won't need the 'temp' table , just load the 2nd table direct from your data source.

temp:

LOAD * INLINE [

    Date

    27 FEB 10:54

    27 DEC 10:10

];

load

  date(  date#( if( month(today()) > month(NewDate), Year(today()) - 1 , Year(Today()))  & '/' & month(NewDate) & '/' & day(NewDate) & ' ' & hour(NewDate) & ':' & Minute(NewDate), 'YYYY/MMM/D h:m') ,'DD/MM/YYYY hh:mm') as FormattedDate;

load

  Date#( Date, 'DD MMM hh:mm') as NewDate

Resident temp;

[#4]

yeah i haven´t year so i need a transfomation

[#5]

load

date( date#( if( month(today()) < month(NewDate), Year(today()) - 1 , Year(Today())) & '/' & month(NewDate) & '/' & day(NewDate) & ' ' & hour(NewDate) & ':' & Minute(NewDate), 'YYYY/MMM/D h:m')
,'DD/MM/YYYY hh:mm') as FormattedDate;

is <

the right answerd!

Thanks