0

I have a df

Begin_Date  Last_date
2010-03-30  NULL
2010-04-07  2010-07-10

I want to calculate a column effective last date such that if there is null in last_date column, then I should get today's date (NULL in last_date column means the last date is not yet reached, still active)

Begin_Date  Last_date   Effective_date
2010-03-30  NULL           2020-09-08  #Today's date
2010-04-07  2010-07-10     2010-07-10

Code:

ALTER TABLE df ADD Effective_date AS
     COALESCE(Last_date, GETDATE()) ;

Output I am getting:

Begin_Date  Last_date   Effective_date
2010-03-30  NULL           2020-09-08 00:00:00:000 Today's date
2010-04-07  2010-07-10     2010-07-10

How to remove the 00:00:00:000 part from date?

noob
  • 3,601
  • 6
  • 27
  • 73

1 Answers1

2

Convert to a date:

ALTER TABLE random_table_1 ADD effective_end_date AS
     CONVERT(date, COALESCE(End_dt, GETDATE())) ;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • can u please change df names etc acc to what i have done...this is to protect privacy – noob Aug 10 '20 at 13:40
  • 3
    @noob: names like `df` or `effective_end_date` do not convey any private information. I bet nearly all databases out there have at least one table that has a column named `effective_end_date` –  Aug 10 '20 at 13:42
  • @noob, while the previous comment is true, the time to think about privacy is before you post. Once you've put data here, it's preserved (and publicly visible) in your edit history. – Eric Brandt Aug 10 '20 at 13:48
  • 3
    And the original version is still available anyway if you look at the [edit history](https://stackoverflow.com/revisions/63341347/1), @noob . But a column called `effective_end_date` breaks no privacy laws, I can assure you. – Thom A Aug 10 '20 at 13:48