Date time format across systems

	A	B	C	D	E	F	G	I	J
1	This is a comparison between the use of datetime format patterns across RDBMS'.
2	Note, this doc tries to cover the pattern elements accepted by each system and does not cover all the semantics related details. (e.g. What to do when "YYYY" format is compared with "018" as input)
3	Note2, the information in the table below is from each system's documentation (links on the right) and not neccessarily based on running actual SQL queries.
4
5		Impala-SimpleDateFormat	SQL:2016	Oracle 18	PostgreSQL 11	Vertica 9	IBM DB2 11.1		Not supported
6	4 digit Year	yyyy	YYYY	YYYY	YYYY	YYYY	YYYY
7	Signed year			SYYYY
8	More than 4 digit Year				YYYY with a non-digit separator	YYYY
9	last 3 digit of Year	yyy	YYY	YYY	YYY	YYY	YYY (First digit from current year)	Sources:
10	last 2 digit of Year	yy - This is 2 digit round year	YY	YY	YY	YY	YY (First 2 digits from current year)	Impala-Java format:	https://github.com/apache/impala/blob/master/be/src/runtime/datetime-parse-util.h
11	last 1 digit of Year	y - Reads until the first separator and acts accoring to how many digits it finds.	Y	Y	Y	Y	Y (First 3 digits from current year)	SQL:2016	https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016
12	4 digit round year		RRRR	RRRR			RRRR	Oracle	https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Format-Models.html#GUID-EAB212CF-C525-4ED8-9D3F-C76D08EEBC7A
13	2 digit round year		RR	RR			RR	PostgreSQL	https://www.postgresql.org/docs/11/functions-formatting.html
14	Year with comma			Y,YYY	Y,YYY	Y,YYY		Vertica	https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Formatting/TemplatePatternsForDateTimeFormatting.htm?tocpath=SQL%20Reference%20Manual%7CSQL%20Functions%7CFormatting%20Functions%7CTemplate%20Patterns%20for%20Date%2FTime%20Formatting%7C_____0
15	4digit year containing calendar week			IYYY	IYYY	IYYY		IBM DB2	https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0007107.html
16	3digit year containing calendar week			IYY	IYY	IYY
17	2digit year containing calendar week			IY	IY	IY
18	1digit year containing calendar week			I	I	I
19	Spelled out year			YEAR \| SYEAR
20	Month	MM	MM	MM	MM	MM	MM
21	Month 1 digit	M
22	Name of Month	MMM		MONTH \| Month \| month (Locale dependent)	MONTH \| Month \| month	MONTH \| Month \| month	MONTH \| Month \| month (Locale dependent)
23	Abbrev. name of Month			MON \| Mon \| mon (Locale dependent)	MON \| Mon \| mon	MON \| Mon \| mon	MON \| Mon \| mon (Locale dependent)
24	Day of Month (1-31)	dd	DD	DD	DD	DD	DD
25	Day 1 digit	d
26	Day of Year (1-366)		DDD	DDD	DDD	DDD	DDD
27	Day of week (1-7)			D	D	D (Sunday is 1)	D (Locale dependent)
28	Day of ISO week-numbering year (1st day is Monday)				IDDD
29	Name of day			DAY	Day \| Day \| day	Day \| Day \| day	Day \| Day \| day (Locale dependent)
30	Abbrev. name of day			DY	DY \| Dy \| dy	DY \| Dy \| dy	DY \| Dy \| dy (Locale dependent)
31	12 hour		HH \| HH12	HH \| HH12	HH \| HH12	HH12	HH \| HH12
32	24 hour	HH	HH24	HH24	HH24	HH \| HH24	HH24
33	Hour 1 digit	H
34	Minute	mm	MI	MI	MI	MI	MI
35	Minute 1 digit	m
36	Second of Minute	ss	SS	SS	SS	SS	SS
37	Second of Day (0-86399)		SSSSS	SSSSS	SSSS	SSSS	SSSSS
38	Second 1 digit	s
39	Fractional Second	S \| ... \| SSSSSSSSS	FF[1..9]	FF[1..9]			FF[1..12]
40	Millisecond (000-999)				MS	MS
41	Microsecond (00000-999999)				US	US	NNNNNN Same as FF6
42	AM		A.M.	AM \| A.M.	AM \| A.M. \| am \| a.m.	AM \| A.M. \| am \| a.m.	AM \| A.M. (Locale dependent)
43	PM		P.M.	PM \| P.M.	PM \| P.M. \| pm \| p.m.	PM \| P.M. \| pm \| p.m.	PM \| P.M. (Locale dependent)
44	Timezone hour	At the end of the format string: +/-hh:mm +/-hhmm +/-hh	TZH	TZH	TZH
45	Timezone min	See above	TZM	TZM	TZM
46	Daylight saving info			TZD
47	Timezone region			TZR
48	Timezone name				TZ \| tz	TZ \| tz
49	Timezone offset from UTC				OF
50	Separator	Any non-alphanumeric char	- . / , ' ; : <space>	- . / , ; : <space>	Not mentioned in docs	Not mentioned in docs	- . / , ' ; : <space> Separator in format doesn't have to match the separator in input. e.g. "YYYY/MM" matches "2019-11"
51	Nested strings			"text"	"text"	Not mentioned in docs
52	Non pattern chars			Error	Copied verbatim	Copied Verbatim
53	AD			AD \| A.D.	AD \| A.D. \| ad \| a.d.	AD \| A.D. \| ad \| a.d.
54	BC			BC \| B.C.	BC \| B.C. \| bc \| b.c.	BC \| B.C. \| bc \| b.c.
55	Century			CC	CC	CC
56	Signed century			SCC
57	Fill mode modifiers			FM \| FX (Prefix, affects whole input)	FM \| FX (Affects next 'section' only)	FM \| FX (Affects next 'section' only)
58	ISO Week of year (1-53)			IW	IW	IW
59	Week of year from 1st Jan			WW	WW	WW
60	Week of Month from 1st day of Month			W	W	W
61	Julian day (# of days since January 1, 4712 BC)			J	J	J	J
62	Quarter of year (1-4)			Q	Q	Q
63	Roman numeral month			RM	RM \| rm	RM \| rm
64	Local radix char (e.g. "3.1" vs "3,1")			X
65	Ordinal number suffix (th, rd, nd)			TH	TH \| th	TH \| th
66	Spelled number			SP
67	Spelled, ordinal number			SPTH \| THSP
68	Translation mode (print localized names)				TM (Basically this uses locale for patterns like 'Mon' etc) to_char(my_date, 'TMMonth')	TM (Basically this uses locale for patterns like 'Mon' etc) to_char(my_date, 'TMMonth')
69	Julian day modifier					JULIAN \| JD \| J
70	Next field is time					T
71	Case insensitive format elements						With some exceptions the format elements are case insensitive
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100