CODEWITHSUNDEEP
sqlsql-server
Aashu
Aashu

Reputation: 11

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

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?

Upvotes: 0

Views: 41

Answers (1)

marc_s
marc_s

Reputation: 755043

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

Upvotes: 3

Related Questions