How to get current date in sql

rbuser

?Hello,
I need to provide the end user with daily,weekly and monthly reports.But
in weeky and monthly reports i am having problem that in query wizard it
is not possible to assign current system date to the auto search dialog
box.How it is possible to assign current date in sql in RB 7.



--- posted by geoForum on http://delphi.newswhat.com

nicocizik

Hi Lucy,

In RAP the methods "CurrentDate, CurrentDateTime, and CurrentTime" are
available to use where ever you need.

Depending on which database you are using the standard SQL command to
retrieve the current date is CURRENT_DATE. You can try entering this into a
calculated field and see if your database supports it.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hello,
Thanks a lot for your help but i think i was not clear with my
question . My problem is that when creating query in query designer
we provide the default values in the search expression and get the
corresponding SQL.
Now in order to get weekly reports i have to provide user the autosearch
dialog box in which he can give the station name.I also have to provide
the range of saledate for one week by default.User need not to enter the
dates The problem is that when i am specifying the search expression for
sale date as 'currentdate' or 'current_date' it is giving sql error
because there is no expression found for sale date in sql.
I have written the RAP code but it is not working .The code is

procedure TUIreport.ppReportUIonAutoSearchDialogClose(Sender: TObject);
var qurdate:tquery;
begin
qurdate:=Tquery.create(Self);
qurdate.sql.clear;
qurdate.sql.add('SELECT
t.ACTUALVALUE,t.FREQUENCY,t.IPNO,t.IPSTARTTIME,t.SCHEDULEVALUE,');
qurdate.sql.add
('t.STATION_ID,t.STEPFREQ,t.SURVEYDATE,t.UI,t.UIAMOUNT,t.UIRATE,gm.LOCATIO
N,');
qurdate.sql.add
('gm.STATION_INITIAL,gm.STATION_NAME,gm.TOTAL_INVESTMENT,t.COMPLETEDATA');
qurdate.sql.add('FROM TBSTATIONUIDATA t,GENCO_MASTER gm');
qurdate.sql.add('WHERE (gm.STATION_ID = t.STATION_ID) AND ((
gm.STATION_NAME ='+ Report.AutoSearchFields[0].Value+'))');
qurdate.sql.add(' and t.surveydate between to_date('+datetostr
(currentdate-50)+')+'and to_date('+datetostr(currentdate)+')');
qurdate.sql.add('ORDER BY t.SURVEYDATE');
qurdate.sql.active;
end;

Can you please provide me with the example of how to specify current date
in the search expression of query wizard in RB7 . I have to do all my
coding in RB itself.
Sorry for the trouble.



--- posted by geoForum on http://delphi.newswhat.com

nicocizik

Hi Lucy,


Why? For some of the things you are trying to accomplish below, you will
need to create a passthru function in Delphi. For instance, the TQuery
object is not available in RAP. If you want to change the SQL of a DataView
you created in DADE, consider using the TdaSQLBuilder object (available in
RB 9.0x). See the topic in the ReportBuilder help for more information and
example code on how it can be used in your situation.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Lucy. You are making this way too complicated. Setup the entire SQL in
DADE using the data tab. Then, instead of modifying the entire SQL
statement, modify only the autosearch expressions using something like:

Report.AutoSearchFields[1].SearchExpression
:=QuotedStr(FormatDateTime('YYYY-MM-DD',currentdate-50));
Report.AutoSearchFields[2].SearchExpression
:=QuotedStr(FormatDateTime('YYYY-MM-DD',currentdate));

David Miller.

rbuser

Hello,
I may be sounding very stupid but i am still not able to assign the
current date to the autosearch dialog box at runtime. I created a pass
through function for Createautosearchfields and also tried the solution
Report.AutoSearchFields[1].SearchExpression
:=QuotedStr(FormatDateTime('YYYY-MM-DD',currentdate-50));
Report.AutoSearchFields[2].SearchExpression
:=QuotedStr(FormatDateTime('YYYY-MM-DD',currentdate));
but neither is working.
In the above solution i am getting error 'quotedstr undefined'.I had
tried this code in Oncreate , after autosearchdialogcreate and
ongetautosearch events but was completely unsuccessful.
I am using Delphi 7 and RB7 . Please help me how can i get current
system date in the query wizard or in the auto search dialog box.
Thanks for your help and sorry to trouble you again.
Lucy



--- posted by geoForum on http://delphi.newswhat.com

rbuser

Hi Lucy. You might want to check that error message again. I suspect you
are getting "Undeclared identifier" rather than "undefined" as you claimed.
It is important to pay close attention to error messages in order to know
what is wrong. Otherwise, the error might just as well say, "Error: try
something else." :-)

The QuotedStr function is a Delphi function that is not exposed to RAP. You
would need to add it to your pass through functions. It simply adds quotes
to the beginning and end of the string, and changes all single quotes to two
single quotes.

However, instead of using the QuotedStr function, you could just specify it
as something like:

Report.AutoSearchFields[2].SearchExpression:=
''''+FormatDateTime('YYYY-MM-DD',currentdate)+'''';

That's four single quotes in a row to make a single quote prefixed to the
expression. Try a line like this and I suspect it will compile just fine
for you.

Of course, this is all database dependent. I don't know what database you
use, so it may not take this. My database expects SQL dates to be in the
format of 'YYYY-MM-DD' and the date must be enclosed within single quote
marks. You need to consult with your database documentation to see how it
expects dates to be formatted. I think Oracle has a to_date function which
takes quoted dates in the format of 'DD-MMM-YYYY'. For example:

WHERE order_date between to_date('01-JAN-2004') AND to_date('31-DEC-2004')

You should try some tests where you specify the date directly in the right
format to make sure your database accepts what you are doing. After you
have that working right, then start coding to format the dates from your
database fields properly. Use ShowMessage in RAP to examine what the date
looks like after it has been parsed, e.g.,
"ShowMessage(Report.AutoSearchFields[2].SearchExpression);" After you get
that working right, either comment out the ShowMessage line or delete it for
the final code.

David Miller.

rbuser

Hi Lucy, I understand exactly what your problem is.

Follow this steps:

1. Declare two variables (i.e.IniDate, FinDate) of type TDateTime and three
(3) variables refering to a date decomposition (i.e. cYear, cMonth, cDay) in
this example the prefix "c" means current, this variables should of type
Integer.

2. Decode the CurrentDate using the DecodeDate RAP function. For example:

DecodeDate(CurrentDate, iYear, iMonth, iDay);

3. Encode the dates you need to be assigned to your AutoSearchFields using
the EncodeDate RAP function to your date variables. For example:

IniDate := EncodeDate(cYear, cMonth, cDay - n); (n = Days to be
substracted from the current date.)
FinDate := EncodeDate(cYear, cMonth, cDay);

After this all you need to do is assign the variables to your
AutoSearchFields though your RAP coding.

This should be done this way because encoding the date is the only way you
have to get the system default date format.

I hope this could be useful to you.

Regards,

Fabio.