Validate the datetime value - Revision history

Planetoid: /* Verify the date_column is a valid date e.g. YYYY-MM-DD */

2026-05-06T04:29:00Z

Verify the date_column is a valid date e.g. YYYY-MM-DD

@@ Line 24: / Line 24: @@
 * Expected format: YYYY-MM-DD
 <pre>
 SELECT CAST(date_column AS DATE) AS date_column

2026-05-06T04:19:03Z

Verify the value is not 0000-00-00 00:00:00

← Older revision		Revision as of 12:19, 6 May 2026
Line 5:		Line 5:
	== MySQL approach ==		== MySQL approach ==

	=== Verify the value is ~~not~~ 0000-00-00 00:00:00 ===		=== Verify the column value is a valid (non-zero) datetime e.g. 0000-00-00 00:00:00 ===
	Schema of column value		Schema of column value

Line 15:		Line 15:
	FROM `my_table`		FROM `my_table`
	WHERE `my_date_column` LIKE '1900-%';		WHERE `my_date_column` LIKE '1900-%';
			</pre>


			=== Verify the date_column is a valid date e.g. YYYY-MM-DD ===
			Schema of column date_column

			* Type: {{kbd \| key=varchar}} or {{kbd \| key=date}}
			* Expected format: YYYY-MM-DD

			<pre>
			SELECT CAST(date_column AS DATE) AS date_column
			FROM my_table
			WHERE CAST(date_column AS DATE) IS NULL
			AND date_column IS NOT NULL;
	</pre>		</pre>

2023-09-17T07:38:15Z

← Older revision		Revision as of 15:38, 17 September 2023
Line 9:		Line 9:

	* Type: {{kbd \| key=datetime}}		* Type: {{kbd \| key=datetime}}
	* Default value: 0000-00-00 00:00:00 (NOT allow be NULL)		* Default value: 0000-00-00 00:00:00 (NOT allow be NULL)、"1999/12/31 上午 12:00:00" on Google sheet

	<pre>		<pre>
Line 23:		Line 23:
	* Default value: allow be NULL		* Default value: allow be NULL

	Abnormal values e.g. {{kbd \| key=1900-01-08 00:00:00}} because the Excel datetime issue<ref>[https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487 Date systems in Excel - Microsoft Support]</ref>~~、"1999/12/31 上午 12:00:00" on Google sheet~~		Abnormal values e.g. {{kbd \| key=1900-01-08 00:00:00}} because the Excel datetime issue<ref>[https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487 Date systems in Excel - Microsoft Support]</ref>.

	<pre>		<pre>

2023-09-12T10:15:28Z

Verify the year of value is not 1900

← Older revision		Revision as of 18:15, 12 September 2023
Line 17:		Line 17:
	</pre>		</pre>

	=== Verify the year of value is not 1900 ===		=== Verify the year of value is not 1900 or 1999 years ===
	Schema of column value		Schema of column value

2023-09-12T10:15:08Z

Verify the year of value is not 1900

← Older revision		Revision as of 18:15, 12 September 2023
Line 23:		Line 23:
	* Default value: allow be NULL		* Default value: allow be NULL

	Abnormal values e.g. {{kbd \| key=1900-01-08 00:00:00}} because the Excel datetime issue<ref>[https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487 Date systems in Excel - Microsoft Support]</ref>		Abnormal values e.g. {{kbd \| key=1900-01-08 00:00:00}} because the Excel datetime issue<ref>[https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487 Date systems in Excel - Microsoft Support]</ref>、"1999/12/31 上午 12:00:00" on Google sheet

	<pre>		<pre>

2023-02-13T08:52:19Z

Verify the value is not 1900-01-xx xx:xx:xx

← Older revision		Revision as of 16:52, 13 February 2023
Line 17:		Line 17:
	</pre>		</pre>

	=== Verify the value is not 1900~~-01-xx xx:xx:xx~~ ===		=== Verify the year of value is not 1900 ===
	Schema of column value		Schema of column value

2023-02-13T08:49:09Z

Verify the value is null

← Older revision		Revision as of 16:49, 13 February 2023
Line 45:		Line 45:
	Schema of column value		Schema of column value

	* Type: {{kbd \| key=datetime}}		* Type: {{kbd \| key=datetime}} or {{kbd \| key=VARCHAR}}
	* Default value: allow be NULL		* Default value: allow be NULL

2023-02-13T08:47:45Z

MySQL approach

← Older revision		Revision as of 16:47, 13 February 2023
Line 8:		Line 8:
	Schema of column value		Schema of column value

	* Type: datetime		* Type: {{kbd \| key=datetime}}
	* Default value: 0000-00-00 00:00:00 (NOT allow be NULL)		* Default value: 0000-00-00 00:00:00 (NOT allow be NULL)

Line 20:		Line 20:
	Schema of column value		Schema of column value

	* Type: VARCHAR		* Type: {{kbd \| key=VARCHAR}}
	* Default value: allow be NULL		* Default value: allow be NULL

Line 45:		Line 45:
	Schema of column value		Schema of column value

	* Type: datetime		* Type: {{kbd \| key=datetime}}
	* Default value: allow be NULL		* Default value: allow be NULL

2023-02-13T08:40:05Z

Verify the value is not 1900-01-20 xx:xx:xx

← Older revision		Revision as of 16:40, 13 February 2023
Line 17:		Line 17:
	</pre>		</pre>

	=== Verify the value is not 1900-01-20 xx:xx:xx ===		=== Verify the value is not 1900-01-xx xx:xx:xx ===
	Schema of column value		Schema of column value

	* Type: VARCHAR		* Type: VARCHAR
	* Default value: allow be NULL		* Default value: allow be NULL

			Abnormal values e.g. {{kbd \| key=1900-01-08 00:00:00}} because the Excel datetime issue<ref>[https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487 Date systems in Excel - Microsoft Support]</ref>

	<pre>		<pre>

2023-02-13T08:37:12Z

Verify the value is not 1900-01-20 xx:xx:xx