MariaDB Str_To_Date [10 Amazing Examples]

In this MariaDB tutorial, we will discuss the MariaDB Str_To_Date function which is used to convert string_date to date_value in the table. Moreover, we will also look at several examples related to this function. There are lists of the topic that comes under discussion:

  • MariaDB Str_To_Date
  • MariaDB Str_To_Date Timezone
  • MariaDB Str_To_Date Milliseconds
  • MariaDB Str_To_Date Null
  • MariaDB String to Date Time
  • MariaDB Cast to Date
  • MariaDB Cast Datetime To Date
  • MariaDB Cast String to Date
  • MariaDB String to Date Conversion
  • MariaDB Format String to Date

MariaDB Str_To_Date

Here we will learn and understand how to use MariaDB Str_To_Date function on the table by the query, which is explained with the help of an illustrated example.

In MariaDB, the STR_TO_DATE function is used to take a string and return date by using the FORMAT_MASK parameter on the table. Here is the syntax of the MariaDB STR_TO_DATE function by the following query:

SYNTAX:

SELECT EXPRESSION, STR_TO_DATE( STRING, FORMAT_MASK)
FROM TABLE_NAME;

In the syntax explanation:

  • STRING: We will take the STRING parameter as a value which is formatted as a date.
  • FORMAT_MASK: The format to apply in a string. Here is a list of options for the format_mask parameter and this parameter can be used in many combinations.
VALUEDESCRIPTION
%pAM or PM
%DMonth day as a numeric value followed by a suffix (1st, 2nd, 3rd)
%dIt is a day of the month as a numeric value (01 to 31)
%eIt is a day of the month as a numeric value (0 to 31)
%jIt is a day of the year from 001 to 366.
%wIt is a day of the week where sunday=0 and saturday=6.
%HHour (00 to 23).
%hHour (00 to 12 ).
%lHour (00 to 12 ).
%bAbbreviation of the month name as (Jan to Dec ).
%cMonth as a numeric value (0 to 12).
%fMicroseconds (000000 to 999999).
%iMinutes (00 to 59).
%MMonth Name in full (January to December).
%mMonth name as a numeric value (00 to 12).
%rTime in 12 hour AM or PM format (hh:mm:ss AM/ PM)
%T24 Hour Format (hh:mm: ss)
%USunday is the first day of the week during where Week (00 to 53).
%uMonday is the first day of the week during where Week (00 to 53).
%VA week where Sunday is the first day of the week (01 to 53)
Used with %X
%vA week where Sunday is the first day of the week (01 to 53)
Used with %X
%WWeekday name in full (Monday to Saturday).
%xIt is the year of the week where Monday is the first day of the week and used with %V
%XIt is the year of the week where Sunday is the first day of the week and used with %v
%yYear numeric value in 2 digit
%YYear numeric value in 4 digit
SYNTAX of the STR_TO_DATE function

Let’s see a sample example of the MariaDB STR_TO_DATE function on the table by the following query:

EXAMPLE:

SELECT STATE_NAME,FULL_NAME,STR_TO_DATE(STATE_ENTERDATE,'%M %e, %Y') FROM united_stateof_america
WHERE STATE_ID>=5;

As we see in the above query, the SELECT statement retrieves all records of the STATE_NAME and FULL_NAME columns from the UNITED_STATEOF_AMERICA table. In the STR_TO_DATE function, we have to convert the STRING_DATE into ACTUAL_DATE format of the STATE_ENTERDATE column of the UNITED_STATEOF_AMERICA table by using the FORMAT_TASK parameter in the function.

In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 5 from the UNITED_STATEOF_AMERICA table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the UNITED_STATEOF_AMERICA table. But if the SELECT statement is executed successfully and retrieves an empty record set from the UNITED_STATEOF_AMERICA table only when the WHERE condition gets a FALSE value.

MariaDB str_to_date function example
Example of MariaDB STR_TO_DATE function

We hope that you have understood how to use MariaDB STR_TO_DATE function on the table by the query. For a better understanding, we have used an example and explained it in depth.

Read: MariaDB Unique Key

MariaDB Str_To_Date Timezone

