Date Conversion Issue

[scotly-victor]

Hi All ,

I have two set of date format in my Date field  31/12/2015 18:57 and 1/1/2016 9:30:34 AM

I wanted combine both format into same  format.

I had tried used all conversion but no results .

I have attached screen shot for your reference

[Chanty4u]

try with alt

Load

Date(Alt(

  Date#(Date,'DD/MM/YYYY hh:mm '),

  Date#(Date,'DD-MM-YYYY')

)) as Date;

LOAD * INLINE [

    Date

     31/12/2015 18:57

1/1/2016 9:30:34 AM

];

[scotly-victor]

Sorry, didn't work.

[MindaugasBacius]

The script:

LOAD *
  ,Alt(Timestamp(Timestamp#(Date, 'DD/MM/YYYY hh:mm:ss TT'), 'DD/MM/YYYY hh:mm'),
  Timestamp(Timestamp#(Date, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm')) as new_Date
INLINE [
    Date
    1/1/2016 9:30:34 AM
    31/12/2015 18:57
];

Results:

[arulsettu]

try this

Load

date(Alt(

  Date#(check,'DD/MM/YYYY hh:mm'),

  Date#(check,'DD/MM/YYYY hh:mm:ss ff')

),'DD/MM/YYYY') as check;

LOAD * INLINE [

check

31/12/2015 18:57

1/1/2016 9:30:34 AM

];

[scotly-victor]

I am getting 2015 records

But 2016 missing

[scotly-victor]

Is there any way to convert this into same format

[arulsettu · Accepted solution]

try this

Load

date(Alt(

  Date#(check,'DD/MM/YYYY hh:mm'),

  Date#(check,'DD/MM/YYYY hh:mm:ss ff'),

  date(check,'DD/MM/YYYY')

),'DD/MM/YYYY') as check;

LOAD * INLINE [

check

31/12/2015 18:57

1/1/2016 9:30:34 AM

42370.5875

];

or post sample qvf

[MindaugasBacius]

Alt(Date(Date, 'DD/MM/YYYY hh:mm'), 

Timestamp(Timestamp#(Date, 'DD/MM/YYYY hh:mm'), 'DD/MM/YYYY hh:mm')) as new_Date 

[brijesh_fofadiy]

Hi,

Try this:

Date(floor(Date))