I created a new column [LastLoginDate-NoTime]
with the data type Date
. I already have another column [LastLoginDate]
that is of Datetime
datatype.
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