■ Database ■/MySQL2018.11.13 14:45

   date, date_format 을 사용한 날짜 비교


date, date_format 을 사용하여 날짜 계산, 날짜형변환 메서드를 알아보자


   DATE (expr)


먼저 DATE 함수는 표현식, 혹은 datetime 부분의 날짜부분을 추출한다.


Extracts the date part of the date or datetime expression expr.


mysql> SELECT DATE('2003-12-31 01:02:03'); -> '2003-12-31'


https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date




   DATE_FORMAT( date, format )


지정된 문자열에 따라서 날짜값의 형식을 변환한다. 형식지정자 에서는 '%' 를 사용하는데 '%'는 형식지정자 문자 앞에 사용한다.


Formats the date value according to the format string.

The following specifiers may be used in the format string. The % character is required before format specifier characters.

SpecifierDescription
%aAbbreviated weekday name (Sun..Sat)
%bAbbreviated month name (Jan..Dec)
%cMonth, numeric (0..12)
%DDay of the month with English suffix (0th1st2nd3rd, …)
%dDay of the month, numeric (00..31)
%eDay of the month, numeric (0..31)
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%jDay of year (001..366)
%kHour (0..23)
%lHour (1..12)
%MMonth name (January..December)
%mMonth, numeric (00..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%UWeek (00..53), where Sunday is the first day of the week; WEEK() mode 0
%uWeek (00..53), where Monday is the first day of the week; WEEK() mode 1
%VWeek (01..53), where Sunday is the first day of the week; WEEK() mode 2; used with %X
%vWeek (01..53), where Monday is the first day of the week; WEEK() mode 3; used with %x
%WWeekday name (Sunday..Saturday)
%wDay of the week (0=Sunday..6=Saturday)
%XYear for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%xYear for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%YYear, numeric, four digits
%yYear, numeric (two digits)
%%A literal % character
%xx, for any x not listed above
SpecifierDescription

Ranges for the month and day specifiers begin with zero due to the fact that MySQL permits the storing of incomplete dates such as '2014-00-00'.

The language used for day and month names and abbreviations is controlled by the value of the lc_time_names system variable (Section 10.15, “MySQL Server Locale Support”).

For the %U%u%V, and %v specifiers, see the description of the WEEK() function for information about the mode values. The mode affects how week numbering occurs.

DATE_FORMAT() returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters.

mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
        -> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
        -> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
    ->                 '%D %y %a %d %m %b %j');
        -> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
    ->                 '%H %k %I %r %T %S %w');
        -> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
        -> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
        -> '00'


https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format



   날짜범위 계산 SQL Query


1
WHERE ( DATE(A.date) >= DATE_FORMAT( '2018-08-01''%Y-%m-01') AND DATE(A.date) <= LAST_DAY('2018-08-01') )
cs













Posted by SAP (Study And Programming) by serpiko

댓글을 달아 주세요