Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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

View solution in original post

10 Replies
Not applicable
Author

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. 

johnw
Champion III
Champion III

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
Author

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
Author

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. 

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

    You can try the below statement.

    Date#( FieldName)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
erichshiino
Partner - Master
Partner - Master

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
Author

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

erichshiino
Partner - Master
Partner - Master

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
Author

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