If Statement with Date Format

Anonymous — Fri, 02 Mar 2018 20:31:43 GMT

I have a current field called CloseDate which is formatted as YYYY-MM-DD

I am trying to write the if statement in my load script below to categorize records into new quarters by CloseDate

if(Date(CloseDate) >= 1/2/2018 AND Date(CloseDate) <= 4/30/2018, 20191,

if(Date(CloseDate) >= 5/1/2018 AND Date(CloseDate) <= 7/31/2018, 20192,

if(Date(CloseDate) >= 8/1/2018 AND Date(CloseDate) <= 10/31/2018, 20193,

if(Date(CloseDate) >= 11/1/2018 AND Date(CloseDate) <= 1/31/2019, 20194, Null())))) as NewQuarter

I've also tried this with the date conditional in the same format as the CloseDate field

if(CloseDate >= 2018-02-01 AND CloseDate <= 2018-04-30, 20191,

if(CloseDate >= 2018-05-01 AND CloseDate <= 2018-07-31, 20192,

if(CloseDate >= 2018-08-01 AND CloseDate <= 2018-10-31, 20193,

if(CloseDate >= 2018-11-01 AND CloseDate <= 2019-01-31, 20194, Null())))) as NewQuarter

Netiher is working for me. Any suggestions?

Re: If Statement with Date Format

sunny_talwar — Fri, 02 Mar 2018 22:58:47 GMT

Try this may be

if(CloseDate >= MakeDate(2018, 2, 1) and CloseDate <= MakeDate(2018, 4, 30), 20191,

if(CloseDate >= MakeDate(2018, 5, 1) and CloseDate <= MakeDate(2018, 7, 31), 20192,

if(CloseDate >= MakeDate(2018, 8, 1) and CloseDate <= MakeDate(2018, 10, 31), 20193,

if(CloseDate >= MakeDate(2018, 11, 1) and CloseDate <= MakeDate(2019, 1, 31), 20194, Null())))) as NewQuarter