SQL Reference
Formatting Functions
formatting considerations the format string can be any combination of characters and format patterns for each function specified in the formatting functions docid\ g ia4znpotfx2kegcvv2r and formatting functions docid\ g ia4znpotfx2kegcvv2r tables patterns can be uppercase or lowercase, but not mixed case yyyy or yyyy matches yyyy, but not yyyy any characters in the format string that are not part of a pattern match any character in that position in to date, to number, and to timestamp you can escape string literals that contain format patterns with quotes for example yyyy"a literal part yyyy" date and time formatting functions date and timestamp formatting patterns pattern description y,yyy 4 digit year with comma yyyy 4 digit year yyy last 3 digits of a year values between 000 599 are assumed to be the year 2xxx and 600 999 are assumed to be the year 1xxx yy last 2 digits of a year values between 00 69 are assumed to be the year 20xx and 70 99 are assumed to be 19xx y last digit of a year assumed to be year 200x bc, bc, ad, or ad an era indicator bc or ad in upper or lower case b c , b c , a d , or a d an era indicator b c or a d in upper or lower case cc century value 1 99 is ignored unless yy, y, iy, or i are set mm month of the year value 01 12 month upper case english month name month english month name with first letter capitalized month lower case english month name rm upper case month of year in roman numerals rm lower case month of year in roman numerals mon 3 letter upper case abbreviation of an english month name mon 3 letter abbreviation of an english month name with first letter capitalized mon 3 letter lower case abbreviation of an english month name w week of month 1 5 for example days 15 21 are in the third week of the month dd day of month 1 31 ww week of year 1 53 ddd day of year 1 366 d day of week 1 7 where sunday is 1 this value is ignored outside of to char iyyy 4 digit iso 8601 week numbered year see for more details iyy last 3 digits of an iso 8601 week numbered year values between 000 599 are assumed to be the year 2xxx and 600 999 are assumed to be the year 1xxx iy last 2 digits of an iso 8601 week numbered year values between 00 69 are assumed to be the year 20xx and 70 99 are assumed to be 19xx i last digit of an iso 8601 week numbered year assumed to be the year 200x iddd day of an iso 8601 week numbered year 001 371 iw week of an iso 8601 week numbered year 01 53 id day of an iso 8601 week 01 07 where monday is 1 hh or hh12 hour of day 01 12 hh24 hour of day 0 23 am, am, pm, or pm meridiem indicator am or pm in upper or lower case a m , a m , p m , or p m meridiem indicator a m or p m in upper or lower case ssss seconds after midnight 0 86399 mi minute of hour 0 59 ss second of minute 0 59 ms milliseconds 000 999 us microseconds 000000 999999 ns nanoseconds 000000000 999999999 j julian day (days after november 24, 4714 bc at midnight) the following modifiers can also be applied to a format pattern date and timestamp formatting modifiers modifier description fm has no effect on to date and to timestamp will not be considered a literal, but will not otherwise affect the behavior th upper case ordinal number suffix for example 'ddth' th lower case ordinal number suffix for example 'ddth' fx global prefix by default, to date and to timestamp collapse all spaces to a single space if fx is specified, spaces are not collapses fx must be at the beginning of the format string to be applied for example 'fx yyyy mm dd' tm has no effect on to date and to timestamp will not be considered a literal, but will not otherwise affect the behavior sp has no effect on to date and to timestamp will not be considered a literal, but will not otherwise affect the behavior date and time notes you can use time specifiers such as hh12 in to date their format will be validated, but their values will not affect the resulting date iso 8601 formats for dates can not be mixed with traditional formats for years, months, and days in to date and to timestamp values for ms, us, and ns are scaled up if they do not have leading zeros to timestamp('0 3', 's ns') corresponds to 300000000 ns and to timestamp('0 000000003', 's ns') corresponds to 3 ns conflicting information for the same pattern will cause an exception ex to date('05 31', 'dd dd') conflicting information across different patterns such as dd and ddd will generally give precedence to the pattern that appears higher in the table to timestamp converts a character value with the specified format to a timestamp type syntax to timestamp(character value, character format) arguments data type description character value char a string of a value to be converted to a timestamp type the string must match the provided character format character format char the format pattern to be used to convert the character value see the formatting functions docid\ g ia4znpotfx2kegcvv2r section for more information about accepted pattern values examples select to timestamp('2022/12/01', 'yyyy/mm/dd'); output 2022 12 01 00 00 00 000 select to timestamp('2023 02 28 13 43 20 403', 'yyyy/mm/dd hh24\ mi\ ss'); output 2023 02 28 13 43 20 000 to date converts a character value with the specified format to a date type syntax to date(character value, character format) arguments data type description character value char a string of a value to be converted to a date type the string must match the provided character format character format char the format pattern to be used to convert the character value see the formatting functions docid\ g ia4znpotfx2kegcvv2r section for more information about accepted pattern values example select to date('2022/12/01', 'yyyy/mm/dd'); output 2022 12 01 number formatting functions number formatting patterns pattern description 9 a digit 0 a digit in to char, includes leading zeros or d a decimal point , or g a group/thousand separator pr negative value in braces for example 1 = <1> s or sg a + or sign for positive or negative, respectively mi a sign if the number is negative pl a + sign if the number is positive v shifts the decimal place to the by the number of digits after v in the format cannot be mixed with or d for example to number('32', '9v99') => 0 32 rn roman numeral not supported by to number eeee exponent for scientific notation not supported by to number l currency symbol number formatting notes you cannot mix pr with other sign indicators any character that does not match the corresponding group in the format string is ignored to number('1&2', '999') = 12 if pr is present, it must be after every 0 or 9 in the format string similar to to date and to timestamp, the fm is not considered a literal, but will not otherwise affect the behavior of to number to number converts a character value with the specified format to a decimal type syntax to number(character value, character format) arguments data type description character value char a string of a value to be converted to a decimal type the string must match the provided character format character format char the format pattern to be used to convert the character value see the formatting functions docid\ g ia4znpotfx2kegcvv2r section for more information about accepted pattern values example select to number('5230 87', '9999v99'); output 5230 87 related links date and time functions docid\ ollvrg4vjupmvwhgpupdu time zone functions docid\ f8elldnuxgrzirili8c4p character and binary functions docid\ t4v9mfehdtbx8w 6ipulp