The date_format() function in Hive is used to format a date or timestamp value as a string, according to a specified format string.
The syntax of the date_format() function is as follows:
date_format(date/timestamp, format)
Here, date/timestamp is the input date or timestamp value that you want to format, and format is the format string that specifies how the output string should be formatted.
The format string consists of a combination of formatting codes and literal characters. The formatting codes are placeholders that represent specific date or time components, such as year, month, day, hour, minute, and second, and the literal characters are fixed characters that are included in the output string as-is.
Here are some examples of formatting codes that you can use in the format string:
- %Y: 4-digit year
- %m: 2-digit month (01-12)
- %d: 2-digit day of the month (01-31)
- %H: 2-digit hour (00-23)
- %M: 2-digit minute (00-59)
- %S: 2-digit second (00-59)
- %b: abbreviated month name (Jan-Dec)
- %a: abbreviated weekday name (Sun-Sat)
For example, to format the date ‘2022-03-05′ as ’05/Mar/2022’, you can use the following query
SELECT date_format(‘2022-03-05’, ‘dd/MMM/yyyy’);
Similarly, to format the timestamp ‘2022-03-05 15:30:00’ as ‘Saturday, March 05, 2022’, you can use the following query:
SELECT date_format(‘2022-03-05 15:30:00’, ‘EEEE, MMMM dd, yyyy’);
Note that the date_format() function only works with date and timestamp values, not with strings. If you need to parse a string as a date or timestamp value, you can use the cast() function to convert it to the appropriate data type