CODEWITHSUNDEEP
sql
Yogesh Govindan
Yogesh Govindan

Reputation: 381

How to add number to a date column with some predifined conditions in SQL?

In the below sql code i am trying to compare two column in which if column B is empty or zero then add the date column to column A else add the date column to column B. However i am not getting result as i am getting the following error:

SQL Error [42883]: ERROR: function day(real) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 107

Below is the sql code:

select distinct 
  (concat(concat(documentno,'_'),itemno)) inv_num
, (case when dis_day2 =0 
     then baselinedate +day(dis_day1) 
     else baselinedate +day(dis_day2) 
   end) z
, *
from close items
where doc_type='BB' 
  and clearingda<='2020-05-18' 
group by z 
limit 100

Upvotes: 1

Views: 187

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This code just does not make sense:

(case when dis_day2 = 0 then baselinedate +day(dis_day1) 
      else baselinedate +day(dis_day2) 
 end)

In databases that have a day function, it extracts the day-of-month field from a date or similar type. But you are comparing the value to 0 and the error message suggests that it is a real.

I suspect you want:

baselinedate + (case when dis_day2 = 0 then dis_day_1 else dis_day2 end) * interval '1 day'

This adds the number of days to the baseline date.

Upvotes: 1

Ken Lee
Ken Lee

Reputation: 8063

You may try using DATE_PART()

The DATE_PART() function extracts a subfield from a date or time value

For your case, please try date_part('day', source)

For example

 date_part('day', dis_day1)

Upvotes: 0

Related Questions