In this MariaDB subtopic, we will learn the subtopic “MariaDB STR_TO_DATE Timezone” by using MariaDB CONVERT_TZ and STR_TO_DATE functions on the table by the query, which will be explained with the help of an illustrated example.

The MariaDB CONVERT TZ() method converts a datetime value from one time zone to another using the parameters from_tz and to_tz and then returns the result. Here is the syntax of the MariaDB CONVERT_TZ and STR_TO_DATE function on the table by the following query:

SYNTAX:

SELECT EXPRESSION, CONVERT_TZ (STR_TO_DATE, FORMAT_TASK), FROM_TZ, TO_TZ)
FROM YOUR_TABLE_NAME
WHERE [CONDITIONS];

Here is a sample of the MariaDB CONVERT_TZ and STR_TO_DATE functions on the table by the following query:

EXAMPLE:

SELECT *,CONVERT_TZ(STR_TO_DATE( STATE_ENTERDATE,'%M %e, %Y'),'+00:00','+10:00') AS STRING_TO_DATE
FROM united_stateof_america
WHERE STATE_ID>8
ORDER BY STATE_NAME DESC
LIMIT 6;

In this previous query, the SELECT statement is used to retrieve all records of the column from the UNITED_STATEOF_AMERICA table. In the CONVERT_TZ function, it is also used with a sub-function on the STR_TO_DATE function.

In the STR_TO_DATE function, it is used with the STATE_ENTERDATE column which will convert the string_date to date_value in the UNITED_STATEOF_AMERICA table.

Then in the CONVERT_TZ function, it will add the timezone from 00:00 to 10:00 hrs in each record of the STATE_ENTERDATE column of the UNITED_STATEOF_AMERICA table.

In the WHERE condition, we have used the STATE_ID column with the GREATER THAN operator to find a value greater than 6 in the UNITED_STATEOF_AMERICA table.

In the end, we used the ORDER BY clause to arrange the records of the STATE_NAME column in descending order. And to limit the records for the result set, we have used the LIMIT clause as LIMIT 6 to retrieve the first 6 records from the UNITED_STATEOF_AMERICA table.

If the SELECT statement retrieves all records from the UNITED_STATEOF_AMERICA table only when the WHERE condition turns out to be TRUE otherwise vice-versa.

MariaDB str_to_date timezone example
Example of MariaDB STR_TO_DATE Timezone

We hope that you have understood the subtopic “MariaDB STR_TO_DATE Timezone” by using the MariaDB CONVERT_TZ and STR_TO_DATE function on the table by the query. For a better understanding, we have used an example and explained it in depth.

Read: MariaDB Difference Between Two Dates

MariaDB Str_To_Date Milliseconds

Here we will learn how to use MariaDB STR_TO_DATE function with FORMAT_TASK as MILLISECONDS of the column on the table by the query, which will be explained with the help of an illustrated example.

EXAMPLE:

SELECT STATE_NAME,STR_TO_DATE(STATE_ENTERDATE,'%M %e, %Y,%f') FROM united_stateof_america
WHERE STATE_ID<=5;

In the previous query, we used the SELECT statement to retrieve all records of the STATE_NAME column from the UNITED_STATEOF_AMERICA table.

In the STR_TO_DATE function, it will convert the string_date to the DATE value of the STATE_ENTERDATE column and also add the microseconds by using the %F in the FORMAT_TASK of the UNITED_STATEOF_AMERICA table.

In the WHERE condition, the STATE_ID column is used with the LESS THAN or EQUAL TO operator to find a value less than or equal to 5 from the UNITED_STATEOF_AMERICA table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve an empty record set from the UNITED_STATEOF_AMERICA table otherwise vice-versa.

MariaDB str_to_date milliseconds example
Example of MariaDB STR_TO_DATE Milliseconds

We hope that you have understood the subtopic “MariaDB STR_TO_DATE Milliseconds” by using MariaDB STR_TO_DATE function on the table by the query. We have used an example and explained it in depth, for better understanding.

Read: MariaDB Date Greater Than

MariaDB Str_To_Date Null

Here we will learn and understand how to use MariaDB STR_TO_DATE function with NULL parameter in the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

SELECT STATE_NAME,STR_TO_DATE(NULL,'%M %e, %Y,%f') AS STRING_TO_DATE FROM united_stateof_america
ORDER BY STATE_NAME DESC;

