Home > Enterprise >  Copying data from one column to another in the same table sets data to null in original column
Copying data from one column to another in the same table sets data to null in original column

Time:09-29

I created a new column [LastLoginDate-NoTime] with the data type Date. I already have another column [LastLoginDate] that is of Datetime datatype.

Columns with the values

I am trying to copy values from the LastLoginDate column to the LastLoginDate-NoTime column using this query:

UPDATE [dbo].[SapUsersExt] 
SET [LastLoginDate] = [LastLoginDate-NoTime]

But the problem I am having is that when I execute this query, it sets the data to null in the original column.

Screenshot: Error

I am also trying to convert the data from the LastLoginDate to just date format in the new column LastLoginDate-NoTime so that I can use it in my application. How would I do that?

CodePudding user response:

I am trying to copy values from the LastLoginDate column to the LastLoginDate-NoTime column using this query

In that case, you're doing it exactly backwards - you should use this SQL instead:

UPDATE [dbo].[SapUsersExt] 
SET [LastLoginDate-NoTime] = [LastLoginDate] 

The first column - right after the SET - is the target column into which your values will be written.

The second column, after the = symbol, is where the data comes from (column or expression).

You had it backwards - setting the column with the actual values, to all NULL ....

This of course only works for a "one time" update - this will not keep your columns in sync over time, when new data is being inserted. For such a case, you'd need a computed column

ALTER TABLE dbo.SapUsersExt
    ADD LastLoginDateOnly AS CAST(LastLoginDate AS DATE) PERSISTED;

or a trigger.

Or maybe, you don't even really need to actually store that date-only value - just use

SELECT
    CAST(LastLoginDate AS DATE), 
    .......

if you need to date-only value from LastLoginDate

  • Related