Search code examples
sql-servert-sqlfoxpro

Foxpro Between Clause

I am currently looking at foxpro queries, aka if SQL where drunk and I need help understanding how something works as I am completely new to foxpro.

SELECT keyfld, SUBSTR(CCB_SERIAL,11,2) AS a ;
FROM g:\mirror\DBFS\CCB ;
WHERE 
(BETWEEN({^2018-01-01},pl_st_dt, pl_end_dt) or 
BETWEEN({^2018-03-31},pl_st_dt,pl_end_dt))

My understanding of how to interpret the bottom 2 lines is

where
     pl_st_dt >= '2018-01-01' OR
     pl_end_dt >= '2018-01-01' OR
     pl_st_dt <= '2018-03-31' OR
     pl_end_dt <= '2018-03-31'

Is that correct?

I am trying to take a foxpro query and write the equivalent t-sql version.

Solution

  • Not quite.

    The BETWEEN there is equivalent to:

    ({^2018-01-01} BETWEEN pl_st_dt and pl_end_dt) or 
({^2018-03-31} BETWEEN pl_st_dt and pl_end_dt)

    So your equivalency would be:

    WHERE
  (pl_st_dt <= '2018-01-01' AND '2018-01-01' <= pl_end_dt)
  OR
  (pl_st_dt <= '2018-03-31' AND '2018-03-31' <= pl_end_dt)