7 Replies Latest reply: Apr 15, 2014 11:27 AM by Melissa van Kluyve RSS

    Removing Time from timestamp

      Hello All,

       

           I need a clarification about text - Date format. I have extracted data from csv file with a field called record_date which has the timestamp values in text format. Take a look at the below pic.

       

           Untitled.jpg

       

      I want to remove the time from record_date Please help me in this.

       

      Thanks in advance.

        • Removing Time from timestamp
          Kennie Nugroho

          Hi Rajan,

           

          If you want to convert from text/string into date format, you can use the DATE# function, which I think will result in the time data to be lost.

           

          So in your case, I think it should be like this:

           

          DATE#( record_date, 'M/D/YYYY HH:MM' ) AS date;

           

          I haven't actually tested if you can force DATE# on a timestamp string, but if QlikView can't, then you can just use TIMESTAMP# combined with DATE:

           

          DATE( TIMESTAMP#( record_date, 'M/D/YYYY HH:MM' ), 'M/D/YYYY' ) AS date;

           

          Of course you can just use only TIMESTAMP# and set your default Numbering Format of that field to just show the date (via Document Settings or individually on each object/chart you're using that field in).

           

          --Kennie

            • Re: Removing Time from timestamp
              John Witherspoon

              In the format string, you need hh:mm instead of HH:MM.  MM stands for the month, not the minute.  Lower case gives you the minute.  When reading in a timestamp, use the timestamp# function.  You could use string functions to strip off the time, then use the date# function, but I'd just use the timestamp# function.  However, you can't just wrap that in a date() function.  Date() is a FORMATTING function, and will not change the underlying data, so you will still have a timestamp including the time, even if it displays as a date.  Use floor() or daystart() to remove the fractional part.  Or if by "remove the time" your meant "create a time field", that's basically the same thing, but with time() and frac().

               

              ,date(floor(timestamp#(record_date,'M/D/YYYY hh:mm'))) as record_date
              ,time(frac (timestamp#(record_date,'M/D/YYYY hh:mm'))) as record_time

              • Re: Removing Time from timestamp

                Hi Kennie,

                 

                     Thanks to you also. I got an initial idea from u. but some of the values were null because of that HH:MM. I have corrected with help of John. Thanks to you Kennie.