loading..
Русский    English
17:02

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.

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
  1. SELECT b_datetime FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

b_datetime
2002-06-01 01:13:39.000

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

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

01-06-2002

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

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

06-01-2002

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

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

01:13:39

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:

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

01-06-2002

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

06-01-2002

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

01:13:39

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:

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

01-06-2002

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

06-01-2002

  1. 12 hour format
  2. SELECT to_char(b_datetime,'hh12:mi:ss')
  3. FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';
  4. 24 hour format
  5. SELECT to_char(b_datetime,'hh24:mi:ss')
  6. FROM utb WHERE b_datetime='2002-06-01T01:13:39.000';

01:13:39

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:

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


Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100