1 field in 2 columns Load?

[tomcatxx]

Hello there,

I have a column of Table like this.

Time
3/12/2015 00:21
12/11/2014 01:12
1/5/2014 00:32
...

I want to load that in 2 columns like this. The loaded Date has a same length.

Date	Time
03/12/2015	00:21
12/11/2014	01:12
01/05/2014	00:32
...	...

Have u a ideal? Thanks.

[shiveshsingh · Accepted solution]

Please try this

T:LOAD * INLINE [ 

    Time

    3/12/2015 00:21

    12/11/2014 01:12

    1/5/2014 00:32

];

F:

load SubField(Time,' ',1) as Date,

Right(Time,4) as Time

Resident T;

[shiveshsingh]

OR THIS

T:LOAD * INLINE [

    Time

    3/12/2015 00:21

    12/11/2014 01:12

    1/5/2014 00:32

];

F:

load DATE(date#(SubField(Time,' ',1),'DD/MM/YYYY'),'DD/MM/YYYY') as Date,

Right(Time,4) as Time

Resident T;

[sasiparupudi1]

Date (Floor (Timestamp#(yourDateTime,'DD/MM/YYYY hh:mm'))) As Date,

Time(Frac(Timestamp#(yourDateTime,'DD/MM/YYYY hh:mm'))) As Time

[vishsaggi]

Try this?

DateField:
LOAD *, Timestamp(Timestamp#(DateTimeField, 'MM/DD/YYYY hh:mm'), 'hh:mm') AS TimeStamp,
Date(Date#(DateTimeField, 'MM/DD/YYYY hh:mm'), 'MM/DD/YYYY') AS DateStamp INLINE [
DateTimeField
12/3/2015 01:21
2/6/2014 00:23
3/11/2015 00:18
];

[sunny_talwar]

Always a good idea to add Floor and Frac (like sasiparupudi1‌ added) because if you don't you will see repeating values in a list box for DateStamp and TimeStamp and you will wonder what is wrong. The problem is that Date() and TimeStamp() function are just formatting functions, they will make your field look like date and time, but if the underlying value is a date and time field.. you will see multiple dates repeating because they have different time values... similarly the same will be the case for Time...

[vishsaggi]

Fair points. Got it Sunny. Thanks for mentioning that. I thought Timestamp# and Date# would take care of that. Will they not?

[sunny_talwar]

Nope they won't in this case... the expression will have to be how shiveshsingh‌ did in that case

[vishsaggi]

Got it.

[tomcatxx]

Thank you ver much.