In the preceding query, the SELECT statement retrieves all records of the STATE_NAME column from the UNITED_STATEOF_AMERICA table. In the STR_TO_DATE function, we have used the NULL value as string_date which will help to convert the string_date to date_value from the UNITED_STATEOF_AMERICA table.

Then we have also used the ALIAS clause with the AS keyword to shorter the function_name and given the name as STRING_TO_DATE column for the result set. In the end, we have used the ORDER BY clause to arrange the strings records of the STATE_NAME column in descending order which is based on the alphabetical order in the UNITED_STATEOF_AMERICA table.

MariaDB str_to_date null example
Example of MariaDB STR_TO_DATE function with a NULL parameter

We hope that you have understood the subtopic “MariaDB STR_TO_DATE Null” by using MariaDB STR_TO_DATE function on the table by the query. For a better description, we have used an example and clarified it in depth.

Read: MariaDB Check Empty String

MariaDB String to DateTime

In this subtopic tutorial, we will learn how to convert MariaDB STRING to DATETIME by using MariaDB STR_TO_DATE function on the table by the query. which is explained with the help of an illustrated example.

EXAMPLE:

SELECT STATE_NAME,STR_TO_DATE(STATE_ENTERDATE,'%M %e, %Y') AS STRING_TO_DATE FROM united_stateof_america
WHERE STATE_ID >6 
AND STATE_NAME LIKE '%a%';

The SELECT statement retrieves all records of the STATE_NAME column from the UNITED_STATEOF_AMERICA table. In the STR_TO_DATE function, we have used the STATE_ENTERDATE column to convert the string_date to the DATE value with the help of the FORMAT_TASK parameter.

To shorter the function_name, we have used the ALIAS clause with the AS keyword and given the name STRING_TO_DATE column for the output result set.

In the WHERE condition, the STATE_ID column is used with the GREATER THAN operator to find a value greater than 6 in the UNITED_STATEOF_AMERICA table. And we have used the LIKE operator to find the alphabet “a” within the string_name of the STATE_NAME column in the UNITED_STATEOF_AMERICA table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records of the UNITED_STATEOF_AMERICA table. But if the SELECT statement gets executed successfully and retrieves an empty record set from the UNITED_STATEOF_AMERICA table only when the WHERE condition gets a FALSE value.

MariaDB string to datetime example
Example of MariaDB STRING to DATETIME

We hope that you have understood how to use MariaDB STR_TO_DATE function to convert STRING to DATETIME value from the table by the query. For a better illustration, we have used an example and defined it in the chasm.

Read: MariaDB Check String Length

MariaDB Cast to Date

In this MariaDB section, we will learn how to use the MariaDB CAST function to get the DATE value from the table by the query, which will be explained with the help of syntax and examples.

In MariaDB, the CAST function is used to convert a value of one data type to another data from the table by the query. Let’s see the syntax of the MariaDB CAST function by the following query:

SYNTAX:

SELECT EXPRESSION, CAST ( [VALUE | COLUMN_NAME]  AS TYPE )
FROM TABLE_NAME
WHERE [CONDITIONS];

In this syntax explanation:

  • VALUE: The value to convert into the other datatype.
  • TYPE: The datatype that we wish to convert the VALUE for another datatype. Here is one of the following:
VALUEDESCRIPTION
BINARYIt converts VALUE to BINARY type which has a binary string.
CHARIt converts VALUE to CHAR type which has a fixed length string.
DATEIt converts VALUE to DATE type which has DATE portion only and format is ‘YYYY-MM-DD’.
DATETIMEIt converts VALUE to DATETIME type which has DATETIME portion only and formats as ‘YYYY-MM-DD HH:MM: SS’.
SIGNEDIt converts VALUE to SIGNED type which has signed 64 integers.
TIMEIt converts VALUE to TIME type and which has TIME portion only. And it formats as ‘HH:MM: SS’.
UNSIGNEDIt converts VALUE to UNSIGNED type and which has unsigned 64 integers only.
Description of VALUE datatype

Here is a sample example of the MariaDB CAST function on the table by the following query:

EXAMPLE:

