Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
Hi Paul,
try
Date(Date#(PurgeChar(YourDateField,'AMP'),'MM/DD/YYYY hh:mm'),'DD/MM/YYYY' )
or SubField(YourDate,' ',1)
Regards,
Antonio
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
Sorry it's not clear from my sample data
both formats are MM/DD/YYYY
Hi
just need
LEFT(Date,10),'MM/DD/YYYY') as Date
Mark
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?
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
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
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.