sql update command to update a date-of-birth

50 pts.
Date format
I need to change the date-of-birth MONTH to May and DAYS to 15 without changing the YEAR

Answer Wiki

Thanks. We'll let you know when a new response is added.

It has been a while since I did any Oracle but I think this will work..

UPDATE your_table
SET date-of-birth = ’15-MAY-‘||EXTRACT(YEAR FROM date-of-birth)

and please don’t forget a WHERE clause (unless you want all birth dates set to May 15th)


Discuss This Question: 5  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Mji
    UPDATE your_table SET field_DOB = '27-FEB-'||EXTRACT(YEAR FROM field_DOB) where ... It's working! Thanks so much.
    50 pointsBadges:
  • Meandyou
    You're very welcome. And thank you for letting me know that it worked.
    5,220 pointsBadges:
  • carlosdl
    I was searching for something else and stumbled upon this question.

    Even though the question is very old and has been dead for a decade, I still think it is worth commenting, because the provided answer shows how NOT to do operations on date columns in Oracle.

    If date-of-birth is actually a date (meaning the column was defined using the DATE data type), then only actual dates can be inserted or assigned to it, so, if someone tries to insert or assign a string to that column, the database engine must convert that string into an actual date, and it does it in the background using local NLS settings.

    That means that if the statement is run from a system that has a local NLS setting in which the date language or format is different, the statement could fail.

    In addition to that, the EXTRACT function returns a number, so, to be able to concatenate the results from EXTRACT to the string '15-MAY-', the engine has to convert it to a string first, and it also does it implicitly.

    Implicit Conversions are evil.

    The OP said the suggested solution worked for them, which is good, but it could fail for someone else.

    So, to avoid the unnecessary conversion added by using EXTRACT, the OP could have used TO_CHAR to extract the year, and to make the conversion to an actual date in an NLS-independent way, they should make the conversion explicitly defining the date format, like this:

    UPDATE your_table
    SET date_of_birth = TO_DATE('15-MAY-' || TO_CHAR(date_of_birth,'YYYY'), 'DD-MON-YYYY')
    WHERE ...
    85,865 pointsBadges:
  • ToddN2000
    I agree with Carlos. Not knowing what type of data base the poster was dealing with or their date format you would need to go that extra step to make sure you are updating it correctly. It could be any of a number of date formats like
    I have even seen some companies store dates in Julian format for security issues...go figure.

    136,250 pointsBadges:
  • TheRealRaven
    Also agree, and I think @carlosdl was right to update an old thread for correctness/completeness. (Unlike the continuing spam updates to various Outlook, etc., threads touting questionable products.)
    36,880 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: