If Statement with Date Format

[Anonymous]

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?

[sunny_talwar · Accepted solution]

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