topic Re: string to date/time format in QlikView

string to date/time format

Wed, 18 May 2011 15:29:34 GMT

Hi,

My Database contains only string charcter, but when I load data on qlikview, I would like to I have some data as date/time format.

How can I do?

Thanks

string to date/time format

Wed, 18 May 2011 15:56:31 GMT

You can use date and string functions within your script to convert a value (e.g. number or string) to a date.

For example, I use 'MakeDate', 'Left' and 'Mid' functions to convert my "RESOLVED_DATETIME" number value as follows:

MakeDate(Left("RESOLVED_DATETIME",4), Mid("RESOLVED_DATETIME",5,2),Mid("RESOLVED_DATETIME",7,2))

The above example uses 'Left' and 'Mid' functions to define the relevant character blocks I want to represent the year, month and day elements of my date, whilst the 'MakeDate' function creates the date value from the defined elements (e.g. 20,110,517,121,922.00 becomes 17/05/2011 in my example).

Remember that you will probably have to work around commas as characters in your data if you are working with strings rather than numeric values.

Re: string to date/time format

johnw — Thu, 19 May 2011 00:14:53 GMT

Typically, you'd use date(date#()) with an appropriate format string. You could handle the example string:

20,110,517,121,922.00

Like this:

date(date#(left(RESOLVED_DATETIME,10),'YY,YYM,MDD'))

Or this if you want to avoid string manipulation entirely (unnecessary):

date(daystart(timestamp#(RESOLVED_DATETIME,'YY,YYM,MDD,hhm,mss.ff')))

string to date/time format

Thu, 19 May 2011 10:33:38 GMT

Hi,

I've tried to use your solution but doesn't work.

I've a a string in my database as 01/01/2011 12:21:34 and I would load as a date/time format in qlikview when I load data in the script.

How can I do?

thanks,

string to date/time format

Thu, 19 May 2011 11:30:41 GMT

John's quite right in highlighting the possible use of other date functions; there are often multiple options available for use depending on your particular data set. Unfortunately, I was unable to utilise the date function in my scenario. Details of date/time or string functions can be found in the reference manual relevant to your implementation of QlikView.

Meanwhile, what exactly have you tried for your example string "01/01/2011 12:21:34"? If you post the relevant script here perhaps we can consider the syntax with you and help point you to a resolution.

string to date/time format

kaushiknsolanki — Thu, 19 May 2011 12:10:03 GMT

Hi,

You can try the below statement.

Date#( FieldName)

Regards,

Kaushik Solanki

Re: string to date/time format

erichshiino — Thu, 19 May 2011 13:10:37 GMT

Hi,

You need to make QlikView recognize that as a timestamp ( date + time).

For you pattern, you need a code like this:

load Timestamp#(ORIGINAL_FIELD, 'DD/MM/YYYY hh:mm:ss') AS NEW_TIMESTAMP_FIELD,

(then the rest of your load script)

FROM ...

For example, if you put the following code on a text box, you will get 2011 as the year part of the timestamp:

=year(Timestamp#('01/01/2011 12:21:34', 'DD/MM/YYYY hh:mm:ss'))

(not sure if your date is DD/MM/YYYY or MM/DD/YYYY you may need to change that!)

Regards,

Erich

Re: string to date/time format

Thu, 19 May 2011 14:00:57 GMT

Hi,

I want to try your solution, but I don't know how, 'cause I've some problem in the script. Where have I put the comand LOAD load Timestamp#(ORIGINAL_FIELD, 'DD/MM/YYYY hh:mm:ss') AS NEW_TIMESTAMP_FIELD FROM ...?

I connect qlikview ro a ODBC and with a script I load data, using a select function. I don't know where insert the command load.

Regards,

FULGENZIO

Re: string to date/time format

erichshiino — Thu, 19 May 2011 14:24:38 GMT

It can be like this:

load *,

Timestamp#(ORIGINAL_FIELD, 'DD/MM/YYYY hh:mm:ss') AS NEW_TIMESTAMP_FIELD ;

sql select --> here is your original select .

You need to replace ORIGINAL_FIELD and NEW_TIMESTAMP_FIELD by the names you want

Re: string to date/time format

Thu, 19 May 2011 14:28:56 GMT

FULGENZIO, Your code should be as follows (But you need to tweak this code as per you requirement)

Load *,Timestamp#(ORIGINAL_FIELD, 'DD/MM/YYYY hh:mm:ss') AS NEW_TIMESTAMP_FIELD;

SQL Select * from TABLENAME;

- Sridhar

Re: string to date/time format

Thu, 19 May 2011 15:07:14 GMT

Hi,

thank you very much. Your solution works!!!

regards,

FULGENZIO