How to add 6 months to a given date in SQL-CodePudding

I want to create a new date column by taking a date from previous column and then adding 6 months to it.

My data fields looks like this,

I want to create another column to it by taking index date then adding 6months to that date for each patients in my data. I tried using the following query but it's giving me an error.

select PATIENT_ID, INDEX_DATE, DATE_ADD(INDEX_DATE, INTERVAL   6 MONTH) as Ref_Index
FROM table;

Ref_Index is the new column I'm trying to create.

Is there any other way to apply the mentioned logic?

CodePudding user response：

Simply remove the from the DATE_ADD function.

select PATIENT_ID, INDEX_DATE, DATE_ADD(INDEX_DATE, INTERVAL 6 MONTH) as Ref_Index
FROM table;

In the change you're using T-SQL, the function parameters are slightly different.

select PATIENT_ID, INDEX_DATE, DATE_ADD(month, 6, INDEX_DATE) as Ref_Index
FROM table;

CodePudding user response：

Simply add the interval:

SELECT
  patient_id,
  index_date, 
  index_date   INTERVAL '6 MONTHS' AS ref_index
FROM table;

https://docs.snowflake.com/en/sql-reference/data-types-datetime.html#interval-constants

CodePudding user response：

In Snowflake you can also use ADD_MONTHS function to achieve the same:

select add_months(INDEX_DATE, 6) as ref_index from table;