Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0  |  CUBRID 8.3.1  |  CUBRID 8.4.0  |  CUBRID 8.4.1  |  CUBRID 8.4.3  |  CUBRID 9.0.0 | 

DATE_FORMAT Function

Description

The DATE_FORMAT function converts the value of strings with DATE format ('YYYY-MM-DD' or 'MM/DD/YYYY') or that of date/time data type (DATE, TIMESTAMP, DATETIME) to specified date/time format and then return the value with the VARCHAR data type. For the format parameter to assign, refer to the Date/Time Format 2 table of the DATE_FORMAT Function. The Date/Time Format 2 table is used in DATE_FORMAT Function, TIME_FORMAT Function, and STR_TO_DATE Function.

When the format argument is assigned, the string is interpreted according to the specified language. At that time, the language specified to the intl_date_lang system parameter is applied. For example, when the language is "de_DE" and the format is "%d %M %Y", the string "3 Oktober 2009" is interpreted as the DATE type of "2009-10-03". When the intl_date_lang value is not set, the language applied to the CUBRID_LANG environment variable is applied. When the format argument specified is not corresponding to the given string, an error is returned.

Syntax

DATE_FORMAT(date, format)

  • date: A value of strings with the DATE format ('YYYY-MM-DD' or 'MM/DD/YYYY') or that of date/time data type (DATE, TIMESTAMP, DATETIME) can be specified .
  • format: Specifies the output format. The format specifier starting with ‘%’ is used.

In the following Date/Time Format 2, the month/day, date, and AM/PM in characters are different by language.

Date/Time Format 2

format Value

Meaning

%a

Weekday, English abbreviation (Sun, …, Sat)

%b

Month, English abbreviation (Jan, …, Dec)

%c

Month (1, …, 12)

%D

Day of the month, English ordinal number (1st, 2nd, 3rd, ...)

%d

Day of the month, two-digit number (01, …, 31)

%e

Day of the month (1, …, 31)

%f

Microseconds, three-digit number (000, …, 999)

%H

Hour, 24-hour based, number with at least two--digit (00, …, 23, …, 100, …)

%h

Hour, 12-hour based two-digit number (01, …, 12)

%I

Hour, 12-hour based two-digit number (01, …, 12)

%i

Minutes, two-digit number (00, …, 59)

%j

Day of year, three-digit number (001, …, 366)

%k

Hour, 24-hour based, number with at least one-digit (0, …, 23, …, 100, …)

%l

Hour, 12-hour based (1, …, 12)

%M

Month, English string (January, …, December)

%m

Month, two-digit number (01, …, 12)

%p

AM or PM

%r

Time, 12-hour based, hour:minute:second (hh:mm:ss AM or hh:mm:ss PM)

%S

Seconds, two-digit number (00, …, 59)

%s

Seconds, two-digit number (00, …, 59)

%T

Time, 24-hour based, hour:minute:second (hh:mm:ss)

%U

Week, two-digit number, week number of the year with Sunday being the first day Week (00, …, 53)

%u

Week, two-digit number, week number of the year with Monday being the first day (00, …, 53)

%V

Week, two-digit number, week number of the year with Sunday being the first day Week (00, …, 53)
(Available to use in combination with %X)

%v

Week, two-digit number, week number of the year with Monday being the first day (00, …, 53)
(Available to use in combination with %X)

%W

Weekday, English string (Sunday, …, Saturday)

%w

Day of the week, number index (0=Sunday, …, 6=Saturday)

%X

Year, four-digit number calculated as the week number with Sunday being the first day of the week (0000, …, 9999)
(Available to use in combination with %V)  

%x

Year, four-digit number calculated as the week number with Monday being the first day of the week (0000, …, 9999)
(Available to use in combination with %V)

%Y

Year, four-digit number (0001, …, 9999)

%y

Year, two-digit number (00, 01, …,  99)

%%

Output the special character "%" as a string

%x

Output an arbitrary character x as a string out of English letters that are not used as format specifiers.
Example

The following example shows the case when the system parameter intl_date_lang is "en_US".

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

 date_format('2009-10-04 22:23:00', '%W %M %Y')

======================

  'Sunday October 2009'

 

 

SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');

 date_format('2007-10-04 22:23:00', '%H:%i:%s')

======================

  '22:23:00'

 

SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');

 date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')

======================

  '4th 00 Thu 04 10 Oct 277'

 

 

SELECT DATE_FORMAT('1999-01-01', '%X %V');

 date_format('1999-01-01', '%X %V')

======================

  '1998 52'

The following example shows the case when the system parameter intl_date_lang is "de_DE".

csql> ;se intl_date_lang="de_DE"

 

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');

   date_format('2009-10-04 22:23:00', '%W %M %Y')

======================

  'Sonntag Oktober 2009'

 

SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s %p');

   date_format('2007-10-04 22:23:00', '%H:%i:%s %p')

======================

  '22:23:00 Nachm.'

 

 

SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');

   date_format('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')

======================

  '4 00 Do. 04 10 Okt 277'

Remark

When only the language is set to "en_US" (the initial value of CUBRID_LANG at installation of CUBRID) in the locale of the CUBRID_LANG environment variable and charset after "." is omitted, the charset is set to ISO-8859-1 (.iso88591).

When the charset is ISO-8859-1, the language that can be changed in TO_DATE function is "ko_KR" and "tr_TR" except "en_US". If the charset is UTF-8, it can be changed to any language supported by CUBRID. For details, see Remark in the TO_CHAR Function (date_time).