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 (2^nd 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

Symbol

Meaning

Presentation

Example

Era designator

Text

Year

Number

1996

Month in a year

Text & Number

July & 07

Day in a month

Number

Hour in am/pm (1~12)

Number

Hour in a day (0~23)

Number

Minute in an hour

Number

Second in a minute

Number

millisecond

Number

978

Day in a week

Text

Tuesday

Day in a year

Number

189

Day of the week in a month

Number

2 (2^nd Wed in July)

Week in a year

Number

Week in a month

Number

Am/pm marker

Text

Hour in a day (1~24)

Number

Hour in am/pm (0~11)

Number

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

Symbol

Meaning

Notes

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

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.

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.