{"id":656,"date":"2012-12-13T18:27:49","date_gmt":"2012-12-13T18:27:49","guid":{"rendered":"https:\/\/ibex.tech\/cloud\/?p=656"},"modified":"2022-06-23T11:23:21","modified_gmt":"2022-06-23T10:23:21","slug":"datetime-2","status":"publish","type":"post","link":"https:\/\/ibex.tech\/cloud\/mysql\/queries\/select\/datetime-2","title":{"rendered":"DateTime general"},"content":{"rendered":"\n<h4 class=\"wp-block-heading\">ORDER BY Date Order<\/h4>\n\n\n\n<p>ASC = oldest first<\/p>\n\n\n\n<p>DESC = most recent first<\/p>\n\n\n\n<p>Note it&#8217;s &#8220;MINUTE&#8221;, &#8220;HOUR&#8221;, etc not &#8220;MINUTES&#8221; or &#8220;HOURS&#8221; etc <\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Convert DateTime to Date<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>CAST('$EventDateTime'AS DATE)<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Specifying Date and DateTime from a string<\/h4>\n\n\n\n<p>It is recommended to use CAST()<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CAST('$Year-$Month-01' AS DATE)\nCAST('$Year-$Month-01T00:00:00' AS DATE)\nCAST('$Year-$Month-01T00:00:00' AS DATETIME)<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Last Day Of Month<\/h4>\n\n\n\n<p>(This produces a DATE result, not DATETIME)<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>LAST_DAY('2019-01-01T00:00:00')\n\/\/Will produce '2019-01-31'<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Get SQL Server&#8217;s Current DateTime<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>  \/\/Get the sqlserver current datetime\n  $query = mysqli_query($dblink, \"SELECT *, NOW() as datetimenow FROM tblMyTable WHERE Id = 1\");\n  $Result = mysqli_fetch_array($query, MYSQLI_ASSOC);\n  $DateTimeNow = $Result&#91;'datetimenow'];<\/code><\/pre>\n\n\n\n<p>Same as Now() but giving just the date:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CURDATE()<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">How is DateTime stored by MySQL?<\/h4>\n\n\n\n<p>From the 10.9 Date and Time Data Type Representation:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>     1 bit  sign           (1= non-negative, 0= negative)\n    17 bits year*13+month  (year 0-9999, month 0-12)\n     5 bits day            (0-31)\n     5 bits hour           (0-23)\n     6 bits minute         (0-59)\n     6 bits second         (0-59)\n    ---------------------------\n    40 bits = 5 bytes<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>ORDER BY Date Order ASC = oldest first DESC = most recent first Note it&#8217;s &#8220;MINUTE&#8221;, &#8220;HOUR&#8221;, etc not &#8220;MINUTES&#8221; or &#8220;HOURS&#8221; etc Convert DateTime to Date Specifying Date and DateTime from a string It is recommended to use CAST() Last Day Of Month (This produces a DATE result, not DATETIME) Get SQL Server&#8217;s Current [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[232,257,64],"tags":[],"class_list":["post-656","post","type-post","status-publish","format-standard","hentry","category-columns-mysql","category-datetime","category-select"],"_links":{"self":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/656","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/comments?post=656"}],"version-history":[{"count":25,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/656\/revisions"}],"predecessor-version":[{"id":4308,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/posts\/656\/revisions\/4308"}],"wp:attachment":[{"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/media?parent=656"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/categories?post=656"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ibex.tech\/cloud\/wp-json\/wp\/v2\/tags?post=656"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}