MariaDB Date Allow Null [17 Useful Examples]

In this MariaDB tutorial, we will discuss the MariaDB Date Allow Null statement and look at several examples. There are lists of the topic that comes under discussion:

  • MariaDB Date Allow Null
  • MariaDB Date Allow Null Alter Column
  • MariaDB Date Allow Null By DateTime
  • MariaDB Date Allow Null Error
  • MariaDB Date Allow Null Group By
  • MariaDB Date Allow Null Greater Than
  • MariaDB Date Allow Null Greater Than Today
  • MariaDB Date Allow Null Hour
  • MariaDB Date Allow Null Join
  • MariaDB Date Allow Null Like
  • MariaDB Date Allow Null Month
  • MariaDB Date Allow Null Now
  • MariaDB Date Allow Null Or Empty
  • MariaDB Date Allow Null Str_to_Date
  • MariaDB Date Allow Null Update
  • MariaDB Date Allow Null Week
  • MariaDB Date Allow Null Year

MariaDB Date Allow Null

In this section, we will understand and learn how to use the MariaDB IS NULL condition to find NULL values in the column in the table. And we will explain it with the help of an illustrated example.

In MariaDB, the IS NULL condition is used to find NULL values using the SELECT statement. It is also used with the DELETE, INSERT and UPDATE statements. Here is an illustrated example of the MariaDB IS NULL condition to find the NULL value of the DATE column in the table by the following query:

EXAMPLE:

SELECT FIRST_NAME,LAST_NAME, PURCHASE_DATE FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE IS NULL;

As we see in the above query, we have used the MariaDB SELECT statement to retrieve all records of the FIRST_NAME, LAST_NAME, and PURCHASE_DATE columns of the USA_ABYSS_COMPANY table.

In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value in the USA_ABYSS_COMPANY table.

The SELECT statement will retrieve all records from the USA_ABYSS_COMPANY table if the WHERE condition gets TRUE.

MariaDB date allow null example
Example of MariaDB IS NULL condition to find NULL value in a DATE column

In this section, we have understood how to use the IS NULL condition on the PURCHASE_DATE column to find the NULL date value in the USA_ABYSS_COMPANY table. We have used an example and explained it in detail for better understanding.

Read: MariaDB Select Where Not Empty

MariaDB Date Allow Null Or Empty

Here we will understand and learn how to use the MariaDB IS NULL condition to find the DATE column which is NULL and empty in the table. It is explained with the help of an illustrated example.

EXAMPLE:

SELECT FIRST_NAME,LAST_NAME,PURCHASE_DATE FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE IS NULL
ORPURCHASE_DATE= ' ';
  • In this query, we have selected the records of the FIRST_NAME, LAST_NAME, and PURCHASE_DATE columns from the USA_ABYSS_COMPANY table by using the SELECT statement.
  • In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value in the table.
  • And we again also used the PURCHASE_DATE column with the EQUAL operator to find empty the DATE values in the USA_ABYSS_COMPANY table.
  • The SELECT statement will retrieve all records from the USA_ABYSS_COMPANY table if the WHERE condition is TRUE.
MariaDB date allow null or empty example
Example of MariaDB IS NULL condition and EQUAL operator used to find NULL value or empty value in the DATE column

In this section, we have understood how to use the IS NULL condition to find the null value in the DATE column. And for better understanding, we have used an example to explain it better.

Read: MariaDB Check String Length

MariaDB Date Allow Null Alter Column

In this section, we will learn and understand how to use the MariaDB ALTER TABLE statement to add NULL value in the DATE column and which is explained with the help of an illustrated example.

In MariaDB, the ALTER TABLE statement is used to add, modify, delete/drop the column of the table. It is also used to rename the column of the table.

Here is an example of the MariaDB ALTER TABLE statement for adding the NULL value in the DATE column in the table by the following query:

EXAMPLE:

ALTER TABLE USA_ABYSS_COMPANY MODIFY PURCHASE_DATE DATE NULL;

DESC USA_ABYSS_COMPANY;

In this preceding query, we have modified the PURCHASE_DATE column with the DATE datatype and added the NULL value in the USA_ABYSS_COMPANY table by using the ALTER TABLE statement.

