DC_Var2SQL with date

[bamunz@earthlink.net]

cSQL := 'SELECT * FROM customer WHERE next_due >= ' + DC_Var2SQL( Date() )

Returns SELECT * FROM customer WHERE next_due >= { d '2019-10-16' }

I am on Build 267.

[rdonnay]

What SQL server are you using?

I originally wrote this for compatability with Microsoft SQL server but only recently discovered that ADS
does not support that date format.

Here is the source.

Code: Select all

FUNCTION DC_Var2SQL( xVal )

LOCAL cValtype := Valtype(xVal), cYear, cMonth, cDay

IF cValtype == 'C'
   IF "'" $ xVal
      return "'" + strtran(xVal, "'", "''") + "'"
   ENDIF
   return "'" + xVal + "'"
ELSEIF cValtype == 'D'
   IF Empty(xVal)
      xVal := Ctod('01/01/1900')
   ENDIF
   cYear := Alltrim(Str(Year(xVal)))
   cMonth := Strtran(Str(Month(xVal),2,0),' ','0')
   cDay := Strtran(Str(Day(xVal),2,0),' ','0')
   RETURN "'" + cYear + '-' + cMonth + '-' + cDay + "'"
   //{ d '2004-01-01' }
ELSEIF cValtype == 'N'
   RETURN Ltrim(Str(xVal))
ELSEIF cValtype == 'L'
   IF xVal
     RETURN 'TRUE'
   ENDIF
   RETURN 'FALSE'
ELSEIF cValtype == 'U'
   RETURN 'NULL'
ENDIF

RETURN "'" + Trim(DC_XtoC(xVal)) + "'"

I recommend that you rename it to MyVar2Sql() and compile it into your application.
This will work with ADS.

[bamunz@earthlink.net]

Forgot that detail, I am using Postgres with the pgdbe.

[rdonnay]

I'm not sure what date format is supported by PostGreSQL but there are a lot of "so called" standards that are unsupported.

[bamunz@earthlink.net]

I copied the code renamed it to My_Var2SQL and it works fine.