Here is my initial test table

I need to run an update in that table so the column IdDimFecCorte shows the date (as an type integer) of the last day of the month based on the date from column FechaRegistro . As you can see, records 1,2,3,4 already satisfy this requirement but I require to run it retrospectively (for example with records 5,6,7)

My desired output should be

db<>fiddle

CodePudding user response：

Maybe you want to do this.

select IdRecord,FechaRegistro, CAST(REPLACE(EOMONTH(FechaRegistro),'-','') as INT) IdDimFecCorte from dbo.fact1Desired

if you want to update all.

UPDATE dbo.fact1Desired
    SET IdDimFecCorte = CAST(REPLACE(EOMONTH(dbo.fact1Desired.FechaRegistro),'-','') as INT)

If you want to have it automatic while adding.

INSERT dbo.fact1Desired(IdRecord,FechaRegistro,IdDimFecCorte)
VALUES (1, '20220425 23:45:00.000', CAST(REPLACE(EOMONTH('20220425 23:45:00.000'),'-','') as INT)),
       (2, '20220424 18:07:00.000', CAST(REPLACE(EOMONTH('20220424 18:07:00.000'),'-','') as INT)),
       (3, '20220310 19:04:00.000', CAST(REPLACE(EOMONTH('20220310 19:04:00.000'),'-','') as INT)),
       (4, '20220322 16:55:00.000', CAST(REPLACE(EOMONTH('20220322 16:55:00.000'),'-','') as INT)),
       (5, '20220210 22:06:00.000', CAST(REPLACE(EOMONTH('20220210 22:06:00.000'),'-','') as INT)),--
       (6, '20220214 02:06:00.000', CAST(REPLACE(EOMONTH('20220214 02:06:00.000'),'-','') as INT)),--
       (7, '20220130 21:55:00.000', CAST(REPLACE(EOMONTH('20220130 21:55:00.000'),'-','') as INT))--
;