Differences

This shows you the differences between two versions of the page.

--- v5:reference:connection:sqldate [2016/01/17 01:22] – ↷ Page moved from v5:reference:sqldate to v5:reference:connection:sqldate mnewnham
+++ v5:reference:connection:sqldate [2016/04/22 03:08] (current) – [Other Periods] mnewnham
@@ Line 9: / Line 9: @@
 </WRAP>
 ===== Description =====
-When the  function ''sqlDate()'' is passed a string formatted using [[http://php.net/manual/en/function.date.php|PHP date formatting]] it returns a database-independent date string, either using the system date, or if passed a string containing a datetime column in a table, a string derived from that column.
+When the  function ''sqlDate()'' is passed a string based on SQL date/time formats,  it returns a database-independent date string, either using the system date, or if passed a string containing a datetime column in a table, a string derived from that column.
+===== Supported Formats =====
+The following column formats are supported across all databases. Other formats may be supported by certain databases but you should check them for portability. The codes used are based loosely on date formatting for Oracle databases.
+==== Dates =====
+^Code^Description|
+|Y,y|4 Digit Year|
+|M|Month of year as a 3 character abbreviation (Jan,Feb etc...)|
+|m|Month of year as a 2 digit number 01-12|
+|D,d|Day of month as a 2 digit number 01-31|
+==== Times ====
+^Code^Description|
+|H|Hour of day in 24 Hour clock format 00-24|
+|h|Hour of day in 12 Hour clock format 00-12|
+|i|Minute of hour 00-59|
+|A,a|AM/PM , use with **h** hour format|
+==== Other Periods====
+^Code^Description|
+|Q,q|Yearly Quarter|
+|w|Day of week as 1 digit number|
+|W|Week of the year as a number (1-52)|
+|l|Day of week as full word (Monday, Tuesday, etc)|
+<WRAP important 100%>
+Day of week code (''w'') is not consistently implemented across DB drivers, as the library relies on database-specific functions, e.g.
+  * [[http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format|MySQL]]: 0 (Sunday) to 6 (Saturday)
+  * MSSQL: not implemented
+  * [[http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924|Oracle]]: 1 to 7 (which day is 1 depends on [[https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams131.htm#REFRN10128|NLS_TERRITORY]] setting)
+  * [[http://www.postgresql.org/docs/9.4/static/functions-formatting.html#FUNCTIONS-FORMATTING-DATETIME-TABLE|PostgreSQL]]: 1 (Sunday) to 7 (Saturday)
+For a portable result, see the [[v5:reference:connection:dow|dow()]] method.
+</WRAP>
 ===== Usage =====
 <code php>