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.
DC_Var2SQL with date
Re: DC_Var2SQL with date
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.
I recommend that you rename it to MyVar2Sql() and compile it into your application.
This will work with ADS.
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.
The eXpress train is coming - and it has more cars.
-
- Posts: 23
- Joined: Fri Apr 17, 2015 11:09 am
Re: DC_Var2SQL with date
Forgot that detail, I am using Postgres with the pgdbe.
Re: DC_Var2SQL with date
I'm not sure what date format is supported by PostGreSQL but there are a lot of "so called" standards that are unsupported.
The eXpress train is coming - and it has more cars.
-
- Posts: 23
- Joined: Fri Apr 17, 2015 11:09 am
Re: DC_Var2SQL with date
I copied the code renamed it to My_Var2SQL and it works fine.