Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

string to date/time format

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

1 Solution

Accepted Solutions
Not applicable

Re: string to date/time format

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

10 Replies
Not applicable

string to date/time format

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. 

MVP
MVP

Re: string to date/time format

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')))

Not applicable

string to date/time format

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,

Not applicable

string to date/time format

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

Hi,

    You can try the below statement.

    Date#( FieldName)

Regards,

Kaushik Solanki

erich_shiino
Honored Contributor

Re: string to date/time format

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

Not applicable

Re: string to date/time format

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

erich_shiino
Honored Contributor

Re: string to date/time format

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

Not applicable

Re: string to date/time format

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

Community Browser