MariaDB Date + Examples

In this MariaDB tutorial, we will learn about the “MariaDB date” where we will know how the date is inserted into a table in a different format. Also, we will learn about the different predefined functions related to date. The following topics will be covered in this tutorial:

• MariaDB date format
• MariaDB date functions
• MariaDB date comparison
• MariaDB date type
• MariaDB date to string
• MariaDB date between
• MariaDB date add
• MariaDB date now
• MariaDB date greater than

MariaDB date format

In MariaDB, the date is displayed in YYYY-MM-DD format, MariaDB supports a range of dates from ‘1000-01-01′ to ‘9999-12-31’. The date can be input in a different format like YYYY/MM/DD, etc.

• Each part of the date is separated using the delimiters, these delimiters can be any symbol like @,#,$,%,& and *, etc. This can be any acceptable delimiter.
• Note: Date can be created without delimiters.
• MariaDB always stores the data in an internal format which is YYYY-MM-DD, irrespective of the date given in a different format.
• To format the date, there is function in MariaDB that is the DATE_FORMAT().

The syntax for DATE_FORMAT is given below.

DATE_FORMAT(date,format);

• DATE_FORMAT: It is the function that takes the date and specific format of the date to format the date.
• date: It is the date value that we want to format in a specific way.
• format: It is the string as a format specifier.

These format specifiers can be any of the followings:

Format

‘%Y-%m-%d %T’

‘%Y-%m-%d %T.%f’

‘%d/%m/%Y’

‘%Y%m%d’

‘%T’

‘%m/%d/%Y’

Let’s use one of the format specifiers to format the date.

SELECT DATE_FORMAT('2022-01-01', '%m/%d/%Y');

From the output, we can see that the output is like the format that is specified in the code.

Now, take one more example with the table without the DATE_FORMAT function.

The syntax to create the date in MariaDB

'YYYY-MM-DD'

Where,

• YYYY: It is the year part of the date.
• MM: It is the month part of the date.
• DD: It is the day part of the date.

Let’s create a table as Person_Birth that will contain the birth date of the person.

CREATE TABLE Person_Birth(person_name VARCHAR(40),birth_date DATE);

Here in the above code:

• CREATE TABLE: It is a statement to create a new table, here we are creating the new table as Person_Birth.
• person_name VARCHAR(40): It is the column of type characters that is going to store the name of the person who born in United States. It can store the name to 40 characters.
• birth_date: It is the column of type date that will contain the birth date of the person.

After creating the above table, insert the following record with the person’s name with date in a different format.

INSERT INTO Person_Birth VALUES ('Trevor Roth',"2010-01-12"), ('Dominick Ryan',"2011-2-28"), 
('Logan Hawkins','120314'),('Kirk McIntyre','13*04*21');

View the table by typing the below code.

SELECT * FROM Person_Birth;

As we can see, we have inserted the dates in a different format but MariaDB stores in an internal format as we talked about before in the above line.

Also, check: MariaDB Cast with Examples

MariaDB date functions

The DATE function in MariaDB extracts the date part from the DateTime and Date values.

The syntax of the DATE function is given below.

DATE(expression);

Where,

• DATE(): It is a function that accepts the expression and extracts the date part from the expression.
• expression: It can be any expression related to dates like date or date-time.

Let’s extract the date part from a date and date-time using the below code.

SELECT DATE('2016-05-18 12:21:32');

Here in the code, we have provided the date-time (‘2016-05-18 12:21:32’) to the DATE function.

The output shows the date part only from the date-time expression which is 2016-05-18.

Read: MariaDB Substring [11 Examples]

MariaDB date comparison

In MariaDB, the dates can be compared with each other using the comparison operator like Less Than (<), Greater Than(>) and Equal (=). This comparison is done in the WHERE clause of the MariaDB queries.

• Note: Date can be compared using the SELECT statement like we can compare the two dates using the equal oprator (=). If two dates are same or equal then MariaDB outuput the result as 1 which indication of TRUE otherwise it returns 0 which means FALSE.

The syntax to compare dates is given below.

Date_Expression_1 comparison_operator Date_Expresson_2;

Where,

• Date_Expression_1: It is the first date expression that is compared to other dates.
• comparison_operator: These are operators like Less Than (<), Greater Than(>) and Equal (=).
• Date_Expression_2: To which Date_Expression_1 is compared.

The table we will use here is the Employee table that contains information about the employees in the United States like employee name, salary, city, description start date, and end date of the job.

The description of the table is given below.

Now run the below code to find the name of employees whose start date is after the date ‘1978-01-01’ and city is New York.

SELECT first_name, city, start_date FROM employee
WHERE start_date > '1978-01-1' AND city = 'New York';

In the about output,

• In line number 1, we have used the SELECT statement to get the columns first_name, city and start_date from a table employee.
• In line number 2, using the WHERE clause we have compared the start_date column to the date ‘1978-01-1′. Then we have used the AND operator to separate the next condition that is city = ‘New York’.

The output shows that the two employees Linda and David from New York have a start date after ‘1978-01-1′.

Lets’ compare the date in the SELECT statement.

SELECT '1920-01-1' = '1920-01-1';

As from the above output, we have compared the dates in the SELECT statement and the result is 1 which means both the date is equal.

Read: MariaDB LIMIT + Examples

MariaDB date type

In MariaDB, the DATE type is a kind of data type that is used to define the dates in a database. The keyword DATE is specified to make a column as a date type column that will contain the date value.

The syntax to define a column as date type is given below.

column_name DATE;

Let’s create a table as a Person, this table will contain the person’s name, birth date, and birthplace.