If we want to check the new modification done in the PURCHASE_DATE column then we will use the DESC statement for the description of the USA_ABYSS_COMPANY table.

In this MariaDB tutorial, we have understood how to use the MariaDB ALTER TABLE statement to modify and add NULL value in the DATE column of the USA_ABYSS_COMPANY table. We have used a query example and explained it in detail for better understanding.

Read: MariaDB Check If Rows Exists

MariaDB Date Allow Null By DateTime

Here we will understand and learn how to use the MariaDB IS NULL condition on the DateTime data type to find the NULL value in the DATE column of the table. And which we will be explained with the help of an illustrated example.

EXAMPLE:

SELECT FULL_NAME,JOINING_DATE, EXPIRING_DATE FROM USA_FAZZRO_COMPANY
WHERE JOINING_DATE IS NULL;
  • As we see in the above query, the MariaDB SELECT statement retrieves all records of the FULL_NAME, JOINING_DATE, and EXPIRING_DATE columns from the USA_FAZZRO_COMPANY table.
  • In the WHERE condition, the JOINING_DATE column is used with the IS NULL condition to find the NULL value in the USA_FAZZRO_COMPANY table.
  • The SELECT statement will retrieve all records from the USA_FAZZRO_COMPANY table if the WHERE condition gets TRUE.
MariaDB date allow null by datetime example
Example of MariaDB IS NULL condition used on the DATETIME data type to find NULL value in the DATE column

In this MariaDB tutorial, we have understood how to use the IS NULL condition on the DATETIME data type of the USA_FAZZRO_COMPANY table. We have used an example query above and explained it in detail for better understanding.

Read: MariaDB Insert Into Select

MariaDB Date Allow Null Error

In this section, we will know the type of error that arises for the NULL value in the MariaDB IS NULL condition of the table. And which will be explained with the help of an illustrated example.

ERROR EXAMPLE:

CREATE TABLE USA_MCKESSON_COMPANY (
	PATIENT_ID INT,
	PATIENT_FIRSTNAME VARCHAR(50),
	PATIENT_LASTNAME VARCHAR(50),
	PATIENT_EMAIL VARCHAR(50),
	PATIENT_ARRIVALTIME DATE NOT NULL
);

INSERT INTO USA_MCKESSON_COMPANY (PATIENT_ID, PATIENT_FIRSTNAME, PATIENT_LASTNAME, PATIENT_EMAIL, PATIENT_ARRIVALTIME) 
values (1, 'Tarrance', 'Roglieri', '[email protected]', NULL);

In the first query, we created a table called USA_MCKESSON_COMPANY by using the CREATE TABLE statement. In the CREATE TABLE statement, we have used the NOT NULL constraint on the PATIENT_ARRIVALTIME column of the USA_MCKESSON_COMPANY table.

In the second query, during the insertion time of new records in the USA_MCKESSON_COMPANY table, we added the NULL value for the PATIENT_ARRIVALTIME column.

MariaDB date allow null error
Example of Error raised in the NOT NULL constraint

ERROR REMOVED EXAMPLE:

ALTER TABLE USA_MCKESSON_COMPANY MODIFY PATIENT_ARRIVALTIME DATE NULL;

insert into USA_MCKESSON_COMPANY (PATIENT_ID, PATIENT_FIRSTNAME, PATIENT_LASTNAME, PATIENT_EMAIL, PATIENT_ARRIVALTIME) 
values (1, 'Tarrance', 'Roglieri', '[email protected]', NULL);

SELECT * FROM USA_MCKESSON_COMPANY;

In the first query, we used the ALTER TABLE statement on the USA_MCKESSON_COMPANY table to modify the PATIENT_ARRIVALTIME column with the NULL constraint.

Then we inserted one record into the USA_MCKESSON_COMPANY table by using the INSERT INTO statement. If we want to retrieve all records from the USA_MCKESSON_COMPANY table then we will use the SELECT statement.

Hence, the error will be removed by using the ALTER TABLE statement and the constraint will be changed from the NOT NULL to NULL constraint in the USA_MCKESSON_COMPANY table.

MariaDB date allow null error example
Example of Error Removed from the DATE column of NULL constraint

