Convert String to Time stamp format

[#1]

Hi all,

It's an urgent requirement for me to finish now.

How to convert an different type format field into Timestamp format ?

Consider the field in my table contains Datefield

Datefield

Fri Apr 06 07:45:47 2012

Fri Apr 06 07:45:50 2012

Fri Apr 06 07:45:55 2012

Sat Apr 07 11:15:12 2012

How can i convert above structure to timestamp format ?

Greatly appreciated to all your suggestions.

Thanks in advance.

Regards,

Venkat

[swuehl]

Try something like

=timestamp(timestamp#(mid('Fri Apr 06 07:45:47 2012',5),'MMM DD hh:mm:ss YYYY'))

or in your load script

LOAD

Datefield,

timestamp(timestamp#(mid(Datefield,5),'MMM DD hh:mm:ss YYYY')) as Timestamp,

...

FROM ...;

your standard month names format should match the month names abbr. in your field, e.g.:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

[#3]

Hi ,

Thanks for reply i tried your code, getting required format after placing in calculated dimension.

Now in my chart the "Timestamp" sort order is in-proper, how to make Timestamp sort order in asc.

Please find the attached sample app.

[swuehl]

Just sort by Numeric Value in Sort tab.

You can also format your Timestamp field in the load script, just by adding the format needed to the timestamp() function.

[#5]

Hi,

thanks a lot . I got exactly sort order by making sort by expresssion "TimeStamp".

And one more help from you when i'm trying directly in below format in "TimeStamp" it is showing empty in TimeStamp field.

Why it is happeining ? Can i give directly "MMM/DD/YYYY  hh:mm:ss".

timestamp(timestamp#(mid([Time Data Recorded],5),'MMM/DD/YYYY hh:mm:ss')) as TimeStamp

Thanks in advance.

Regards,

Venkat

[swuehl · Accepted solution]

There are two functions involved, timestamp#() to interpret your string as timestamp (not changing the displayed text format) and timestamp() to format the displayed timestamp. Both take a format code as second argument:

timestamp(timestamp#(mid([Time Data Recorded],5),'MMM DD hh:mm:ss YYYY'),'MMM/DD/YYYY hh:mm:ss') as Timestamp,

[#7]

Thanks a lot Swuehl. Your post is very helpful to me to complete the project.

Regards,

Venkat.

[#8]

Hi,

One more help from you.

I'm using the below line in edit script and trying to make sort by field "TimeStamp". But not able to sort the PLDate field same as TimeStamp field.

Trim(Left(Mid([Time Data Recorded],5),7)&Right([Time Data Recorded],4)) as PLDate

What might be the cause  or need to follow some other way ?

Thanks in advance.

Regards,

Venkat

[swuehl]

It seems that you want to create a Date only from your timestamp and format it.

Don't do this by concatenating strings.

Try always to create date or timestamp values with a text as well as a numerical representation, so you can use QV date and time functions and you can calculate with dates / timestamps, for example like this for your request:

Date(daystart(Timestamp),'MMM DD YYYY') as PLDate

(if you need to create Timestamp first from your Time Data Recorded, use above as preceding load or replace Timestamp with the expression used to create Timestamp (see above)).

LOAD *,

Date(daystart(Timestamp),'MMM DD YYYY') as PLDate;

LOAD

[Time Data Recorded],

timestamp(timestamp#(mid([Time Data Recorded],5),'MMM DD hh:mm:ss YYYY'),'MMM/DD/YYYY hh:mm:ss') as Timestamp,

...

FROM ...;

Hope this helps,

Stefan

[#10]

Awesome Swuehl. Really thanks for your support and sharing your knowledge to me.

Once again thanks a lot.

Regards,

Venkat.