MySQL convert datetime to date – MySQL CONVERT() and CAST() Functions with Best 5 Example

mysql convert datetime to date using “CAST”. You can run this simple Query to convert from datetime to date mysql Example

mysql convert datetime to date Example

SELECT CAST(orders.crated_at AS DATE) AS join_DATE

MySQL Query to convert from datetime to date?

simple single query to convert from datetime to date in MySQL Example.

mysql> select cast(join_date as Date) as Date from MemberInformation;

The following is the output.

+------------+
| Date       |
+------------+
| 2021-08-17 |
| 2021-11-25 |
| 2021-09-24 |
| 2021-04-30 |
+------------+
4 rows in set (0.00 sec)

MySQL CONVERT() Function

Syntax

CONVERT( value, type )

Example: mysql convert datetime to date

SELECT CONVERT("2022-08-29", DATE);

The following is the output.

Number of Records: 1
2022-08-29

MySQL STR_TO_DATE() Function with Example

mysql convert datetime to date Using MySQL STR_TO_DATE examples
Example 1:

SELECT STR_TO_DATE('25,4,2022','%d,%m,%Y');  //2022-04-25

Example 2:

SELECT STR_TO_DATE('25,4,2022 extra characters','%d,%m,%Y');  //2022-04-25

Example 3:

SELECT STR_TO_DATE('2022','%Y');   //2022-00-00

Example 4:

SELECT STR_TO_DATE('122504','%h%i%s'); // 12:25:04

Example 5:

SELECT STR_TO_DATE('12','%h'); // 12:00:00

Example 6:

SELECT STR_TO_DATE('20220101 1125','%Y%m%d %h%i') ;  // 2022-01-01 12:25:00

Don’t Miss : Convert Date Format In Php

Read Also:  how to get url in controller in codeigniter?

How to Get the Date from a Datetime Column in MySQL?

SELECT member_first_name,
       member_last_name,
       DATE(crated_at) 
         AS  join_date
FROM members;

Specify string format using format specifiers

  SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %T.%f');
  # 2022-12-25 18:11:41.000000

How to Query Date and Time in MySQL

  • SELECT now(); — date and time
  • SELECT curdate(); –date
  • SELECT curtime(); –time in 24-hour format

To find rows between two dates or timestamps:

SELECT *
FROM tasks
where task_list_date between '2021-01-01' and '2021-01-31';

To find rows created within the last week:

Read Also:  Simple PHP Data Type Conversion Example

SELECT *
FROM tasks
WHERE task_list_date > date_sub(now(), interval 1 week);

To convert a timestamp to a unix timestamp (integer seconds):

SELECT unix_timestamp('2022-11-08');

//OR

SELECT unix_timestamp('2022-11-08 14:53:21');

//OR

SELECT unix_timestamp();

PHP date() format when inserting into datetime in MySQL

Examples:

DATE: YYYY-MM-DD
Example: 2022-11-25

DATETIME: YYYY-MM-DD HH:MI:SS
Example: 2022-11-25 22:30:10

TIMESTAMP: YYYY-MM-DD HH:MI:SS
Example: 2022-11-25 22:30:10

YEAR: YYYY or YY

mysql convert datetime to date
mysql convert datetime to date

PHP program to insert date into the table.

index.php

<?php

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "pakainfo_v1";

$link = mysqli_connect( $servername, $username, $password, $dbname );

if ( !$link ) {
	die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO members( created_at )
		VALUES( '2022-11-25 22:40:10' );";

if (mysqli_query($link, $sql)) {
	echo "Good Luck New Member created successfully";
} else {
	echo "Error: " . $sql . "<br>" . mysqli_error($link);
}

mysqli_close($link);
?>

I hope you get an idea about mysql convert datetime to date.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.