CODEWITHSUNDEEP
oracle-databasedate
Chris
Chris

Reputation: 124

Oracle Is there a way to update date and not month, year

Is there a way to update the date part of a column of type Date. For example, a row in the DB has value 2013-11-22 [stored in YYYY-MM-DD format]. And I want to update it to 2013-11-01.

If it had been one or few rows I could have done a simple update. But the real problem is that there are about hundred thousand rows with different date values. And I want to update all the dates to the 1st of each month. For example

     Actual    What I would like it to be
    2013-01-22  2013-01-01
    1989-10-03  1989-10-01
    2004-07-01  2004-07-01
    2005-12-31  2005-12-01

Would appreciate any help, how can i get this done through SQL. Thanks in Advance.

Upvotes: 0

Views: 622

Answers (2)

A.B.Cade
A.B.Cade

Reputation: 16905

Assuming that the data type is a date, use trunc

TRUNC(date_column, 'mm')

Here is a sqlfiddle demo

Upvotes: 6

tylert
tylert

Reputation: 301

Yes, you can use the trunc() function.

For example:

UPDATE thetable set datevalue = trunc(datevalue, 'MM');

Upvotes: 2

Related Questions