Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Character to Date

Character to Date

Character to Date

How do I convert a character field to a date field? I created a table with a date. However I defined the field as a char field because I did not know I would be doing any manipulation with it. Now I need to convert the field to a date without having to redo the table. Help me please...

RE: Character to Date

What RDBMS are you using?

RE: Character to Date

You cannot directly change the datatype from char to date if there is data in the column.
There are a couple of ways to take care of the problem:

1.  If you are using Oracle 8.1, you can add a column to the table, update the table so that all of the rows have the new column populated with the date equivilent of the character column you currently have.  Then drop the character column.

2.  For pre-8.1, you can do all of the above except dropping the character column.  You COULD just set the character column to all NULL values and just ignore it.  
However, you could take a cleaner approach:
a.  Create a temporary table populated by your table's primary key column and the character column.
b.  Set all of the values in the character column to NULL.
c.  Now that there is no data in the character column, you can ALTER the table to MODIFY the column to a DATE datatype.
d.  Update your date column with the date equivilent of the char values in the temporary table created in Step a.  Do this by joining your table with the temp table (this is why you included the primary key column in the temporary table).
e.  Finally, after you verify the correctness of your data, drop the temporary table.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close