Formatting a Mixed Datetime Field

[haymarketpaul]

I've got a column with a mix of datetimes in it like this and i just want to format the column to leave me with DD/MM/YYYY

10/15/2015 10:16 AM

10/02/2015 22:24

09/25/2015 03:13 PM

10/06/2015 11:42

09/27/2015 12:26 PM

10/17/2015 09:03 PM

10/01/2015 15:15

10/18/2015 12:07 AM

10/06/2015 08:08

10/12/2015 15:13

10/09/2015 18:27

08/24/2015 12:23 PM

10/20/2015 10:12 AM

10/14/2015 01:10 PM

Any ideas much appreciated

[sunny_talwar]

May be try this with Alt function:

For TimeStamp

TimeStamp(Alt(Num(TimeStampField), Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm TT')), Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm')))) as TimeStampField

For Date

Date(Floor(Alt(Num(TimeStampField), Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm TT')), Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm'))))) as DateField

Sample Script:

Table:

LOAD *,

  Date(Floor(TimeStampField)) as DateField;

LOAD TimeStamp(Alt(Num(TimeStampField),

    Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm TT')),

    Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm')))) as TimeStampField

Inline [

TimeStampField

10/15/2015 10:16 AM

10/02/2015 22:24

09/25/2015 03:13 PM

10/06/2015 11:42

09/27/2015 12:26 PM

10/17/2015 09:03 PM

10/01/2015 15:15

10/18/2015 12:07 AM

10/06/2015 08:08

10/12/2015 15:13

10/09/2015 18:27

08/24/2015 12:23 PM

10/20/2015 10:12 AM

10/14/2015 01:10 PM

];

[antoniotiman]

Hi Paul,

try

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

or SubField(YourDate,' ',1)

Regards,

Antonio

[Mark_Little]

Hi Paul,

If it is the case that the one ending am or PM are formatted MM/DD/YYYY and the other are DD/MM/YYYY

then maybe something like below.

DATETemp:

LOAD * INLINE [

    Date

  10/15/2015 10:16 AM

    10/02/2015 22:24

    09/25/2015 03:13 PM

    10/06/2015 11:42

    09/27/2015 12:26 PM

    10/17/2015 09:03 PM

    10/01/2015 15:15

    10/18/2015 12:07 AM

    10/06/2015 08:08

    10/12/2015 15:13

    10/09/2015 18:27

    08/24/2015 12:23 PM

    10/20/2015 10:12 AM

    10/14/2015 01:10 PM

];

NoConcatenate

LOad

  Date,

  IF(Right(Date,1) =  'M',

       DATE(DATE#(LEFT(Date,10),'MM/DD/YYYY'),'DD/MM/YYYY'),

       DATE(LEFT(Date,10),'DD/MM/YYYY')

   ) as DATEFormated

Resident DATETemp;

Drop Table DATETemp;

Mark

[haymarketpaul]

Sorry it's not clear from my sample data

both formats are MM/DD/YYYY

[Mark_Little]

Hi

just need

LEFT(Date,10),'MM/DD/YYYY') as Date

Mark

[haymarketpaul]

Thanks for reply

I need the output in DD/MM/YYYY so tried this...

Date(Date#(Left([Opt In Date],10),'MM/DD/YYYY'),'DD/MM/YYYY')

but the dates without the AM/PM parts seem to be missing from my output ?

I've attached a snapshot of the xlsx file i've got

Any ideas?

[sunny_talwar]

Try this:

TimeStamp(

          Alt(

               Num(TimeStampField),

               Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm TT')),

               Num(TimeStamp#(TimeStampField, 'MM/DD/YYYY hh:mm'))

          )

, 'DD/MM/YYYY hh:mm:ss TT') as TimeStampField

[Mark_Little]

HI,

That seems fine on my data,

Maybe try adding a trim to removed any spaces?

Date(Date#(Left(Trim([Opt In Date]),10),'MM/DD/YYYY'),'DD/MM/YYYY')

Mark

[haymarketpaul]

Thanks for reply - still can't get the dates without the AMPM parts to show?  The field only gives me this (with Sunny's answer as well)...

24/08/2015

25/09/2015

27/09/2015

28/09/2015

29/09/2015

30/09/2015

14/10/2015

15/10/2015

17/10/2015

18/10/2015

20/10/2015

22/10/2015

Thought it might be something to do with the xlsx which is data that comes out of Silverpop ?

If i do this...

Date#(Left(Trim([Opt In Date]),10),'MM/DD/YYYY')

I get this...

42014.6354

42045.9333

42165.3388

42165.4875

42257.7687

42287.525

42348.6340

08/24/2015

09/25/2015

09/27/2015

09/28/2015

09/29/2015

09/30/2015

10/14/2015

10/15/2015

10/17/2015

10/18/2015

10/20/2015

10/22/2015

If i add then add Date(Date#(Left(Trim([Opt In Date]),10),'MM/DD/YYYY'),'DD/MM/YYYY')

i lose all the dates that look like numbers  eg  42014.6354

Perhaps i need to convert these somehow.