SELECT PATIENT_FIRSTNAME,PATIENT_LASTNAME, PATIENT_ADMITDATE,CAST(PATIENT_DISCHARGEDATE AS DATE) 
FROM JOHNS_HOPKINS_HOSPITAL
WHERE PATIENT_ID >=6
LIMIT 2;

In the above query, we have used the SELECT statement to retrieve all records of the PATIENT_FIRSTNAME, PATIENT_LASTNAME and PATIENT_ADMITDATE columns from the JOHNS_HOPKINS_HOSPITAL table.

In the CAST function, we have used the PATIENT_DISCHARGEDATE column with DATETIME datatype which means it converts the DATETIME type to the DATE type in the JOHNS_HOPKINS_HOSPITAL table.

To shorter the function_name, we have used the ALIAS clause with the AS keyword and given the name as CAST_FUNCTION for the output column in the result set.

In the WHERE condition, the PATIENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 6 from the JOHNS_HOPKINS_HOSPITAL table. And to limit the record of the JOHNS_HOPKINS_HOSPITAL table, we have used the LIMIT clause as LIMIT 2 in the table at the end of the query.

If the WHERE condition turns out to be TRUE then the SELECT statement retrieves all records from the JOHNS_HOPKINS_HOSPITAL table otherwise vice-versa.

MariaDB cast to date example
Example of MariaDB CAST function to cast in date

We hope that you have understood how to use the MariaDB CAST function to cast to DATE value in the table by the query. For a better illustration, we have used a sample example and explained it in depth.

Read: MariaDB Check If Rows Exists

MariaDB Cast Datetime To Date

We will learn and understand how to cast DATETIME to DATE value by using the CAST function on the table by the following query:

EXAMPLE:

SELECT PATIENT_FIRSTNAME,PATIENT_LASTNAME,CAST(PATIENT_ADMITDATE AS DATE) AS CAST_FUNCTION
FROM JOHNS_HOPKINS_HOSPITAL
WHERE PATIENT_ID >=10;

In the previous query, the SELECT statement retrieves all records of the PATIENT_FIRSTNAME and PATIENT_LASTNAME columns from the JOHNS_HOPKINS_HOSPITAL table. In the CAST function, the PATIENT_ADMITDATE column is used with the DATETIME datatype which means that it will convert the DATETIME datatype to the DATE value in the JOHNS_HOPKINS_HOSPITAL table.

Then we used the ALIAS clause with the AS keyword to shorter the CAST function_name and gave the name as CAST_FUNCTION for the output result set.

In the WHERE condition, the PATIENT_ID column is used with the GREATER THAN or EQUAL TO operator to find values greater than or equal to 10 from the JOHNS_HOPKINS_HOSPITAL table.

But if the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records with the CAST function from the JOHNS_HOPKINS_HOSPITAL table otherwise vice-versa.

MariaDB cast datetime to date example
Example of MariaDB Cast DateTime to Date

We hope that you have understood the subtopic “MariaDB Cast Datetime to Date” by using the MariaDB CAST function on the table by the query. We have used an example and explained it in depth, for better understanding.

Read: MariaDB Insert If Not Exists

MariaDB Cast String to Date

We will learn and understand how to use the MariaDB CAST function to cast string to date in the table by the following query:

EXAMPLE:

SELECT FULL_NAME,CAST( STATE_ENTERDATE AS DATE) AS CAST_FUNCTION
FROM UNITED_STATEOF_AMERICA
WHERE STATE_ID =5;

In the previous query, the SELECT statement retrieves all records of the FULL_NAME column from the UNITED_STATEOF_AMERICA table. In the CAST function, we have used the STATE_ENTERDATE column to cast VARCHAR to DATE datatype in the UNITED_STATEOF_AMERICA table.

In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 5 from the UNITED_STATEOF_AMERICA table. If the WHERE condition gets a TRUE value then the SELECT statement will retrieve all records from the UNITED_STATEOF_AMERICA table otherwise vice-versa.

As we s, the STATE_ENTERDATE column carries half the number and string as a date and by using the CAST function makes it difficult to cast into DATE datatype. If the STATE_ENTERDATE column carries a full string of dates then it could convert into the DATE datatype for the result set in the UNITED_STATEOF_AMERICA table.

