Validate the datetime value: Difference between revisions

From LemonWiki共筆
Jump to navigation Jump to search
mNo edit summary
Line 3: Line 3:
== MySQL approach ==
== MySQL approach ==
<pre>
<pre>
SELECT `my_date_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y-%m-%d'))  
SELECT `my_date_column`, STR_TO_DATE(`my_date_column`, '%Y-%m-%d')
FROM `my_table`
FROM `my_table`
WHERE
WHERE
UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y-%m-%d')) IS NULL;
STR_TO_DATE(`my_date_column`, '%Y-%m-%d') IS NULL;


</pre>
</pre>
Line 12: Line 12:
Verify the value should be year/month/day format
Verify the value should be year/month/day format
<pre>
<pre>
SELECT `my_date_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y/%m/%d'))  
SELECT `my_date_column`, STR_TO_DATE(`my_date_column`, '%Y/%m/%d')
FROM `my_table`
FROM `my_table`
WHERE
WHERE
UNIX_TIMESTAMP(STR_TO_DATE(`my_date_column`, '%Y/%m/%d')) IS NULL;
STR_TO_DATE(`my_date_column`, '%Y/%m/%d') IS NULL;


</pre>
</pre>
Line 21: Line 21:
Verify the value should be hour:minute:second format e.g. {{CURRENTTIME}}:06
Verify the value should be hour:minute:second format e.g. {{CURRENTTIME}}:06
<pre>
<pre>
SELECT `my_time_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S'))  
SELECT `my_time_column`, STR_TO_DATE(`my_time_column`, '%H:%i:%S')  
FROM `my_table`
FROM `my_table`
WHERE
WHERE
UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%H:%i:%S')) IS NULL;
STR_TO_DATE(`my_time_column`, '%H:%i:%S') IS NULL;


</pre>
</pre>
Line 30: Line 30:
Verify the value should be year-month-day hour:minute:second format e.g. {{Template:Today}} {{CURRENTTIME}}:06
Verify the value should be year-month-day hour:minute:second format e.g. {{Template:Today}} {{CURRENTTIME}}:06
<pre>
<pre>
SELECT `my_time_column`, UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S'))  
SELECT `my_time_column`, STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S')  
FROM `my_table`
FROM `my_table`
WHERE
WHERE
UNIX_TIMESTAMP(STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S')) IS NULL;
STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S') IS NULL;


</pre>
</pre>


Alternative PHP solution: [https://www.php.net/manual/en/function.strtotime.php strtotime]
== PHP appracoh ==
Using PHP [https://www.php.net/manual/en/function.strtotime.php strtotime] function


== references ==
== references ==

Revision as of 17:30, 13 August 2020

Validate the value should be year-month-day format e.g. 2026-05-06

MySQL approach

SELECT `my_date_column`, STR_TO_DATE(`my_date_column`, '%Y-%m-%d')
FROM `my_table`
WHERE
STR_TO_DATE(`my_date_column`, '%Y-%m-%d') IS NULL;

Verify the value should be year/month/day format 

SELECT `my_date_column`, STR_TO_DATE(`my_date_column`, '%Y/%m/%d')
FROM `my_table`
WHERE
STR_TO_DATE(`my_date_column`, '%Y/%m/%d') IS NULL;

Verify the value should be hour:minute:second format e.g. 15:06:06 

SELECT `my_time_column`, STR_TO_DATE(`my_time_column`, '%H:%i:%S') 
FROM `my_table`
WHERE
STR_TO_DATE(`my_time_column`, '%H:%i:%S') IS NULL;

Verify the value should be year-month-day hour:minute:second format e.g. 2026-05-06 15:06:06 

SELECT `my_time_column`, STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S') 
FROM `my_table`
WHERE
STR_TO_DATE(`my_time_column`, '%Y-%m-%d %H:%i:%S') IS NULL;

PHP appracoh

Using PHP strtotime function

references

Retrieved from "https://wiki.planetoid.info/index.php?title=Validate_the_datetime_value&oldid=21956"