Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

[rt_new_user]

Dear community users

Can anyone suggest how do i format my Date field which is originally in this format (YYYY-MM-DD:hh:mm:SS) into two separate list boxes named Date and time?

my script is:

LOAD @1 as Country,

     @5 as DATE,

     @6 as Duration,

     @11 as Technology

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

[rwunderlich]

date(floor(@5)) as DATE

time(frac(@5)) as TIME

-Rob

[rt_new_user]

Thanks for your reply Rob

Updating my script to this give me an error:

LOAD @1 as Country,

// @5 as DATE,

Date(floor@5) as DATE,

time(frac@5) as TIME,

@6 as Duration,

@11 as Technology

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

[senpradip007]

Try like:

LOAD @1 as Country,

Date(floor(@5)) as DATE,

time(frac(@5)) as TIME,

     @6 as Duration,

     @11 as Technology

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

[rt_new_user]

Thanks

Loading the below script didn’t give any error but no data loaded in the list box for DATE and TIME

LOAD @1 as Country,

// @5 as DATE,

Date(floor(@5)) as DATE,

time(frac(@5)) as TIME,

@6 as Duration,

@11 as Technology

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

[Anonymous]

Hi

if you want to do in list box then

Try adding list boxes and edit expressions as

fro date left (Date,8)

for time right(Dtae,7)

Regards

Harsha

[jyothish8807]

Hi Ravi,

Try

Date(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Date,

time(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Time

or

Try like this:

left(@5,10) as date,

right(@5,8) as Time

Hope it works

Regards

KC

[rt_new_user]

Hi Jyotish

That worked exactly the way i wanted. However can i just change my requirement slightly?

What if i want the date column just to display in YYYY-MM format?

Current Script is:

LOAD @1 as Country,

Date(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Date,

time(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Time,

@6 as Duration,

@11 as Technology

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

[rt_new_user]

Hi Jyotish

Don’t worry i just used your Date script and modified by just swapping date with month and it worked almost close to what i wanted.

Month(timestamp#(@5,'YYYY-MM-DD:hh:mm:SS')) as Month,

This is how my list boxes look now which is ok

I want to plot a bar graph showing monthly totals counts of 2G occurrences vs 3G occurrences.

For example for each country in the month of Sep count the number of times 2G appeared in the list box vs 3G.

I used Month and country as dimensions, and used Count of Technology but that just counts 2G 3G Unknown all together. I want counts of each technology.

Can you please help again?

Regards

[saumyashah90]

Hi Ravi,

Use Subfield like:

=

SubField(@5,':',1) as Date;

SubField(@5,':',2)&':'&SubField(@5,':',3)&':'&SubField(@5,':',4)&':'&SubField(@5,':',5)  as Time;