AnsweredAssumed Answered

ExecuteSQL between dates

Question asked by PaulWebb on Oct 31, 2012
Latest reply on Oct 31, 2012 by PaulWebb

I have the following that is working to give me a list of dates (notice first part of WHERE is commented out). I am trying to change it so it gives me a list of records between two dates.

 

SR_Create_Date - calculation field to pull the date from a timestamp field using GetAsDate ( text )

$startdate - (Get ( CurrentDate) - DayOfWeek (Get ( CurrentDate )) - 6 )

$enddate - Get ( CurrentDate ) - DayOfWeek (Get (CurrentDate ))

 

I used SeedCodes SQL Eplorer to get as far as I did below. I've tried everything I could think of or find online. Need some direction on this one.

 

Thanks for looking.

 

// ------------ BEGIN EXECUTESQL BLOCK ------------

Let ( [

ReturnSub = " " ; // We need to swap out carriage returns in your results with a different character, so specify the character here.   is the default.

SQLResult = ExecuteSQL (

// ------------ BEGIN QUERY ------------

"SELECT a."SR_Create_Date"

FROM "SR" a

WHERE /* a."SR_Create_Date" >= '$startdate' AND */ a."SR_Create_Date" <= '$enddate'

ORDER BY a."SR_Create_Date" ASC" ;

// ------------ END QUERY ------ ------

// ------------ BEGIN FIELD AND ROW SEPARATORS ------------

" " ; "|*|"

// ------------ END FIELD AND ROW SEPARATORS ------------

) ] ;

// ------------ BEGIN CARIAGE RETURN SUBSTITUTIONS ------------

Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ] )

// ------------ END CARIAGE RETURN SUBSTITUTIONS ------------

)

// ------------ END EXECUTESQL BLOCK ------------

Outcomes