Solved

SQL Selection problem...

Posted on 1999-01-08
11
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

729 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