Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to use the Date function in MySql. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

NOW () returns the current date and time

NOW () returns the current date and time. Syntax NOW ()-- example-- the following is the SELECT statement: SELECT NOW (), CURDATE (), CURTIME () the result is as follows: NOW () CURDATE () CURTIME () 2018-06-27 14:25:34 2018-06-27 14 SELECT NOW 25 CURDATE 34-instance-- the following SQL creates a "Orders" table with a date-time column (OrderDate): CREATE TABLE Orders (OrderId int NOT NULL,ProductName varchar (50) NOT NULL,OrderDate datetime NOT NULL DEFAULT NOW () PRIMARY KEY (OrderId)-- Please note The OrderDate column specifies NOW () as the default value. As a result, the current date and time are automatically inserted into the column when you insert a row into the table. Now, we want to insert a record in the "Orders" table: INSERT INTO Orders (ProductName) VALUES ('Jarlsberg Cheese')-- the "Orders" table will look like this: OrderId ProductName OrderDate1 Jarlsberg Cheese 2018-06-11 14:36:55

CURDATE () returns the current date

-- CURDATE () returns the current date. -- example-- the following is the SELECT statement: SELECT NOW (), CURDATE (), CURTIME () the result is as follows: NOW () CURDATE () CURTIME () 2018-06-27 14:25:34 2018-06-27 1414 CURTIME-- instance-- the following SQL creates a "Orders" table with a date-time column (OrderDate): CREATE TABLE Orders (OrderId int NOT NULL,ProductName varchar (50) NOT NULL,OrderDate datetime NOT NULL DEFAULT CURDATE () PRIMARY KEY (OrderId)-- Please note The OrderDate column specifies CURDATE () as the default value. As a result, the current date and time are automatically inserted into the column when you insert a row into the table. Now, we want to insert a record in the "Orders" table: INSERT INTO Orders (ProductName) VALUES ('Jarlsberg Cheese')-- the "Orders" table will look like this: OrderId ProductName OrderDate1 Jarlsberg Cheese 2018-06-11 14:36:55

CURTIME () returns the current time

-- CURTIME () returns the current date. -- example-- here is the SELECT statement: SELECT NOW (), CURDATE (), CURTIME ()-- the result is as follows: NOW () CURDATE () CURTIME () 2018-06-27 14:25:34 2018-06-27 14:25:34

DATE () extracts the date portion of a date or date / time expression

The DATE () function extracts the date portion of a date or date / time expression. -- example-- Let's say we have the following "Orders" table: OrderId ProductName OrderDate1 Jarlsberg Cheese 2018-06-27 14 SELECT 53 SELECT statement: SELECT ProductName, DATE (OrderDate) AS OrderDateFROM OrdersWHERE OrderId=1-- results as follows: ProductName OrderDateJarlsberg Cheese 2018-06-27

EXTRACT () returns a separate part of the date / time

The EXTRACT () function returns a separate part of the date / time, such as year, month, day, hour, minute, and so on. -- syntax-- EXTRACT (unit FROM date)-- the date parameter is a legal date expression. The unit parameter can be the following value: Unit value MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH-- instance-- suppose we have the following "Orders" table: OrderId ProductName OrderDate1 Jarlsberg Cheese 2018-06-2715 OrderId ProductName OrderDate1 Jarlsberg Cheese 03mono 01muri-here is the SELECT statement: SELECT EXTRACT (YEAR FROM OrderDate) AS OrderYear,EXTRACT (MONTH FROM OrderDate) AS OrderMonth,EXTRACT (DAY FROM OrderDate) AS OrderDay The FROM OrdersWHERE OrderId=1-- results are as follows: OrderYear OrderMonth OrderDay2018 06 27

DATE_ADD () adds a specified time interval to the date

The DATE_ADD () function adds a specified time interval to the date. -- syntax-- DATE_ADD (date,INTERVAL expr type)-- the date parameter is a legal date expression. The expr parameter is the interval you want to add. -- the type parameter can be the following values: Type value MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH-- instance-- suppose we have the following "Orders" table: OrderId ProductName OrderDate1 Jarlsberg Cheese 2018-06-27 15 MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH-- 02RV 474-now we want to add 45 days to "OrderDate" so that we can find the payment date. We use the following SELECT statement: SELECT OrderId,DATE_ADD (OrderDate,INTERVAL 45 DAY) AS OrderPayDateFROM Orders-- result: OrderId OrderPayDate1 2018-08-11 15:02:47

DATE_SUB () subtracts the specified time interval from the date

The DATE_SUB () function subtracts the specified interval from the date. The syntax DATE_SUB (date,INTERVAL expr type) date parameter is a valid date expression. The expr parameter is the interval you want to add. The type parameter can be the following value: Type value MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH example suppose we have the following "Orders" table: OrderId ProductName OrderDate Jarlsberg Cheese 2018-06-27 15:02:47 now, we want to subtract five days from "OrderDate". We use the following SELECT statement: SELECT OrderId,DATE_SUB (OrderDate,INTERVAL 5 DAY) AS SubtractDateFROM Orders result: OrderId SubtractDate 2018-06-22 15:02:47

DATEDIFF () returns the number of days between two dates

DATEDUFF () returns the number of days between two dates. Instance: SELECT DATEDIFF ('2018-11-29) AS DiffDate result: DiffDate-1

DATE_FORMAT () displays the date / time in different formats

DATE_FORMAT () displays the date / time instance in a different format: SELECT DATE_FORMAT (NOW (),'% b% d% Y% HGV% I% p'); # DATE_FORMAT (NOW (),'% b% d% Y% HGV% I% p') 'Jun 27 2018 03:12 PM'SELECT DATE_FORMAT (NOW (),'% m% dmi% Y') # DATE_FORMAT (NOW (),'% mmurf% dmurt% Y')'06-27-2018'SELECT DATE_FORMAT (NOW (),'% d% b% y'); # DATE_FORMAT (NOW (),'% d% b% y')'27 Jun 18'SELECT DATE_FORMAT (NOW (),'% d% b% Y% TRV% f')) # DATE_FORMAT (NOW (),'% d% b% Y% TRV% f')'27 Jun 2018 15V 15V 15V 444V 000000' format description% an abbreviated week name% b abbreviated month name% c month, numeric% D prefixed day of month% d month, numeric value (00-31)% e month Value (0-31) f microsecond% H-hour (00-23)% h-hour (01-12)% I-hour (01-12)% I minute, value (00-59)% j-year day (001-366)% k-hour (0-23)% l-hour (1-12)% M month name% m-month, value (00-12)% p AM or PM%r time 12-hour (hh:mm:ss AM or PM)% S seconds (00-59)% s seconds (00-59)% T time, 24-hours (hh:mm:ss)% U week (00-53) Sunday is the first day of the week% u week (00-53) Monday is the first day of the week% V week (01-53) Sunday is the first day of the week With% X using% v week (01-53) Monday is the first day of the week, with% x using% W week name% w week day (0 = Sunday, 6 = Saturday)% X year, where Sunday is the first day of the week, 4 digits, and% V using% x year, where Monday is the first day of the week, 4 digits, and% v using% Y year, 4 digits, 2 digits

The following are the data types in which dates are stored in the database

DATE-format: YYYY-MM-DD

DATETIME-format: YYYY-MM-DD HH:MM:SS

TIMESTAMP-format: YYYY-MM-DD HH:MM:SS

YEAR-format: YYYY or YY

The above is the editor for you to share how to use the Date function in MySql, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.