in this section, we have understood how to check and remove the error of NULL constraint in the DATE column of the USA_MCKESSON_COMPANY table. For better understanding, we have used the two examples above and explained them in detail.

Read: MariaDB Greatest Function

MariaDB Date Allow Null Group By

In this MariaDB section, we will learn and understand how to use the MariaDB IS NULL condition with the GROUP BY clause to find the NULL value in the DATE column of the table. And which is explained with the help of an illustrated example.

The group by clause in MariaDB divides a result’s rows into categories. The aggregate functions count()min(), max(), sum(), and avg() are frequently used with the group by function to obtain attributes of groups.

For example, the number of elements (count), the total of values (sum), the maximum element (max), the minimum element (min), and the average of elements (avg).

Here is an example of how to use the MariaDB IS NULL condition with the GROUP BY clause in the table by the following query:

EXAMPLE:

SELECT FIRST_NAME,LAST_NAME,PURCHASE_DATE FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE IS NULL
GROUP BY FIRST_NAME;

In this query, we have selected the records of the FIRST_NAME, LAST_NAME, and PURCHASE_DATE columns from the USA_ABYSS_COMPNAY table by using the SELECT statement.

In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value in the USA_ABYSS_COMPANY table. And we have grouped them by the FIRST_NAME using the GROUP BY clause.

The SELECT statement will retrieve all records from the USA_ABYSS_COMPANY table if the WHERE condition gets TRUE.

MariaDB date allow null group by example
Example of MariaDB IS NULL condition used with the GROUP BY clause to find NULL value in a DATE column

In this section, we have understood how to use the MariaDB IS NULL condition with the GROUP BY clause to find the NULL value in the PURCHASE_DATE column. For a better explanation, we have used an example and explained it in detail.

Read: MariaDB Between + Examples

MariaDB Date Allow Null Greater Than

We will learn and understand how to use the MariaDB IS NULL condition with the GREATER THAN operator to find the NULL value in the DATE column of the table. And which will be explained with the help of an illustrated example.

In MariaDB, the GREATER THAN operator is used to find a greater value in the expression or column_name of the table. To express it, we use the > keyword in the query for the operations.

Here is a sample example of the MariaDB IS NULL condition with the GREATER THAN operator to find the NULL value in the DATE column by the following query:

EXAMPLE:

SELECT FIRST_NAME,LAST_NAME,PURCHASE_DATE FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE IS NULL
AND EMP_ID >15;

As we see in the above query, we have selected all records of the FIRST_NAME, LAST_NAME, and PURCHASE_DATE columns of the USA_ABYSS_COMPANY table by using the SELECT statement.

In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value of the USA_ABYSS_COMPANY table. And the EMP_ID column is used with the GREATER THAN operator to find values greater than 15 in the table.

The SELECT statement will retrieve all records from the USA_ABYSS_COMPANY table if the WHERE condition gets TRUE.

MariaDB date allow null greater than example
Example of the MariaDB IS NULL condition with the GREATER THAN operator to find NULL value in the PURCHASE_DATE column

In this section, we will learn how to use the IS NULL condition with the GREATER THAN operator to find the NULL value in the PURCHASE_DATE column. For a better explanation, we have used an example and explained it in detail.

Read: MariaDB Drop Index

MariaDB Date Allow Null Greater Than Today

In this section, we will learn and understand how to use the MariaDB CURRENT_DATE function to find the NULL value in the DATE column. And which is explained with the help of an illustrated example.

In MariaDB, the CURRENT_DATE column is used to return the current date from the expression or column_name of the query. Here is an illustrated example of the MariaDB CURRENT_DATE column to find the NULL value in the DATE column of the table by the following query:

EXAMPLE:

SELECT FIRST_NAME, LAST_NAME,PURCHASE_DATE FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE > CURRENT_DATE;
  • As we see in the above query, the MariaDB SELECT statement retrieves all records of the FIRST_NAME, LAST_NAME, and PURCHASE_DATE columns from the USA_ABYSS_COMPANY table.
  • In the WHERE condition, the GREATER THAN operator is used between the PURCHASE_DATE and CURRENT_DATE columns to find the value of today’s date greater than the function.
  • The MariaDB SELECT statement will retrieve all records from the USA_ABYSS_COMPANY table if the WHERE condition gets TRUE.
