Teradata PT provides a set of formatting characters so FORMATIN strings can represent all types of incoming DateTime data and FORMATOUT strings can represent all database formats, both default and user defined.
The following table shows the formatting characters for the FORMATIN and FORMATOUT strings that Teradata PT supports.
| Formatting Character | Description | Examples |
|---|---|---|
| Separators | Any non-alphanumeric character that separates two alphanumeric formatting characters. | "/": Slash Separator ",": Comma Separator "'": Apostrophe Separator ":": Colon Separator ".": Period Separator "D": Period Separator "-": Dash Separator "B": Space Separator “ “: Space Separator |
| YY | A two-digit year that is valid for the calendar, numeric characters only. When converting a two-digit year into a four-digit year, if the two-digit year is less than or equal to 68, it is prepended with '20'; otherwise the two-digit year is prepended with '19'. | 03 (stored as 1903 when converted to four digit year) |
| YYYY Y4 |
A four-digit year that is valid for the calendar, numeric characters only. | 2003 |
| MM | The month of the year as two numeric characters that are valid for the calendar. | 02 |
| MMM M3 |
A three-character month that matches one of the names specified by ShortMonths in the current Specification for Data Formatting (SDF) file. | Jan Feb May |
| MMMM M4 |
A full month name that matches one of the names specified by LongMonths in the current SDF. | January February May |
| EEE E3 |
An abbreviated day of the week name that matches one of the names specified by ShortDays in the current SDF. | Fri |
| EEEE E4 |
A day of the week name that matches one of the names specified by LongDays in the current SDF. | Friday |
| DD | The day of the month as two numeric characters that are valid for the calendar. | 03 24 |
| HH | Represents the hour as two numeric digits. | 23 |
| MI | Represents the minute as two numeric digits. | 59 |
| SS | Represents the second as two numeric digits. | 01 |
| SS.S(N) | Represents the second as two numeric
digits followed by a decimal point and up to six numeric digits
representing fractional seconds. The user must define the precision of the fractional seconds, where N is less than or equal to 6 and greater than or equal to 0. |
12:51:10.9538 ('HH:MM:SS.S(4)') |
| T | Represents time in 12-hour format instead of 24-hour format. The appropriate time of day, as specified by AMPM in the current SDF is copied to the output string where a T appears in the FORMAT phrase. | 01:20:01PM ('HH:MI:SST') |
| Z | Time zone. The Z controls the placement of the time zone in the output of TIMESTAMP data, and can only appear at the beginning or end of the time formatting characters. |
98-01-01 +00:00 ('YY-MM-DDBZ') |