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.