SQL question?

Question

I need to change data in one column of my table, example: 19870301 to 1987-03-01

How can I do this for an entire column, as I have 7 hundred something rows of data, I don't want to update every single one individually.

All the data is like this, I need to make it to the date format. but if i use the date command all my data is gone. please help

Answer 1

1. make sure that the destination column has enough space (e.g min varchar(10) or char(10). If the source column is named 'scol' and the destination is 'dcol' in 'yourtable' execute:



update yourtable set dcol=

concat( concat ( concat( concat( substr( scol,1,4),'-'), substr( scol,5,2)), '-'), substr( scol,7,2));





commit;





This is ANSI SQL synthax and it should work across several db systems.



n.b.

the source and the destination column could be same.

Answer 2

Step 1. Create a temporary column. Set its type to datetime

Step 2. Write an SQL update statement that takes the old datetime strings and sets them to the new column. It'll look something like this: 'UPDATE mytable SET newdate = myparsefunction(stringdate);'

Step 3. Delete the old column and rename the new column with the name of the old column.



P.S. Remember to stop your app before running this.

Answer 3

Which datatype you are using?



if sql server then i suggest write a stored procedure or simple block of code that will run a loop on the table and will update the table rows so you wont need to update each rows manually.



Hope you understood this

Answer 4

First make a sql using string methods and update it to date format and once its ready change the field type

Answer 5

select convert(char(10), cast(cast(yourcolumn as varchar) as datetime), 120) from yourtable



--For update:



Update yourtable

set yourcolumn = convert(char(10), cast(cast(yourcolumn as varchar) as datetime), 120)