MariaDB date allow null greater than today example
Example of MariaDB CURRENT_DATE function used to find NULL value in the DATE column

We hope that you have understood how to use the MariaDB CURRENT_DATE column to find the NULL value in the DATE column. And for better understanding, we have used an example and explained it with the help of an example.

Read: MariaDB Delete From Statement

MariaDB Date Allow Null Hour

In this section, we will learn and understand how to use the MariaDB HOUR function with the IS NULL condition to find the NULL value in the table. And which will be explained with the help of an example

The MariaDB HOUR function is used to find the hour portion value from the expression or column_name in the query. Let’s see an example of the MariaDB HOUR function with the IS NULL condition to find the NULL value in the table by the following query:

EXAMPLE:

SELECT FIRST_NAME,LAST_NAME, HOUR(PURCHASE_DATE) FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE IS NULL;

As we see in the above query, the SELECT statement is used to select records of the FIRST_NAME and LAST_NAME columns of the USA_ABYSS_COMPANY table.

In the HOUR function, we have used the PURCHASE_DATE column to extract the hour portion value from the USA_ABYSS_COMPANY table. In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value from the USA_ABYSS_COMPANY table.

The SELECT statement will retrieve all records from the USA_ABYSS_COMPANY table if the WHERE statement is TRUE.

MariaDB date allow null hour example
Example of MariaDB HOUR function used with the IS NULL condition to find NULL value in the DATE column

In this section, we hope that you have understood how to use the MariaDB MONTH function with the IS NULL condition to find the NULL value on the PURCHASE_DATE column. We have used an example and explained it in detail for better understanding.

Read: MariaDB GROUP BY

MariaDB Date Allow Null Join

In this section, we will learn and understand how to use the MariaDB IS NULL condition on the JOIN statement of the two tables in the query. And which will be explained with the help of an illustrated example.

In MariaDB, JOIN is used to merge the rows from more than one table based on common columns in tables. In other words, The data is extracted from more than one table into a single table using the JOIN clause.

  • The JOIN clause can be used when there are two or more tables with common columns.

There are four types of JOIN in MariaDB:

  • INNER JOIN: It is a simple JOIN that retrieves all the rows from more than one table where the JOIN condition is True.
  • LEFT JOIN: It is a LEFT OUTER JOIN that retrieves all the rows from the left table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
  • RIGHT JOIN: It is a RIGHT OUTER JOIN that retrieves all the rows from the right table based on whatever is specified in the ON condition and returns those rows from another table where the JOIN condition is True.
  • CROSS JOIN: It returns the result set where each row in a table is joined with each row in another table.

Here is the syntax example of the MariaDB JOIN statement with the IS NULL condition to find the NULL value in the DATE column of the table by the following query:

SYNTAX:

SELECT TABLE_1.EXPRESSION_1,TABLE_1.EXPRESSION_2,TABLE_2.EXPRESSION_1,TABLE_2.EXPRESSION_2
FROM TABLE_1
INNER JOIN TABLE_2
ON TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN
WHERE [TABLE_1 | TABLE_2].COLUMN_NAME IS NULL;

Let’s see an illustrated example of the MariaDB JOIN statement with the IS NULL condition to find the NULL value in the DATE column of the table by the following query:

EXAMPLE:

SELECT USA_ABYSS_COMPANY.FIRST_NAME, USA_ABYSS_COMPANY.LAST_NAME,USA_ABYSS_COMPANY.PURCHASE_DATE,
USA_FAZZRO_COMPANY.FULL_NAME,USA_FAZZRO_COMPANY.JOINING_DATE FROM USA_ABYSS_COMPANY
INNER JOIN USA_FAZZRO_COMPANY 
ON USA_FAZZRO_COMPANY.ID= USA_ABYSS_COMPANY.EMP_ID
WHERE USA_ABYSS_COMPANY.PURCHASE_DATE IS NULL;

