10 Replies Latest reply: May 19, 2011 11:07 AM by fulgenzio RSS

    string to date/time format



      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?




        • 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. 

            • Re: string to date/time format
              John Witherspoon

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




              Like this:




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



              • string to date/time format



                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?



                  • 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. 

                    • Re: string to date/time format
                      Erich Shiino


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