CREATE TABLE Person(name VARCHAR(30),birth_date DATE, birth_place VARCHAR(30));

Where,

• CREATE TABLE: This command is used here to create a new table as a Person.
• name: It is the column of type character of the table Person for storing the name of a person.
• birth_date: It is the column of type date to store the birth date of the person.
• birth_place: It is the column of type character to store the birthplace of the person.

Insert the following records.

INSERT INTO Person(person_name,birth_date,birth_place)VALUES('Kenneth S. Covington','1970-08-20','United State'),
('Brian S. Jensen','1967-01-14','New York'),('Neil F. Tatum','1988-04-28','United Kindom'),
('Debbie K. Kreider','1974-08-15','Canada'),('Charles P. Henderson','1936-03-15','United Kindom');

Now view the table with the inserted records using the below code.

SELECT * FROM Person;

From the output, we can see that the column birth_date contains the value of date type.

Read: MariaDB index with Examples

MariaDB date to string

In MariaDB, we can convert the date to a string using the function DATE_FORMAT function that we have already discussed in the above sub-section ‘MariaDB date format’. Please refer to that section.

For the sake of example, we will do here to know how the date is converted to a string.

SELECT DATE_FORMAT(NOW(),'%d/%m/%Y');

Here in the code, we have provided the two values as NOW() to get the current date-time and format specifier (‘%d/%m/%Y’) to format the date.

From the output, we can see that the output is like the format that is specified in the code.

Read: MariaDB LIKE Operator [7 Examples]

MariaDB date between

The DATEDIFF function returns the days between two dates in MariaDB. This function calculates the days between two different dates.

• We can filter the dates within range using the BETWEEN clause in the WHERE clause.

The syntax for DATEDIFF is given below.

DATEDIFF(expression1,expression2)

Where,

• DATEDIFF: It is the function that calculates the days between expression1 and expression2.
• expression: These expressions are the date and date-time values that are provided to the DATEDIFF.

Let’s check with an example to know the days between the dates.

SELECT DATEDIFF('2008-11-15','2007-12-30');

Here in the code, we have provided the two dates ‘2008-11-15′, ‘2007-12-30’ to the function DATEDIFF to calculate the days between two dates.

The number of days between the dates is 321 from the output we can see.

As we talked about at the starting of this section we can use the dates with the BETWEEN operator in the WHERE clause.

The syntax is given below.

SELECT column_name
FROM table_name
WHERE column_name BETWEEN date1 AND date2;

Where,

• column_name: It is the column that we want to view, we can mention more than one column with a SELECT statement.
• table_name: It is the name of the table on which run the query to get the desired information.
• BETWEEN date1 AND date2: BETWEEN is used to select the values within range of date1 and date2.

For the example, we will use the same table employee that we have used in the above sub-section MariaDB date comparison.

SELECT first_name,last_name,end_date FROM employee
WHERE end_date BETWEEN '1986-01-01' AND '1998-01-01';

The output shows that there are three persons Alison, James, and Linda whose end_date between ‘1986-01-01‘ and ‘1998-01-01‘.

Read: MariaDB Insert Into + Examples

MariaDB date add

In MariaDB, there is a function named DATE_ADD that performs the arithmetic operation on the date.

The syntax is given below.

DATE_ADD(date_value,INTERVAL expression unit)

Where,

• DATE_ADD: It is the function to add the date.
• date_value: It is the Date value or DateTime value to which the date will be added.
• INTERVAL: It is the keyword.
• expression: The expression is a string.
• unit: It is a unit of the date that can be second, minute and day, etc.

Let’s take an example.

SELECT '2010-12-31' + INTERVAL 1 DAY;

In the code, we are adding the one-day interval to date ‘2010-12-31’.

After adding the one-day interval to date ‘2010-12-31’, it becomes ‘2011-01-01’.

We can also subtract the one-day interval using the below code.

SELECT '2010-12-31' - INTERVAL 1 DAY;

Read: MariaDB Update Statement

MariaDB date now

The MariaDB has a NOW function that displays the current date and time values in the specific format as 'YYYY-MM-DD HH:MM:SS'.

• This NOW function is equivalent to other functions in MariaDB like CURRENT_TIMESTAMP, LOCALTIMESTAMP that displays the current date and time values.

The syntax of the NOW is given below.

NOW(precision)

Where, NOW is the function that gives us the current date and time, precision within the NOW function represents the microsecond.

Let’s consider an example of how this function works.

SELECT NOW();

From the output, we can see that the NOW function shows both the current date and time.

But what we want is only the date part, for that we will warp the NOW function with the DATE function that we learned above.

SELECT DATE(NOW());

Now we have successfully, found the current date as 2022-01-20.

Read: Replace Function in MariaDB

MariaDB date greater than

In MariaDB, we can compare the dates using the different comparison operators that we have already learned the above sub-section of this tutorial that is the ‘MariaDB date comparison’.

Please refer to that section where we have compared the date using the operator Greater Than (>).

You may also like to read the following MariaDB tutorials.

• How to Create View in MariaDB
• MariaDB Check Constraint + Examples
• MariaDB Truncate Table + Examples
• MariaDB Timestamp + Examples
• How to Create Function in MariaDB
• MariaDB Vs SQLite – Key Differences

So, in this tutorial, we have learned about the “MariaDB date” and covered the following topics.

• MariaDB date format
• MariaDB date functions
• MariaDB date comparison
• MariaDB date type
• MariaDB date to string
• MariaDB date between
• MariaDB date add
• MariaDB date now
• MariaDB date greater than