Hive SQL - How to convert a string of date to date type?

In Hive SQL, to convert a string into a DATE type, you use the TO_DATE or CAST/DATE functions depending on the exact format of the string.

---

✅ Common Methods

1. If the string is already in yyyy-MM-dd format

   SELECT TO_DATE('2025-08-27');   -- returns 2025-08-27 (DATE type)

   or

   SELECT CAST('2025-08-27' AS DATE);

    

---

2. If the string has a custom format (e.g. yyyyMMdd, dd-MM-yyyy, etc.)
   First parse it into a timestamp with unix_timestamp, then cast:

   -- Example: string '20250827' (yyyyMMdd)
   SELECT TO_DATE(
              FROM_UNIXTIME(
                  UNIX_TIMESTAMP('20250827', 'yyyyMMdd')
              )
          );
   -- result: 2025-08-27

    

   -- Example: string '27-08-2025' (dd-MM-yyyy)
   SELECT TO_DATE(
              FROM_UNIXTIME(
                  UNIX_TIMESTAMP('27-08-2025', 'dd-MM-yyyy')
              )
          );
   -- result: 2025-08-27

    

---

3. If you need TIMESTAMP instead of DATE

   SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2025-08-27 15:30:00', 'yyyy-MM-dd HH:mm:ss'));

    

   This will give a full TIMESTAMP rather than truncating to the date.

---

📌 Summary:

• Use TO_DATE(string) if it’s already yyyy-MM-dd.

• Use TO_DATE(FROM_UNIXTIME(UNIX_TIMESTAMP(str, format))) if the string has another format.

• Use CAST(... AS DATE) when the string is ISO formatted.

 

In Hive SQL, the function FROM_UNIXTIME(unix_timestamp [, format]) is used to convert a UNIX timestamp (an integer number of seconds since 1970-01-01 00:00:00 UTC) into a string (formatted date/time).

---

🔹 Behavior

1. Without format argument

   SELECT FROM_UNIXTIME(1693113600);

   → Returns a string in yyyy-MM-dd HH:mm:ss format.
   Example result:

   2023-08-27 00:00:00

2. With format argument (custom output)

   SELECT FROM_UNIXTIME(1693113600, 'yyyy-MM-dd');

   → Returns a string formatted according to the given pattern.
   Example result:

   2023-08-27

3. Return type

   • Always returns a STRING (not DATE or TIMESTAMP).

   • If you want a DATE, wrap it with TO_DATE(...).

   • If you want a TIMESTAMP, cast it explicitly:

     SELECT CAST(FROM_UNIXTIME(1693113600) AS TIMESTAMP);

 

In Hive SQL, both TO_DATE() and CAST(... AS DATE) can look similar but behave differently.

---

🔹 TO_DATE()

• Input type: Takes a STRING or TIMESTAMP.

• Behavior: Extracts only the date portion (yyyy-MM-dd) and discards the time part.

• Return type: DATE.

Examples:

 

SELECT TO_DATE('2025-08-27'); -- ✅ 2025-08-27 SELECT TO_DATE('2025-08-27 14:35:20'); -- ✅ 2025-08-27 (time is dropped) SELECT TO_DATE(current_timestamp); -- ✅ today’s date (no time)

---

🔹 CAST(... AS DATE)

• Input type: Requires a STRING in standard ISO format (yyyy-MM-dd) or a TIMESTAMP.

• Behavior: Converts the value directly into DATE without extra parsing flexibility.

• Return type: DATE.

Examples:

SELECT TO_DATE('2025-08-27'); 
-- ✅ 2025-08-27

SELECT TO_DATE('2025-08-27 14:35:20');
-- ✅ 2025-08-27   (time is dropped)

SELECT TO_DATE(current_timestamp);
-- ✅ today’s date (no time)

 

---

🔹 Key Differences

Function	Handles yyyy-MM-dd HH:mm:ss string	Flexible with formats (via unix_timestamp)	Drops time part automatically	Requires strict ISO yyyy-MM-dd
TO_DATE()	✅ Yes	✅ Yes (with unix_timestamp)	✅ Always	❌ Not strict
CAST(... AS DATE)	❌ No (needs pure yyyy-MM-dd)	❌ No	✅ If input is timestamp	✅ Strict ISO format only

---

📌 Rule of thumb:

• Use TO_DATE if your string may include time or is not strictly in yyyy-MM-dd.

• Use CAST(... AS DATE) when you’re sure it’s already in ISO date format.