Skip to content

Instantly share code, notes, and snippets.

@mjc

mjc/iso8601_to_datetime.sql

Created November 28, 2012 23:17
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mjc/4165466 to your computer and use it in GitHub Desktop.
Save mjc/4165466 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL function to convert from ISO8601 to DATETIME
Raw
iso8601_to_datetime.sql
delimiter $$
CREATE FUNCTION ISO8601_DATETIME(iso varchar(26))
RETURNS DATETIME
DETERMINISTIC
BEGIN
RETURN CONVERT_TZ(
STR_TO_DATE(
CONCAT(SUBSTRING(iso, 1, 10),
' ',
SUBSTRING(iso, 12, 8)),
'%Y-%m-%d %H:%i:%s'),
SUBSTRING(iso, 20, 6),
'+00:00');
END$$
delimiter ;
@aand18
Copy link

aand18 commented Mar 15, 2018

Hi!
It doesn't work with 2018-03-14T15:14:08.956+01:00. Bye

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment