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

    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

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

               

              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

                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

                    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

                      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