Prepare for the second stage of testing

Typical problems

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.

SQL Server

In SQL Server, the CONVERT function is used for date formatting.

We'll use the date returned by the following query as an example:

Console

Execute

SELECT b_datetime FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

select b_datetime from utb where b_datetime='2002-06-01T01:13:39.000';

    Scheme

No scheme

E. g., to get the date in the "dd-mm-yyyy" format from this timestamp, we’ll just have to write

Console

Execute

SELECT CONVERT(varchar, b_datetime, 105) 
FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

select convert(varchar, b_datetime, 105) from utb where b_datetime='2002-06-01T01:13:39.000';

    Scheme

No scheme

For the "mm-dd-yyyy" format, the style argument of the CONVERT function can be changed to 110:

Console

Execute

SELECT CONVERT(varchar,b_datetime,110) 
FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

select convert(varchar,b_datetime,110) from utb where b_datetime='2002-06-01T01:13:39.000';

    Scheme

No scheme

To display just the time part without milliseconds, style 108 is used:

Console

Execute

SELECT CONVERT(varchar, b_datetime, 108) 
FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

select convert(varchar, b_datetime, 108) from utb where b_datetime='2002-06-01T01:13:39.000';

    Scheme

No scheme

MySQL

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:

SELECT date_format(b_datetime,'%d-%m-%Y') 
FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

SELECT date_format(b_datetime,'%m-%d-%Y') 
FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

SELECT date_format(b_datetime,'%H:%i:%s') 
FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

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:

SELECT to_char(b_datetime,'dd-mm-yyyy') 
FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

SELECT to_char(b_datetime,'mm-dd-yyyy') 
FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

–12 hour format
SELECT to_char(b_datetime,'hh12:mi:ss') 
FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';
–24 hour format
SELECT to_char(b_datetime,'hh24:mi:ss') 
FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

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:

SELECT to_char(b_datetime,'hh24:mi:ss') 
FROM utb WHERE b_datetime=timestamp'2002-06-01 01:13:39.000';