## User defined format for parameters

How can you define formats for different types of parameters? The most commonly used are the Date and Time formats.

### Date format syntax

To specify the time format, use a time pattern string. In this pattern, all ASCII letters are reserved as pattern letters, and are defined as follows:

Symbol | Meaning | Presentation | Example
--- | --- | --- | ---
G | Era designator | Text | AD
y | Year | Number | 1996
M | Month in a year | Text & Number | July & 07
d | Day in a month | Number | 10
h | Hour in am/pm (1~12) | Number | 12
H | Hour in a day (0~23) | Number | 0
m | Minute in an hour | Number | 30
s | Second in a minute | Number | 55
S | millisecond | Number | 978
E | Day in a week | Text | Tuesday
D | Day in a year | Number | 189
F | Day of the week in a month | Number | 2 (2nd Wed in July)
w | Week in a year | Number | 27
W | Week in a month | Number | 2
a | Am/pm marker | Text | PM
k | Hour in a day (1~24) | Number | 24
K | Hour in am/pm (0~11) | Number | 0
z | Time zone | Text | Pacific Standard Time
' | Escape for text |  |
'' | Single quote |  |

Notes:

- The count of pattern letters determines the format.
- Text: 4 or more pattern letters -- use full form, < 4 -- use short or abbreviated form if one exists.
- Number: the minimum number of digits. Shorter numbers are zero-padded to this amount. However, Year is handled in a different way. If the number of letter 'y' is 2, then the year will be truncated to 2 digits.
- Text & Number: 3 or over, uses text, otherwise uses number.
- Any characters in the pattern that are not in the ranges of ['a'..'z'] and ['A'..'Z'] will be treated as quoted text. For example, characters such as ':', '.', ' ', '#' and '@' will appear in the resulting time text even if they have not been single quoted.
- A pattern containing any invalid pattern letter will result in a thrown exception during formatting or parsing.

Examples

- Format Pattern: "yyyy.MM.dd G 'at' hh:mm:ss z"
  Result: 1996.07.10 AD at 15:08:56 PDT
- Format Pattern: "EEE, MMM d, ''yy"
  Result: Wed, July 10, '96
- Format Pattern: "h:mm a"
  Result: 12:08 PM
- Format Pattern: "hh 'o''clock' a, zzzz"
  Result: 12 o'clock PM, Pacific Daylight Time
- Format Pattern: "K:mm a, z"
  Result: 0:00 PM, PST
- Format Pattern: "yyyyy.MMMMM.dd GGG hh:mm aaa"
  Result: 1996.July.10 AD 12:08 PM

### Decimal number format syntax

Symbol | Meaning | Notes
--- | --- | ---
0 | A digit |
* | A digit, zero shows as a star | Can't mix 0, *, and _ in same format
_ | A digit, zero shows as a space | Can't mix 0, *, and _ in same format
# | A digit, zero shows as absent |
. | Placeholder for decimal separator |
, | Placeholder for grouping delimiter | Shows the interval to be used
; | Separates formats | positive and negative.
- | If there is no explicit negative sign, - is prefixed | "0.00" -> "0.00;-0.00"
% | Divides by 100 and shows as a percentage |
X | Any other characters can be used in the prefix or suffix |

### String format syntax

Symbol | Meaning | Example
--- | --- | ---
% | Any string of zero or more characters. | WHERE title LIKE '%computer%' finds all book titles with the word 'computer' anywhere in the book title.
_ (underscore) | Any single character. | WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ] | Any single character within the specified range ([a-f]) or set ([abcdef]). | WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and beginning with any single character between C and P, for example Carsen, Larsen, Karsen, and so on.
[^] | Any single character not within the specified range ([^a-f]) or set ([^abcdef]). | WHERE au_lname LIKE 'de[^l]%' all author last names beginning with de and where the following letter is not l.