Date and Time to Time Only

[#1]

Help nothing works. I want to convert a date and time field to time only

The field I'm converting always looks like this  with dates as 00/01/1900 with the time after ie

  00/01/1900 00:01:03

  00/01/1900 10:05:13

  00/01/1900 15:29:22

  00/01/1900 20:44:52

nothing seems to extract the time only any ideas

[tresesco]

Use Frac(). Like:

LOAD Time(Frac(Time),'hh:mm:ss') as Time

FROM <>;

[its_anandrjs]

Load like and try one of this

Load

Time,

Timestamp#(Time,'M/D/YYYY h:mm:ss TT') as NewTime

Time(Time,'hh:mm:ss') as NewTime2,

Time(Time,'hh:mm:ss TT') as NewTime3

From location;

And it seems in excel file your Time field is not formatted as the proper Time or Date field try one of this.

[ashfaq_haseeb]

If it is date field

then use

=Time(DATE_TIME_Field)

Regards

ASHFAQ

[thomas_skariah]

Hi,

PFA, you can get help from this.

Regards,

Tom

[#6]

Doesn't work it just returns " -  "

[its_anandrjs]

Try one of this

Time(Time,'hh:mm:ss TT') as NewTime1,

Time(Time,'hh:mm:ss') as NewTime2,

Time(Time,'hh:mm:ss TT') as NewTime3

[#8]

That the problem the formatting of the excel file seems strange but all the files come like this and I need an extraction which will put it right

[tresesco]

It worked with me. PFA

[nlmmaurya]

Hi,

Try this if your Date format would be always same.

LOAD Time,

     Right(Time,11) as NewTime       

FROM<>