CODEWITHSUNDEEP
mysqlsql
user2060677
user2060677

Reputation: 35

SQL update date field when it contains a specific hour/minute


I need a little help in updating my DATE fields on my db.
I have to update every DATE field that has a date in which hour, minute and seconds are "00:00:00".
The format of the date is "yyyy-mm-dd HH:mm:ss".
First question: is there a way to do this?
Second question: how can it be done?

Thank you very much for the help!


EDIT: I need to update that field with the same date with only hour (and minute, and seconds) changed. Thanks

Upvotes: 2

Views: 1641

Answers (2)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

update table_name
set column_name='your date'
where DATE_FORMAT(column_name, "%h/%i/%s") = "00:00:00"

OR in sql server

update table_name
set column_name='your date'
where 
CONVERT(VARCHAR(8), column_name, 14)='00:00:00'

Upvotes: 0

Dipesh Parmar
Dipesh Parmar

Reputation: 27364

You can use DATE_FORMAT MySQL function.

Example

UPDATE Table_Name set Field ='EDITVALUE'
   WHERE DATE_FORMAT(dateField, "%h/%i/%s") = "00:00:00";

Edit Answer

You can use CONCAT MySQL function.

UPDATE Table_Name set dateField = CONCAT(date(dateField),' 05:12:50');

Upvotes: 3

Related Questions