CODEWITHSUNDEEP
sqlpostgresql
Burak
Burak

Reputation: 83

Making new column based on date with variable year

As topic is saying, it all should apply to dates with variable year.

I would like to make a new column (called which_part for example) saying if the date (in this case date is in "sil.sales_invoice_date") is between 1st of january to 30th of june - which should say H1 (half 1) and else is H2 (between 1st of july to end of the year).

I've tried doing something like that with convert

SELECT
sil.sales_invoice_date AS "Distributor Invoice Date",
CONVERT(sil.sales_invoice_date, @year + '-01-01') AS [start year],
CONVERT(sil.sales_invoice_date, @year + '-06-30') AS [end year],

It doesn't work at all and I can't write [half year] in there.

I've also tried to do it by using

case sil.sales_invoice_date between

But I don't really know how to format it to work with variable year.

Upvotes: 0

Views: 184

Answers (1)

Ronio
Ronio

Reputation: 68

if you have date in format dd.mm.yyyy Try this

Case when substr(sil.sales_invoice_date,1,5) between '01.01' and '30.06' then 'H1'
else 'H2'
end as which_part;

Upvotes: 1

Related Questions