Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an issue in formatting dates as described below. Would appreciate any help on this from my fellow members.
My script has the following:
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
DateTable:
LOAD DateFormat as OriginalDateFormat,
alt(Date#(DateFormat,'mm/dd/yyyy'),Date#(DateFormat,'dd/mm/yyyy'),Date#(DateFormat,'mm-dd-yyyy'),Date#(DateFormat,'dd-mm-yyyy')) as FormattedDate,
alt(Date(Date#(DateFormat,'mm/dd/yyyy'),'mm.dd.yyyy'),Date(Date#(DateFormat,'dd/mm/yyyy'),'mm.dd.yyyy'),Date(Date#(DateFormat,'mm-dd-yyyy'),'mm:dd:yyyy'),Date(Date#(DateFormat,'dd-mm-yyyy'),'mm:dd:yyyy')) as FormattedDateDisplay,
Year(alt(Date#(DateFormat,'mm/dd/yyyy'),Date#(DateFormat,'dd/mm/yyyy'),Date#(DateFormat,'mm-dd-yyyy'),Date#(DateFormat,'dd-mm-yyyy'),Date#(DateFormat,'dd-mm-yyyy'))) as FormattedDateYear
INLINE [
DateFormat
08/28/2013
28/08/2012
08-28-2014
28-08-2011];
Now my question is regarding the field: FormattedDateDisplay which shows as below
Now for 28-08-2011 I have used Date(Date#(DateFormat,'dd-mm-yyyy'),'mm:dd:yyyy') still getting the display as 28:08:2011 instead of 08:28:2011.
Thanks,
Shoubhik.
Try
alt(Date(Date#(DateFormat,'MM/DD/YYYY'),'MM.DD.YYYY'),Date(Date#(DateFormat,'DD/MM/YYYY'),'MM.DD.YYYY'),Date(Date#(DateFormat,'MM-DD-YYYY'),'MM:DD:YYYY'),
Date(Date#(DateFormat,'DD-MM-YYYY'),'MM:DD:YYYY')) as FormatteDDateDisplay_NEW
Try
alt(Date(Date#(DateFormat,'MM/DD/YYYY'),'MM.DD.YYYY'),Date(Date#(DateFormat,'DD/MM/YYYY'),'MM.DD.YYYY'),Date(Date#(DateFormat,'MM-DD-YYYY'),'MM:DD:YYYY'),
Date(Date#(DateFormat,'DD-MM-YYYY'),'MM:DD:YYYY')) as FormatteDDateDisplay_NEW
This works...Thanks a lot. Well since date formats are case-sensitive so MM and mm interpretations are different.