As we see in the above query, the MariaDB SELECT statement will retrieve all records from the FIRST_NAME, LAST_NAME, PURCHASE_DATE, FULL_NAME, and JOINING_DATE columns of the USA_ABYSS_COMPANY and USA_FAZZRO_COMPANY tables.

And based on the INNER JOIN clause, the common column is ID and EMP_ID of both tables USA_FAZZRO_COMPANY and USA_ABYSS_COMPANY.

In the WHERE condition, the PURCHASE_DATE column of the USA_ABYSS_COMPANY table is used with the IS NULL condition to find the NULL value in the table.

The SELECT statement will retrieve all records from both tables USA_ABYSS_COMPANY and USA_FAZZRO_COMPANY table if the WHERE condition gets TRUE.

MariaDB date allow null join example
Example of MariaDB JOIN statement used with the IS NULL condition to find NULL value in the DATE column

In this section, we have understood how to use the MariaDB JOIN statement with the IS NULL condition to find the NULL value in the USA_FAZZRO_COMPANY and USA_ABYSS_COMPANY table. We have used an example and explained it in detail for better understanding.

Read: MariaDB Max Connections

MariaDB Date Allow Null Like

In this MariaDB tutorial, we will learn and understand how to use the MariaDB LIKE condition with the IS NULL condition to find a NULL condition in the DATE column of the table. And which will be explained with the help of an illustrated example.

Wildcards can be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE query with the MariaDB LIKE condition. This gives us the ability to match patterns. Here is an illustrated example of the MariaDB LIKE condition with the IS NULL condition to find the NULL value in the table by the following query:

EXAMPLE:

SELECT FIRST_NAME,LAST_NAME, PURCHASE_DATE FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE IS NULL
AND LAST_NAME LIKE  's%';
  • As we see in the above query, the SELECT statement will retrieve all records of the FIRST_NAME, LAST_NAME, and PURCHASE_DATE columns of the USA_ABYSS_COMPANY table.
  • In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value in the USA_ABYSS_COMPANY table.
  • The percent operator is used in the LAST_NAME column to find the alphabet s at the beginning of the USA_ABYSS_COMPANY table using the LIKE operator.
MariaDB date allow null like example
Example of MariaDB LIKE condition used with the IS NULL condition to find NULL value in the DATE column

In this section, we hope that you have understood how to use the MariaDB LIKE condition with the IS NULL condition to find the NULL value on the PURCHASE_DATE column. We have used an example and explained it in detail for better understanding.

Read: How to load files into MariaDB

MariaDB Date Allow Null Month

In this section, we will learn how to use the MariaDB MONTH function with the IS NULL condition to find the NULL value in the DATE column of the table. And which will be explained with the help of a detail.

In MariaDB, the YEAR function is used to extract the month portion value from the expression or column_name in the query. Here is a sample example of the MariaDB MONTH function with the IS NULL condition to find the NULL value in the table by the following query:

EXAMPLE:

SELECT first_name,LAST_NAME, MONTH (PURCHASE_DATE) FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE IS NULL;
  • As we see in the above query, the SELECT statement is used to select records of the FIRST_NAME and LAST_NAME columns of the USA_ABYSS_COMPANY table.
  • In the MONTH function, we have used the PURCHASE_DATE column to extract the month portion value from the USA_ABYSS_COMPANY table.
  • In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value from the USA_ABYSS_COMPANY table.
  • The SELECT statement is used to select all records from the USA_ABYSS_COMPANY table if the WHERE condition is TRUE.
MariaDB date allow null month example
Example of MariaDB MONTH function used with the IS NULL condition to find NULL value in the DATE column

In this section, we hope that you have understood how to use the MariaDB MONTH function with the IS NULL condition to find the NULL value on the PURCHASE_DATE column. We have used an example and explained it in detail for better understanding.

Read: MariaDB Reserved Words

MariaDB Date Allow Null Now

Here we will understand how to use the MariaDB NOW function with the IS NULL condition to find the NULL value in the DATE column of the table. And which will be explained with the help of an illustrated example.

In MariaDB, the NOW function is used to extract the current DateTime from the table. Here is an illustrated example of the MariaDB NOW function with the IS NULL condition to find the NULL value in the DATE column of the table by the following query:

EXAMPLE:

