date format issues

[galderman]

I'm having some trouble operating with "date" information in a VORTEX script. I have a query which works fine in "tsql", but I can't get it working in VORTEX (apparently because of some "quoting" issues?)

EXAMPLE:
tsql -d. "select count(*),tocid,itemnum from table where edition='xyz' AND artdate > 'begin of 2000-05-30'
AND artdate < 'end of 2000-05-30' group by tocid"

As I said, this works. In the VORTEX script, the
actual date of interest is a variable "$this_artdate".

<sql "select count(*),tocid,itemnum from table
WHERE edition = $this_edition
AND artdate > begin of $this_artdate
AND artdate < end of $this_artdate
group by tocid">

gives me a syntax error at "of"

<sql "select count(*),tocid,itemnum from table
WHERE edition = $this_edition
AND artdate > 'begin of $this_artdate'
AND artdate < 'end of $this_artdate'
group by tocid">

spews a bunch of errors:
SQLSetParam(,2,1,1,,,,) failed with -1 in the function paramtexis
SQLSetParam(,3,1,1,,,,) failed with -1 in the function paramtexis
followed by two copies of:
garbled time in the function month

I have searched the Thunderstone web space and these comment boards. I have found some clues, but still can't get it to work.

How can I get this to work?
Also, how can I simply get a timestamp integer into
a variable and deal with it as an integer?

[John]

You would need to get the entire string into a variable first, and use that in the SQL, e.g.

<$startdate=( 'begin of ' + $this_artdate)>
<$enddate=( 'end of ' + $this_artdate)>

<sql "select count(*),tocid,itemnum from table
WHERE edition = $this_edition
AND artdate > $startdate
AND artdate < $enddate
group by tocid">

You can convert the date to a long when selecting it, e.g.

<sql "select convert(artdate, 'long') artdate from table">
$artdate
</sql>