MariaDB cast string to date example
Example of MariaDB Cast String to Date

We hope that you have understood the subtopic “MariaDB Cast String to Date” by using the MariaDB CAST function on the table by the query. For a better elucidation, we have used an illustration and explained it in depth.

Read: MariaDB Between + Examples

MariaDB String to Date Conversion

Here we will learn and understand how to use MariaDB STR_TO_DATE function to convert String to Date Conversion on the table by the following query:

EXAMPLE:

SELECT STATE_ID,FULL_NAME,STR_TO_DATE( STATE_ENTERDATE,'%M %e, %Y') AS STRING_TO_DATE
FROM UNITED_STATEOF_AMERICA
GROUP BY FULL_NAME
ORDER BY STATE_ID DESC
LIMIT 5;

As we see in the above query, we have used the SELECT statement to retrieve all records of the STATE_ID and FULL_NAME columns from the UNITED_STATEOF_AMERICA table. In the STR_TO_DATE function, we have used the STATE_ENTERDATE column to convert string_date to date_value in the UNITED_STATEOF_AMERICA table.

To shorter the function_name, we have used the ALIAS clause with the AS keyword to shorter the STR_TO_DATE function and given the name STRING_TO_DATE column for the output result set.

In the end, we have used the GROUP BY clause to group the FULL_NAME column by using the GROUP BY clause and arrange them in descending order of the STATE_ID column by using the ORDER BY expression DESC.

In the end, we have used the LIMIT clause as LIMIT 5 to show the first 5 records from the UNITED_STATEOF_AMERICA table.

MariaDB string to date conversion example
Example of MariaDB String to Date Conversion

We hope that you have understood the subtopic “MariaDB String to Date Conversion” by using the MariaDB STR_TO_DATE function on the table by the query. For a better elucidation, we have used an example and demonstrated it in profundity.

Read: MariaDB Drop Index 

MariaDB Format String to Date

In this MariaDB subtopic tutorial, we will understand the subtopic “MariaDB Format String to Date” by using the MariaDB STR_TO_DATE function on the table by the following query:

EXAMPLE:

SELECT  *,FULL_NAME,STR_TO_DATE( STATE_ENTERDATE,'%M %e, %Y') AS STRING_TO_DATE
FROM UNITED_STATEOF_AMERICA
WHERE STATE_ID>8
GROUP BY FULL_NAME
LIMIT 2;

In the preceding query, the SELECT statement retrieves all records from the UNITED_STATEOF_AMERICA table. In the STR_TO_DATE function, we have used the parameter as the STATE_ENTERDATE column which will convert the string_date to date_value from the UNITED_STATEOF_AMERICA table.

Then we used the ALIAS clause as AS keyword to shorter the function name as the STRING_TO_DATE column for the output result set.

In the WHERE condition, we have used the STATE_ID column with the GREATER THAN operator to find a value greater than 8 from the UNITED_STATEOF_AMERICA table. Then we group the FULL_NAME column by using the GROUP BY clause. In the end, we have used the LIMIT clause as LIMIT 2 from the UNITED_STATEOF_AMERICA table for the output result set.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the UNITED_STATEOF_AMERICA table. But if the SELECT statement gets executed successfully and retrieves an empty record set from the UNITED_STATEOF_AMERICA table only when the WHERE condition gets a FALSE value.

MariaDB format string to date example
Example of MariaDB Format String to Date

We hope that you have understood the subtopic “MariaDB Format String to Date” by using the MariaDB STR_TO_DATE function on the table by the query. For a better explanation, we have used an example and explained it in depth.

Yao may also like to read the following MariaDB tutorials.

In this MariaDB lesson, we covered the use of the MariaDB STR_TO_DATE function and various sample instances related to it. There are several listings of the topics that are discussed:

  • MariaDB Str_To_Date
  • MariaDB Str_To_Date Timezone
  • MariaDB Str_To_Date Milliseconds
  • MariaDB Str_To_Date Null
  • MariaDB String to Date Time
  • MariaDB Cast to Date
  • MariaDB Cast Datetime To Date
  • MariaDB Cast String to Date
  • MariaDB String to Date Conversion
  • MariaDB Format String to Date