DC_Var2SQL with date

This forum is for eXpress++ general support.
Post Reply
Message
Author
bamunz@earthlink.net
Posts: 23
Joined: Fri Apr 17, 2015 11:09 am

DC_Var2SQL with date

#1 Post 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.

User avatar
rdonnay
Site Admin
Posts: 4762
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: DC_Var2SQL with date

#2 Post 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.
The eXpress train is coming - and it has more cars.

bamunz@earthlink.net
Posts: 23
Joined: Fri Apr 17, 2015 11:09 am

Re: DC_Var2SQL with date

#3 Post by bamunz@earthlink.net »

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

User avatar
rdonnay
Site Admin
Posts: 4762
Joined: Wed Jan 27, 2010 6:58 pm
Location: Boise, Idaho USA
Contact:

Re: DC_Var2SQL with date

#4 Post 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.
The eXpress train is coming - and it has more cars.

bamunz@earthlink.net
Posts: 23
Joined: Fri Apr 17, 2015 11:09 am

Re: DC_Var2SQL with date

#5 Post by bamunz@earthlink.net »

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

Post Reply