FORMAT_DATE
The FORMAT_DATE function formats an input argument based on a format string. The output is a VARCHAR(255).
Syntax
FORMAT_DATE (input, format_string)
Remarks
• The input argument must be a DATE, TIME, or TIMESTAMP.
• The format_string argument must be a string.
• The format_string is not case-sensitive except as indicated in the following table, which also lists the format string types.
• If input is a DATE, the format_string must not contain any TIME elements such as hour, minute, or seconds.
• If input is a TIME, the format_string must not contain any DATE elements such as year, month, or day of month.
• The output is a string representation of the DATE, TIME, or TIMESTAMP argument based on the format indicated by format_string.
• If the output exceeds 255 characters, it is truncated.
Note: Different data sources return results of FORMAT_DATE in different formats. To make sure TDV is formatting the date, put it in a CSV file and test it from that.
Any leading white space causes a parsing error. Tabs, newlines, the punctuation marks - / , . ; : and embedded or trailing white spaces are acceptable and are passed to the output. Enclose characters in single-quotes (for example, ‘quoted’) if you want them to be passed directly to the output. (The single-quotes are removed.) Use two single-quotes in a row to pass one single-quote to the output.
format_string | Description |
fm | Fill mode. If this is used at the start of format, excess zeroes are suppressed. |
yyyy | 4-digit year ('2006') |
yy | 2-digit year ('06') |
MONTH
Month
month | Full month name ('JULY').
Case is matched. |
MON
Mon
mon | Abbreviated month name ('JUL'). Case is matched. |
mm | Numeric month ('07'; '7' if fill mode). |
DAY
Day
day | Name of day ('FRIDAY').
Case is matched. |
DY
Dy
dy | Abbreviated name of day ('FRI').
Case is matched. |
dd | Day of month ('04'; '4' if fill mode). |
hh | Hour in 12-hour format ('11'). |
hh24 | Hour in 24-hour format ('23'). |
AM am
PM pm | Results are followed by AM or PM string. Case is matched. |
mi | Minute ('59') |
ss | Second ('59'). |
ff | Fractional seconds to millisecond level ('790'; '79' if fill mode). |
Examples
FORMAT_DATE (DATE '2000-02-01', 'Mon mon MON Month month MONTH')
This results in: Feb feb FEB February february FEBRUARY.
FORMAT_DATE (DATE '2001-02-03', 'dd')
This results in: 03.
FORMAT_DATE (DATE '2001-02-03', 'fmdd')
This results in: 3.
FORMAT_DATE (TIME '23:59:01', 'hh hh24:mi:ss')
This results in: 11 23:59:01.