Page 1 of 1
DC_Var2SQL with date
Posted: Wed Oct 16, 2019 8:33 am
by 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.
Re: DC_Var2SQL with date
Posted: Mon Oct 21, 2019 5:43 pm
by 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.
Re: DC_Var2SQL with date
Posted: Wed Oct 23, 2019 6:50 am
by bamunz@earthlink.net
Forgot that detail, I am using Postgres with the pgdbe.
Re: DC_Var2SQL with date
Posted: Thu Oct 24, 2019 12:04 pm
by rdonnay
I'm not sure what date format is supported by PostGreSQL but there are a lot of "so called" standards that are unsupported.
Re: DC_Var2SQL with date
Posted: Fri Oct 25, 2019 10:14 am
by bamunz@earthlink.net
I copied the code renamed it to My_Var2SQL and it works fine.