CODEWITHSUNDEEP
mysqlsqlsql-update
SnakeDoc
SnakeDoc

Reputation: 14361

Update only day portion of SQL Date

I have a field with random Date types, for example:

2005-01-08
2001-11-15
1988-05-06

I need to change these all to have the same day portion of the date, preserving the year and month. Like:

2005-01-01
2001-11-01
1988-05-01

How can this be done with plain old SQL?

Upvotes: 3

Views: 1381

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

Easiest way would be using date_format 

mysql> select date_format('2005-01-08','%Y-%m-01') as fday ;
+------------+
| fday       |
+------------+
| 2005-01-01 |
+------------+
1 row in set (0.02 sec)

So the update command becomes

update table_name
set date_col = date_format(date_col,'%Y-%m-01') ;

Upvotes: 7

Related Questions