CREATE TABLE USA_CAMEL_COMPANY
( PAGE_ID INT(11) NOT NULL,
WEBSITE_ID INT(11) NOT NULL,
HOST_NAME VARCHAR(45),
LAST_MODIFIED DATETIME NULL DEFAULT NOW()
 );

INSERT INTO USA_CAMEL_COMPANY(PAGE_ID, WEBSITE_ID, HOST_NAME) 
VALUES (101, 201, 'godaddy');

SELECT * FROM USA_CAMEL_COMPANY;

In the first query, we created a table called the USA_CAMEL_COMPANY table. In the USA_CAMEL_COMPANY table, we have added one DATE column as LAST_MODIFIED with the default value as CURRENT_TIME.

In the second query, we used the INSERT INTO statement on the USA_CAMEL_COMPANY table. But we have used the NULL constraint and the DEFAULT value as the NOW function for the LAST_MODIFIED column. If we want to retrieve all records from the USA_CAMEL_COMPANY table then we will use the SELECT statement.

MariaDB date allow null now example
Example of MariaDB DATE allow Null Now

We hope that you have understood how to allow NULL data type with the NOW function on the DATE column of the table for a better result set. We have used an illustrated example and explained it in detail for better understanding.

Read: MariaDB Check Empty String

MariaDB Date Allow Null Str_to_Date

Here we will learn and understand how to use the MariaDB STR_TO_DATE function with the IS NULL condition to find the NULL value in the DATE column of the table. And which will be explained with the help of an illustrated example.

In MariaDB, the STR_TO_DATE function is used to take a string and return the DATE in the format_mask from the query. Let’s have a look at the MariaDB STR_TO_DATE function with the IS NULL condition to find a NULL condition in the DATE column by the following query:

SYNTAX:

SELECT EXPRESSION, STR_TO_DATE(COLUMN_NAME, FORMAT_MASK) FROM TABLE_NAME
WHERE COLUMN_NAME IS NULL;

Now, let’s see an illustrated example of the MariaDB STR_TO_DATE function with the IS NULL condition to find the NULL value in the DATE column of the table by the following query:

EXAMPLE:

SELECT FIRST_NAME,LAST_NAME,STR_TO_DATE(PURCHASE_DATE,'%Y %m %d') AS STRING_TO_DATE 
FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE IS NULL;

As we see in the above query, the MariaDB SELECT statement is used to retrieve all records of the FIRST_NAME and LAST_NAME columns of the USA_ABYSS_COMPANY table.

In the STR_TO_DATE function, we have used the PURCHASE_DATE column to find the YYYY-MM-DD value in numeric digit from the USA_ABYSS_COMPANY table. In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value from the USA_ABYSS_COMPANY table.

To shorter the function_name, we have used the ALIAS clause with the AS keyword and given the name STRING_TO_DATE for output column_name. The SELECT statement will retrieve all records from the USA_ABYSS_COMPANY table if the WHERE condition gets TRUE.

MariaDB date allow null str_to_date example
Example of MariaDB STR_TO_DATE column used with the IS NULL condition to find NULL value in the DATE column

We hope that you have understood how to use the MariaDB STR_TO_DATE function with the IS NULL condition to find the NULL value in the PURCHASE_DATE column of the USA_ABYSS_COMPANY table. And we have used an example and explained it in detail.

Read: How to Drop Column from MariaDB Table

MariaDB Date Allow Null Update

In this section, we will learn and understand how to use the MariaDB UPDATE statement to find the NULL value in the table by the query. And which we will explain with the help of an illustrated example.

EXAMPLE:

UPDATE USA_ABYSS_COMPANY 
SET PURCHASE_DATE= ' 2022-07-09'
WHERE PURCHASE_DATE IS NULL;

SELECT * FROM USA_ABYSS_COMPANY;

Before we move to the explanation part of the UPDATE statement for finding the NULL value in the DATE column. If we want to modify the DATE column of the table with the NULL constraint then we can use the query method which is done in the MariaDB Date Allow Null Alter table.

As we see in the above query, we have used the UPDATE statement to set the value of the PURCHASE_DATE column from the NULL constraint to the new value as 2022-07-09. In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value of the USA_ABYSS_COMPANY table.

