Comparing Date and Strings

[JimReiley]

How do I compare a date string in yyyymmdd format to the current date?

 

[Linda84]

If you're using Pervasive.SQL 9.5, you can use the new scalar function DATEDIFF. Check out the scalar function docs in the SQL Engine Reference at

http://www.pervasive.com/library/docs/psql/950/sqlref/sqlref-06-4.html

DATEADD, DATENAME, and DATEPART are also new, as well as a new datatype called DATETIME.


Linda
Pervasive Software

 

[JimReiley]

I am using 9.5. I am entering the following

select * from "adheader"
where DATEDIFF(day,LastInsertDate,Current_Date())>0

I get

[LNA][Pervasive][ODBC Engine Interface]Expression evaluation error.

2ndly, the LastInsertDate field is in yyyymmdd string format - no hypens. Is this going to work?

Thanks

 

[mirtheil]

You're going to need to convert (or mask) the field to a date so you can use data functions.
So your select should look like:

select * from "adheader"
  where DATEDIFF(day, convert(left(LastInsertDate,4) + '-' + substring(LastInsertDate,5,2) + '-' + right(LastInsertDate,2) , sql_date),Current_Date())>0

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[mirtheil]

Sorry, bad term in there. "Mask" was an old term.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[JimReiley]

Here is what I have in the sql statement"
L := form1.tbCLient.FieldByName('ClientNumber').asString;
form1.qAds.SQL.Clear;
form1.PvSQLSession1.ServerName := 'Btrieve';
form1.qAds.DatabaseName := 'LPV8OE';
form1.qAds.SQL.Add ('select clientnumber, pubnumber, adnumber,entryyear,regarding, lastinsertdate from adheader');
form1.qAds.SQL.Add (' where clientnumber = ' + quotedstr(L) + 'and');
form1.qAds.SQL.Add (' KBI<>' + quotedstr('Y') + 'and InvoiceNumber=' + quotedstr('') + 'and' );
form1.qAds.SQL.Add (' DateDiff(day,convert(left(LastInsertDate,4) + '-' + substring(LastInsertDate,5,2) + '-' + right(LastInsertDate,2), sql_date),Current_Date() )>0';
form1.qAds.SQL.Add ('order by pubnumber,entryyear,adnumber');
form1.qAds.SQL.Text;
form1.qAds.Open;

The problem is the compiler doesn't like the 2nd dash:
[Error] Client.pas(405): Operator not applicable to this operand type

Is there a line length limit and if so how do I continue on the next line? Thanks

 

[mirtheil]

You're getting that error because Delphi treats single quotes as the string delimiter. You need to escape the single quotes. It should look like:

  form1.qAds.SQL.Add ('  DateDiff(day,convert(left(LastInsertDate,4) + ''-'' + substring(LastInsertDate,5,2) + ''-'' + right(LastInsertDate,2), sql_date),Current_Date() )>0');
  form1.qAds.SQL.Add (' order by pubnumber,entryyear,adnumber');

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com