Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: How do I add leading zeros to a numeric data field in Hive QL?

  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Default How do I add leading zeros to a numeric data field in Hive QL?

    I have a Select statement wherein I'm trying to subtract one date from another resulting in an output of days. The different date components are stored in different columns, so I want to concatenate them, then subtract one from the other. However due to the nature of months and days (single digit or double digit, this is proving to be very difficult (eg. May 12th - May 3rd looks like 512 - 53). I tried to use the "RIGHT" function from SQL, but that doesn't appear to be working in Hive QL.

    ex.
    select
    reg.uid
    ,CONCAT(reg.month,reg.day) regist_date
    ,CONCAT(log.month,log.day) login_date
    ,RIGHT('00000'+ CONVERT(VARCHAR,CONCAT(reg.month,reg.day),6)) as NUM

    Help is greatly appreciated. Thanks!

  2. #2
    jdixon Guest

    Default

    Its very hard to subtract dates this way. July 1st will be 601 and June 30th will be 530. When you substract them you will get 70 days difference instead of the correct result of 1.

    Take a look at the Hive date functions. You need to use a function like datediff: https://cwiki.apache.org/Hive/langua...-DateFunctions

    James

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.