Load date/time as date

[#1]

I have a csv file I am loading with a date/time field called SessionStarted (e.g. 28/03/2014 08:56).

I need to either load it as a date, or edit my expressions to read the data as a date. Or is it even possible to split the data into two fields when doing the load?

One of my expressions is as follows:

Count({$<UserName-={"*test*"},Firm-={"Acme"}>}UserName)

Thanks

Alex

[#2]

Hi,

Use this while loading data

Date(Timestamp#(SessionStarted ,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY')

Thanks,

Prabhu

[jonathandienst]

Hi

You can split the field at load using (in fact I would recommend it):

Date(Floor(TimeStamp#(SessionStarted, 'DD/MM/YYYY hh:mm'))) As Date,

Time(Frac(TimeStamp#(SessionStarted, 'DD/MM/YYYY hh:mm'))) As Time,

Note that Date() without Floor does not remove the time portion, it merely reformats the field; so you will land up with a whole lot of seemingly identical but actually unique values for each day. This is a common mistake. The Frac does the equivalent for the time.

HTH

Jonathan

[#4]

So, how would that fit into my load script?

LOAD UserName,

     SessionId,

     Environment,

     DisconnectionReason,

     SessionStarted,

     WorkingDayReconnect

FROM

[\\XXXXXX\XXXXXX\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

[#5]

That's great Jonathan. I'm a bit of a beginner at this, so how would that fit into my load script?

LOAD UserName,

     SessionId,

     Environment,

     DisconnectionReason,

     SessionStarted,

     WorkingDayReconnect

FROM

[\\XXXXXX\XXXXXX\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

[#6]

I tried simply replacing the SessionStarted line with your script, but it just seems to load blank values for the date

LOAD UserName,

     SessionId,

     Environment,

     DisconnectionReason,

     Date(Floor(TimeStamp#(SessionStarted, 'DD/MM/YYYY hh:mm'))) As CalendarDate,

     Time(Frac(TimeStamp#(SessionStarted, 'DD/MM/YYYY hh:mm'))) As Time,

     WorkingDayReconnect

FROM

[\\XXXXXX\XXXXXX\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

[#7]

Try this..

LOAD UserName,

     SessionId,

     Environment,

     DisconnectionReason,

     Date(Timestamp#(SessionStarted ,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY') as SessionStarted,

     SessionStarted as SessionStartedTime,

     WorkingDayReconnect

FROM

[\\XXXXXX\XXXXXX\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

[#8]

I get the same thing as which Jonathan's suggestion below. The SessionStarted data which should show the date is blank for all rows

[#9]

Could you pls give some sample data that SessionStarted field contains?

It is just to check the format of the data time in it.

Thanks,

Prabhu

[ashwanin · Accepted solution]

LOAD UserName,

     SessionId,

     Environment,

     DisconnectionReason,

     Date(SessionStarted,'DD-MMM-YYYY') As Date,

     WorkingDayReconnect

FROM

[\\XXXXXX\XXXXXX\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);