2 Replies Latest reply: Aug 30, 2010 7:27 PM by Oleg Troyansky RSS

    turning text into date in script

    dcpy

      I have a couple tables where the dates in the older files come thru as text

      2007/12/01 00:00:00

      1/12/2007 00:00

      I want to convert these to dates in the script, is this possible? Qlikview does not recognize them as dates even if I use teh date() function. I tried teh below but I get Error in expression ')' expected

       

      if
      (mid(Date,5) = '/' ,

      left(Date,4) & '/' & mid(Date,6,2) & '/' & mid(Date,9,2,) as Date,

      Date as Date),

        • turning text into date in script
          Vlad Gutkovsky

          You need to use the timestamp# transformation in combination with date(). So something like this:

           


          date(timestamp#(datefield,'MM/DD/YYYY hh:mm')) as Date


          Or:

           


          if(left(datefield,5)='/',
          date(timestamp#(datefield,'YYYY/MM/DD hh:mm')),
          date(timestamp#(datefield,'MM/DD/YYYY hh:mm'))
          ) as Date


          Regards,

          • turning text into date in script
            Oleg Troyansky

            Look up functions date#() and date() in the Help Section - the first function is casting string into a date, and the second one formats the date to a desired date format.

            Generally speaking, QlikView Help Section is organized fairly logically - if you need "Date Functions", search for the string and you'll typically find what you need...