Conversion of the date to a string
Let's talk about date formatting. It's most often used for representation of date and time values in printed reports. Usually this formatting is done by means of reporting tools. However, the DBMS level provides similar functionality as well. We won't discuss the question here which way is better. Let's just note that a number of sql-ex.ru exercises require the representation of the query result using a particular format.
In SQL Server, the CONVERT function is used for date formatting.
We'll use the date returned by the following query as an example:
E. g., to get the date in the "dd-mm-yyyy" format from this timestamp, we’ll just have to write
For the "mm-dd-yyyy" format, the style argument of the CONVERT function can be changed to 110:
To display just the time part without milliseconds, style 108 is used:
In MySQL, the DATE_FORMAT function is used for date formatting, whose second argument represents an output mask that specifies how the first argument, a date/time value, has to be formatted. The examples above can be rewritten for MySQL as follows:
Let’s note that "H" is used for representation of time in 24 hour format, and "h" – in 12 hour format.
PostgreSQL & Oracle
These database management systems both use the TO_CHAR function with an intuitive output mask for date formatting. For PostgreSQL, our examples above can be rewritten as follows:
There are no fundamental differences for Oracle. To make the PostgreSQL queries listed above work in Oracle, we need to know that the string representation of a date has to be converted to a corresponding temporal data type when compared to a date value. Besides, the standard representation of a timestamp with the letter T separating the date and time parts is not supported. E. g., our last example will look as follows in Oracle: