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:
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.
Typically, you'd use date(date#()) with an appropriate format string. You could handle the example string:
Or this if you want to avoid string manipulation entirely (unnecessary):
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.
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)
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!)
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.