# Datetime format strings
<a name="FORMAT_strings"></a>

You can use datetime patterns in the following common scenarios:
+ When working with CSV and JSON data sources to parse and format datetime content
+ When converting between string types and date or timestamp types using functions such as:
  + unix\$1timestamp
  + date\$1format
  + to\$1unix\$1timestamp
  + from\$1unixtime
  + to\$1date
  + to\$1timestamp
  + from\$1utc\$1timestamp
  + to\$1utc\$1timestamp

Use the pattern letters in the following table for date and timestamp parsing and formatting.

[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/clean-rooms/latest/sql-reference/FORMAT_strings.html)

The number of pattern letters determines the format type:

Text Format
+ Use 1-3 letters for the abbreviated form (for example, "Mon" for Monday)
+ Use exactly 4 letters for the full form (for example, "Monday")
+ Don't use 5 or more letters - this will cause an error

Number Format (n)
+ The value n represents the maximum number of letters allowed
+ For single letter patterns: 
  + Output uses minimum digits without padding
+ For multiple letter patterns: 
  + Output is padded with zeros to match the letter count width
+ When parsing, input must contain the exact number of digits

Number/Text Format
+ For 3 or more letters, follow the Text Format rules
+ For fewer letters, follow the Number Format rules

Fraction Format
+ Use 1-9 'S' characters (for example, SSSSSS)
+ For parsing: 
  + Accept fractions between 1 and the number of S characters
+ For formatting: 
  + Pad with zeros to match the number of S characters
+ Supports up to 6 digits for microsecond precision
+ Can parse nanoseconds but truncates extra digits

Year Format
+ The letter count sets the minimum field width for padding
+ For two letters: 
  + Prints the last two digits
  + Parses years between 2000-2099
+ For less than four letters (except two): 
  + Shows the sign only for negative years
+ Don't use 7 or more letters - this will cause an error

Month Format
+ Use 'M' for standard form or 'L' for standalone form
+ Single 'M' or 'L': 
  + Shows month numbers 1-12 without padding

  
+ 'MM' or 'LL': 
  + Shows month numbers 01-12 with padding
+ 'MMM': 
  + Shows abbreviated month name in standard form
  + Must be part of a full date pattern
+ 'LLL': 
  + Shows abbreviated month name in standalone form
  + Use for month-only formatting
+ 'MMMM': 
  + Shows full month name in standard form
  + Use for dates and timestamps
+ 'LLLL': 
  + Shows full month name in standalone form
  + Use for month-only formatting

Time Zone Formats
+ am-pm: Use 1 letter only
+ Zone ID (V): Use 2 letters only
+ Zone names (z): 
  + 1-3 letters: Shows short name
  + 4 letters: Shows full name
  + Don't use 5 or more letters

Offset Formats
+ X and x: 
  + 1 letter: Shows hour (\$101) or hour-minute (\$10130)
  + 2 letters: Shows hour-minute without colon (\$10130)
  + 3 letters: Shows hour-minute with colon (\$101:30)
  + 4 letters: Shows hour-minute-second without colon (\$1013015)
  + 5 letters: Shows hour-minute-second with colon (\$101:30:15)
  + X uses 'Z' for zero offset
  + x uses '\$100', '\$10000', or '\$100:00' for zero offset
+ O: 
  + 1 letter: Shows short form (GMT\$18)
  + 4 letters: Shows full form (GMT\$108:00)
+ Z: 
  + 1-3 letters: Shows hour-minute without colon (\$10130)
  + 4 letters: Shows full localized form
  + 5 letters: Shows hour-minute-second with colon

Optional Sections
+ Use square brackets [ ] to mark optional content
+ You can nest optional sections
+ All valid data appears in output
+ Input can omit entire optional sections

**Note**  
The symbols 'E', 'F', 'q', and 'Q' work only for datetime formatting (like date\$1format). Don't use them for datetime parsing (like to\$1timestamp).