Solved

SQL Selection problem...

Posted on 1999-01-08
11
155 Views
Last Modified: 2010-04-06
How can I convert one of the fields format from an SQL query...

on DAO you can write:
Select Str$(myDatefield) as xdate from myTable...

or select part of an string using:
Select Left$(5,myDatefield) as xdate from myTable...


my real problem is this... I need to make an selection criteria that read from more than one database using UNION statement, and then select from xdata to ydate and from xtime to ytime, doing this you receive just the record that cover all the criteria so if you select more than one day the result is wrong...

example

start date  1/1/1999
end date    1/15/1999
start time  12:00:00
end time    15:00:00

with this selection the result doesn't show you the transactions made after 15:00:00 on all the days betwen 1/1/1999 to 1/15/1999...
0
Comment
Question by:lortega
  • 7
  • 3
11 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1355338
hi lortega,

it seems you should build your sql-statements at runtime, then you can use delphi-functions like copy(myDateField,1,5) instead Left$(5,MyDatefield)

and conversion are available

StrToDateTime('1/1/1999 12:00:00)
StrToDateTime('1/15/1999 15:00:00)

your statement could shown as

query1.sql.add(Select myDatefield as xdate from myTable where myDatefield between '+StrToDateTime('1/1/1999 12:00:00')+' and '+StrToDateTime('1/15/1999 15:00:00');

meikl
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1355339
hi Iortega,

sometimes i wrote silly statements,

in designmode

the propertys of query1 are filled additional as follows:

SQL = Select myDatefield as xdate from myTable where myDatefield between :StartTime and :EndTime

Params
-> StartTime - Typ Date
-> EndTime - Typ Date

now you can in Runtime use following codefragment

  query1.ParamByName('StartTime').Value := StrToDateTime('1/1/1999 12:00:00');
  query1.ParamByName('EndTime').Value := StrToDateTime('1/15/1999 15:00:00');
  query1.Open;

meikl
0
 
LVL 2

Author Comment

by:lortega
ID: 1355340
meikl, I try this lines from Database Explorer and the result was an error 'Capability not supported.'

Select copy(P19990108."Key",1,5) as XTime From P19990108
Select StrToDateTime(P19990108."Time") as XTime From P19990108


0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1355341
hi lortega,

sorry, my mistake as i said, in a sql-statement cannot be such as copy... or strdate..., because the statement is passthrough to the database-engine(in this case access i think) and must have the right syntax.

try my second comment, really sorry

meikl
0
 

Expert Comment

by:qdyoung
ID: 1355342
You haven't said what database you use. If you use SQL-based database, such as Oracle, you statements
  Select copy(P19990108."Key",1,5) as XTime From P19990108
  Select StrToDateTime(P19990108."Time") as XTime From P19990108
can write it this way:
  Select SubStr(Key,1,5) XTime From P19990108
  Select To_Date(Time) XTime From P19990108


0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 1355343
hi qdyoung,

your first statement causes an error if field not a CharField.
your second oracle statement causes an error if the field a Datefield. and i think the To_Date-statement awaits a mask as second parameter. if it oracle for this question the complete statement is:

  Select Time as XTime From P19990108 where Time between To_Date('01/01/1999 15:00:00','MM/DD/YYYY HH24:MI:SS') and To_Date('01/15/1999 15:00:00','MM/DD/YYYY HH24:MI:SS')

there converting function in oracle that converts a DSateField to a String and then you can use SubStr, but i haven't it in my head now.

meikl
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1355344
hi iortega,

i have configured out it in access (German Version) with Database Nordwind table Bestellungen, Field Bestelldatum (Typ Date)

in designmode
the propertys of query1 are filled additional as follows:

SQL = select Left(CStr(bestelldatum),5) as XTime from bestellungen where bestelldatum between :StartDate and :EndDate

Params (available after input SQL)
    -> StartTime - Typ DateTime
    -> EndTime - Typ DateTime

this fragment in the SQL-Statement copies the first five Characters from the Datefield and name this field XTime
Left(CStr(bestelldatum),5) as XTime

now i have four DateTimePickers placed on the form (startdate, starttime, enddate, endtime), a datasource connected to the query and a dbgrid connected to the Datasource

and this is the code, that do the work
 
procedure TForm1.Button1Click(Sender: TObject);
begin
  query1.Close;
  query1.ParamByName('StartDate').AsDateTime := Int(DateTimePicker1.Date) + Frac(DateTimePicker3.Time);
  query1.ParamByName('EndDate').AsDateTime := Int(DateTimePicker2.Date) + Frac(DateTimePicker4.Time);
  query1.open;
end;

meikl
0
 
LVL 2

Author Comment

by:lortega
ID: 1355345
I'am usinG pure BDE with Paradox 5 tables, that mean that I don't have Left$, Copy, To_Date, StrToDateTime, or SubStr...

the solution is change the database structure, create a new field of Timestamp type and record the date and time on this field too...

yep, I don't liked but is the way that should it be from the start...


thanks a lot,
luis
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 30 total points
ID: 1355346
Hi Iortega,

in appandix to my last comment,
here is the SQL-Statement for Paradox (works with DateTime and DateFields)

select SubString(Cast(YourDateField as Char(20)) From 1 For 5) as XTime from YourTable where YourDateField between :StartDate and :EndDate


meikl
0
 
LVL 2

Author Comment

by:lortega
ID: 1355347
I think you are rigth and excuse me for the last rejection, maibe I misspell something when I tried... now, where I can get more information about sql for BDE, Isaw that some words are allowed in Oracle, some others in MS DAO, and some others in BDE... how can I get information about BDE SQL reserved functions?

thanks,
luis
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 1355348
Hi luis,

the bde only passthrough the SQL-Statement to the databasedriver, the SQL-Syntax depends on the database-system at the end, and there are many dialectics here in case of TypConversions there is no standard. It gives an Standard in SQL to handle the lowlevel procedures (select, update, insert ...) thats ANSI-SQL or SQL-92, mostly Database-Systems are compatible to this Standard (except MS-Products not fully implemented), but have own extended functionality.

You can get most Information by ANSI-SQL and the differences by the database-system.

Some Information about SQL-92 you find by OCELOT
http://ourworld.compuserve.com/homepages/OCELOTSQL/index.htm

meikl
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now