The UPDATE statement will set the new value as 2022-07-09 only when the WHERE condition gets TRUE. If we want to check the NULL value of the PURCHASE_DATE column has been changed into the 2022-07-09 as date_value then we will use the SELECT statement

in this section, we have understood how to use the MariaDB UPDATE statement to find the NULL value in the DATE column and set a new value in the USA_ABYSS_COMPANY table. For better understanding, we have used an example and explained it in detail.

Read: How to Add Column in MariaDB

MariaDB Date Allow Null Week

In this section, we will learn how to use the MariaDB WEEK function with the IS NULL condition to find the NULL value in the DATE column of the table. And which will be explained with the help of a detail.

In MariaDB, the WEEK function is used to extract the week portion value in the expression or column_name from the query. Here is an illustrated example of the MariaDB WEEK function with the IS NULL condition to find the week value in the table by the following query:

EXAMPLE:

SELECT first_name,LAST_NAME,WEEK(PURCHASE_DATE) FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE IS NULL;

As we see in the above query, the SELECT statement is used to select records of the FIRST_NAME and LAST_NAME columns of the USA_ABYSS_COMPANY table.

In the WEEK function, we have used the PURCHASE_DATE column to extract the week portion value from the table. In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value from the USA_ABYSS_COMPANY table.

The SELECT statement is used to select all records from the USA_ABYSS_COMPANY table if the WHERE condition is TRUE.

MariaDB date allow null week example
Example of MariaDB WEEK function used with the IS NULL condition to find NULL value in the DATE column

In this section, we hope that you have understood how to use the MariaDB WEEK function with the IS NULL condition to find the NULL value on the PURCHASE_DATE column. We have used an example and explained it in detail for a better understanding of all.

Read: How to Create Table in MariaDB

MariaDB Date Allow Null Year

In this section, we will learn how to use the MariaDB YEAR function with the IS NULL condition to find the NULL value in the DATE column of the table. And which will be explained with the help of a detail.

In MariaDB, the YEAR function is used to extract the year value from the expression or column_name in the query. Here is an illustrated example of the MariaDB YEAR function with the IS NULL condition to find the NULL value in the table by the following query:

EXAMPLE:

SELECT first_name,LAST_NAME,YEAR(PURCHASE_DATE) FROM USA_ABYSS_COMPANY
WHERE PURCHASE_DATE IS NULL;

As we see in the above query, the SELECT statement is used to select records of the FIRST_NAME and LAST_NAME columns of the USA_ABYSS_COMPANY table.

In the YEAR function, we have used the PURCHASE_DATE column to extract the year value from the table. In the WHERE condition, the PURCHASE_DATE column is used with the IS NULL condition to find the NULL value from the USA_ABYSS_COMPANY table.

The SELECT statement is used to select all records from the USA_ABYSS_COMPANY table if the WHERE condition is TRUE.

MariaDB date allow null year example
Example of MariaDB YEAR function used with the IS NULL condition to find NULL value in the DATE column

In this section, we hope that you have understood how to use the MariaDB YEAR function with the IS NULL condition to find the NULL value on the PURCHASE_DATE column. We have used an example and explained it in detail for better understanding.

You may also like to read the following MariaDB tutorials.

So, in this tutorial, we have learned how to use the MariaDB Date Allow Null condition. Additionally, we have also covered the following set of topics.

  • MariaDB Date Allow Null
  • MariaDB Date Allow Null Alter Column
  • MariaDB Date Allow Null By DateTime
  • MariaDB Date Allow Null Error
  • MariaDB Date Allow Null Group By
  • MariaDB Date Allow Null Greater Than
  • MariaDB Date Allow Null Greater Than Today
  • MariaDB Date Allow Null Hour
  • MariaDB Date Allow Null Join
  • MariaDB Date Allow Null Like
  • MariaDB Date Allow Null Month
  • MariaDB Date Allow Null Now
  • MariaDB Date Allow Null Or Empty
  • MariaDB Date Allow Null Str_to_Date
  • MariaDB Date Allow Null Update
  • MariaDB Date Allow Null Week
  • MariaDB Date Allow Null Year