converting timestamp to date and then to num

[Anonymous]

Hi Geniuses,

I am facing problem while converting timestamp to date

I have the timestap coming as text from source like below

4/6/2016 9:52 AM

4/6/2016 10:28 AM

4/6/2016 10:32 AM

4/6/2016 10:40 AM

4/6/2016 11:35 AM

4/6/2016 11:38 AM

4/6/2016 11:41 AM

4/6/2016 11:42 AM

I want to first convert it into date and then change that date into number

As i have to calculate the open days for the ticket

Thanks in advance

Shashank

[Chanty4u]

=Date(DATE#(Datefield,'D/M/YYYY hh:ss T'   )    'DD/MM/YYYY') AS  new date.

What is ur required date format?

=Num(Date field)

[HirisH_V7 · Accepted solution]

Hi,

May be like this,

Data:
LOAD *,
Date(Timestamp#(Field,'DD/MM/YYYY h:mm TT'),'DD/MM/YYYY') as Date,
Num(Date(Timestamp#(Field,'DD/MM/YYYY h:mm TT'),'DD/MM/YYYY')) as NumDate

 INLINE [
    Field
    4/6/2016 9:52 AM
    4/6/2016 10:28 AM
    4/6/2016 10:32 AM
    4/6/2016 10:40 AM
    4/6/2016 11:35 AM
    4/6/2016 11:38 AM
   4/6/2016 11:41 AM
   4/6/2016 11:42 AM
];

Output:

HTH,

PFA,

Hirish

[tamilarasu]

Not sure your what you are trying to do. Try,

Num(Floor(Date#(Datefield,'M/D/YYYY h:mm TT')))

[Chanty4u]

Small change to my exp.

Data:

LOAD *,

num(Date(DATE#(Datefield,'D/M/YYYY hh:ss TT') ,'DD/MM/YYYY')) AS  new1

INLINE [

    Datefield

    4/6/2016 9:52 AM

    4/6/2016 10:28 AM

    4/6/2016 10:32 AM

    4/6/2016 10:40 AM

    4/6/2016 11:35 AM

    4/6/2016 11:38 AM

   4/6/2016 11:41 AM

   4/6/2016 11:42 AM];

[jagan]

Hi,

Try like this

Floor(DATE#(Datefield,'M/D/YYYY hh:ss TT'   ) )

Regards,

Jagan.