CODEWITHSUNDEEP
sqlsql-server
Arnab
Arnab

Reputation: 2354

Adding columns in sql server with transformed data from same table

I have a table in sql server db which has a 'nvarchar' datatype column with datetime data.

I want to add two more columns to the table, one having the whole datetime data in 'datetime' datatype and the other column should have just the date in 'datetime' datatype

I have around 5 million rows in the table.

The nvarchar data looks like this: 2013-03-20 00:00:50

I would sincerely appreciate if someone could help me with a sql command which would do this..

Thanks

Upvotes: 0

Views: 1501

Answers (3)

tom redfern
tom redfern

Reputation: 31760

Try this:

update table x
set dateColumnWithTime = cast(MyVarcharDate as datetime), 
    datecolumnWithoutTime = DATEADD(Day, DATEDIFF(Day, 0, cast(MyVarcharDate as datetime)), 0)

Output:

dateColumnWithTime      datecolumnWithoutTime
----------------------- -----------------------
2013-03-20 00:00:50.000 2013-03-20 00:00:00.000

Upvotes: 2

Rahul
Rahul

Reputation: 77876

First of all storing datetime type as nvarchar is wrong. Instead of storing the the data as two more column(s); you get the data from your nvarchar column and display it accordingly like below and that way you can avoid doing update on 5M rows

SELECT CAST(nvarchar_column as DATETIME) as datetime_col,
CAST(nvarchar_column as DATE) as date_col
from my_5m_rows_table

Upvotes: 0

jpw
jpw

Reputation: 44881

What you need to do is to first alter the table, then update it.

ALTER TABLE YOUR_TABLE ADD DATETIME_COL DATETIME
ALTER TABLE YOUR_TABLE ADD DATE_COL DATE

UPDATE YOUR_TABLE 
SET 
  DATE_COL = CAST(NVCHAR_DATE AS DATE), 
  DATETIME_COL = CAST(NVCHAR_DATE AS DATETIME)

However, storing the date as a separate column seems a bit redundant. Maybe a computed column would be a better choice:

ALTER TABLE YOUR_TABLE ADD COMNPUTED_DATE_COL AS CAST(DATETIME_COL AS DATE)

See this SQL Fiddle

Upvotes: 0

Related Questions