Solved

SQL Selection problem...

Posted on 1999-01-08
11
157 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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
 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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