QS-Date Fields

[Karahs]

Hello everyone,

               I have date fields like this:  May 12 2016 10:46AM

I want it in DD/MM/YYYY format and also want the timestamp.

How can i do that?

Thanks & Regards,

Kshitija

[Chanty4u]

load *,

Date(Date#(Datefiled,' MMM DD YYYY hh:mm TT'),'DD/MM/YYYY') AS newdate ;

or

Date(Date#(Datefiled,' May 12 2016 10:46AM'),'DD/MM/YYYY') AS newdate ;

Edited:

[fashid]

Try the date# and the date function

[sunny_talwar]

Try like this:

Date(Floor(Date#(DateField, 'MMM DD YYYY hh:mmTT'), 'DD/MM/YYYY') as Date,

Time(Frac(Date#(DateField, 'MMM DD YYYY hh:mmTT')) as Time,

[Karahs]

Hello,

     So here is the format pf the date i have and i want to calculate TAT of this....

[Karahs]

Hello ,

I tried theb formula but its coming blank...

[sunny_talwar · Accepted solution]

I think I missed some parenthesis, can you try this:

Date(Floor(Date#(DateField, 'MMM DD YYYY hh:mmTT')), 'DD/MM/YYYY') as Date,

Time(Frac(Date#(DateField, 'MMM DD YYYY hh:mmTT'))) as Time,

[Karahs]

Hello Sunny,

     It worked thankyou. Can you also suggest how to calculate TAT(turn around time) ?? ie. difference between the review and entry date??

[sunny_talwar]

May be like this:

LOAD Interval([REVIEW DATE] - [ENTRY DATE]) as TAT

LOAD [ENTRY DATE],

          Date(Floor(Date#([REVIEW DATE], 'MMM DD YYYY hh:mmTT')), 'DD/MM/YYYY') as [REVIEW DATE],

          Time(Frac(Date#([REVIEW DATE], 'MMM DD YYYY hh:mmTT'))) as [REVIEW Time]

From Source;