SQL Selection problem...

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...
LVL 2
lortegaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
kretzschmarConnect With a Mentor Commented:
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
 
kretzschmarCommented:
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
 
kretzschmarCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
lortegaAuthor Commented:
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
 
kretzschmarCommented:
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
 
qdyoungCommented:
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
 
kretzschmarCommented:
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
 
kretzschmarCommented:
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
 
lortegaAuthor Commented:
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
 
lortegaAuthor Commented:
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
 
kretzschmarCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.