2 Replies Latest reply: Oct 30, 2014 4:06 AM by Shoubhik Mukherjee RSS

    Date Interpret and Format Issue

      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

      Capture4.PNG.png

      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.