DateTime in Query. How?
Tasso Kaup
following doesn't work. It doesn't seem to like the formatting. (Illegal
token 10)
SELECT * FROM table
WHERE Datetime > 12/05/99 10:05:10
When looking for examples in books and on the Web I only find those that
use either the date or the time. But the field in my table is a
datetime one which uses both in one and needs to be queried as such.
Please help.
Thanks
Tasso
Eileen Ewen
What database are you using ?
if you can build up the SQL in an application allow the user to select a
date from a date time picker or create a datetimepicker variable and set the
date and time by
var
TDateTimePicker : MyDateTime;
then in body of code do
MyDateTime := TDateTimePicker.Create(Application);
MyDateTime.date := StrToDate('02/12/98');
MyDateTime.Time := StrToTime('00:00:00'); // or whatever time you
want
then for build the SQL using a parameterised query or build up the SQL and
then execute it.
// remember to free MyDateTime
SELECT *
FROM table
WHERE table.datefield > :datetime
PARADOX offers the following help:
"Local SQL supports the EXTRACT() function for isolating a single numeric
field from a date/time field on retrieval using the following syntax:
EXTRACT (extract_field FROM field_name)
For example, the following statement extracts the year value from a DATE
field:
SELECT EXTRACT(YEAR FROM HIRE_DATE)
FROM EMPLOYEE
You can also extract MONTH, DAY, HOUR, MINUTE, and SECOND using this
function."
Eileen Ewen
Ex RGU Student
Tasso Kaup wrote in message <3768BEFC...@dei-ltd.co.uk>...
Pierre du Parte
#mm/dd/yyyy#"
--
Pierre
http://delphidao.finalfiler.com
http://delphiotc.finalfiler.com
http://www.finalfiler.com
Tasso Kaup <ka...@dei-ltd.co.uk> wrote in message
news:3768BEFC...@dei-ltd.co.uk...
> Can someone please tell me how to use datetime in a where clause. The
> following doesn't work. It doesn't seem to like the formatting. (Illegal
> token 10)
>
> SELECT * FROM table
> WHERE Datetime > 12/05/99 10:05:10
>
Tasso Kaup
Thanks for your help. I haven't tried the parameterised version yet but the
EXTRACT ones work fine with the SELECT statement. However, I am still stuck. I
am trying to copy values starting from a given date and time from one table to a
new one. I could create a query and then copy the result into the new one field
by field but I was rather hoping to use a more efficient method like the
following:
INSERT INTO TABLE
WHERE DATETIME = (SELECT * FROM OtherTable WHERE DateTime > :DateTime)
According to SQL Literature this should work but whenever I run queries like
this I get an error that WHERE is an illegal keyword.
I am using a Paradox database
Tasso
Eileen, are you the same that did the Computing Course at RGU about two/three
years ago?
> Hi Tasso
>
> What database are you using ?
>
> if you can build up the SQL in an application allow the user to select a
> date from a date time picker or create a datetimepicker variable and set the
> date and time by
>
> var
> TDateTimePicker : MyDateTime;
>
> then in body of code do
>
> MyDateTime := TDateTimePicker.Create(Application);
> MyDateTime.date := StrToDate('02/12/98');
> MyDateTime.Time := StrToTime('00:00:00'); // or whatever time you
> want
>
> then for build the SQL using a parameterised query or build up the SQL and
> then execute it.
>
> // remember to free MyDateTime
>
> SELECT *
> FROM table
> WHERE table.datefield > :datetime
>
> PARADOX offers the following help:
>
> "Local SQL supports the EXTRACT() function for isolating a single numeric
> field from a date/time field on retrieval using the following syntax:
>
> EXTRACT (extract_field FROM field_name)
>
> For example, the following statement extracts the year value from a DATE
> field:
>
> SELECT EXTRACT(YEAR FROM HIRE_DATE)
> FROM EMPLOYEE
>
> You can also extract MONTH, DAY, HOUR, MINUTE, and SECOND using this
> function."
>
> Eileen Ewen
> Ex RGU Student
>
> Tasso Kaup wrote in message <3768BEFC...@dei-ltd.co.uk>...
Jeff Overcash (TeamB)
comparing a DataTime to a whole row. Try changing it to
INSERT INTO TABLE
WHERE DATETIME in (SELECT DateTime FROM OtherTable WHERE DateTime >
:DateTime)
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
If there is somebody up there could they throw me down a line. Just a
little helping hand just a little understanding. Just some answers to the
questions that surround me now. If there's somebody up there could
they throw me down a line. (Fish)
--
Tasso Kaup
I just tried this but got the same error message (Invalid keyword WHERE). I tested it
with different fields but always get the same error.
Tasso
Steve Koterski
>Can someone please tell me how to use datetime in a where clause. The
>following doesn't work. It doesn't seem to like the formatting. (Illegal
>token 10)
>
>SELECT * FROM table
>WHERE Datetime > 12/05/99 10:05:10
>
>When looking for examples in books and on the Web I only find those that
>use either the date or the time. But the field in my table is a
>datetime one which uses both in one and needs to be queried as such.
>Please help.
In local SQL (what the BDE uses for Paradox, dBASE, and FoxPro tables),
DATE, TIMESTAMP, and TIME literals must be enclosed in quotation marks
(single or double). This prevents 12/05/99 from being mistaken from the
arithmetic operation 12 divided by five divided by 99. You should also
include the am/pm designator with the time portion of the TIMESTAMP
literal.
SELECT *
FROM table
WHERE Datetime > "12/05/99 10:05:10am"
Of course, if you pass the value to the statement as a parameter, quotation
marks need not be added.
Local SQL is documented in the online help file LOCALSQL.HLP, found in the
main BDE directory. This help file is a language reference of the local SQL
implementation (subset) of SQL-92. The copy of this file that came with BDE
4.x (and earlier versions) was outdated and has since been rewritten.
Updated copies will have the topic "Unsupported language" in the index (and
lack of this topic indicates a pre-update copy). I can e-mail you a copy of
the updated file if you need and desire it.
//////////////////////////////////////////////////////////////////////////
Steve Koterski "My problem lies in reconciling my gross
Technical Publications habits with my net income."
INPRISE Corporation -- Errol Flynn (1909-1959)
http://www.borland.com/delphi
Steve Koterski
>Thanks for your help. I haven't tried the parameterised version yet but the
>EXTRACT ones work fine with the SELECT statement. However, I am still stuck. I
>am trying to copy values starting from a given date and time from one table to a
>new one. I could create a query and then copy the result into the new one field
>by field but I was rather hoping to use a more efficient method like the
>following:
>
> INSERT INTO TABLE
> WHERE DATETIME = (SELECT * FROM OtherTable WHERE DateTime > :DateTime)
>
>According to SQL Literature this should work but whenever I run queries like
>this I get an error that WHERE is an illegal keyword.
>
>I am using a Paradox database
What "SQL literature"? I have never seen an SQL reference that indicated a
WHERE clause is allowed for an INSERT statement. At any rate, I can say
that local SQL definitely does not support this.
From the looks of your statement, allow you appear to have been trying to
do with that WHERE clause was filter the result set of the SELECT subquery.
In that case, the WHERE clause is not needed for the INSERT statement, only
for the subquery.
INSERT INTO OneTable
SELECT *
FROM OtherTable
WHERE (DateTimeColumn > :DateTimeParam)
Note that in local SQL, a SELECT subquery for an INSERT statement should
not be enclosed in parentheses.
Also note that for this statement to work, the structures of the two tables
must be the same. If they are not, you would need to explicitly list the
columns in the columns list of the INSERT statement and the SELECT clause
of the subquery.
Jeff Overcash (TeamB)
syntax without considering the semantics of it at all. My fault :).
Tasso Kaup wrote:
>
> Hi Jeff
>
> I just tried this but got the same error message (Invalid keyword WHERE). I tested it
> with different fields but always get the same error.
>
> Tasso
>
--
Tasso Kaup
PC with internet access is in the training room, thus the delay.
The literature was SQL for Dummies.
Tasso
Eileen Ewen
Here's another option:
add SysUtils to uses clause (if not already there ie for Console Apps)
Procedure BuildSQL(var StrWhere:string);
begin
ShortDateFormat := 'mm/dd/yyyy'; // to american way -- needs
SysUtils
StrWhere := 'WHERE (D.StartDate <= "' + DateToStr(Now+7) + '") AND
(D.EndDate >= "'+ DateToStr(now)+'")';
// string = 'Where (StartDate <= (todays date + 7 days)) AND (EndDate
>= todays date)
// Table D has fields for StartDate and EndDate
end;
In short :
1. Set the date format to the American mm/dd/yyyy before your build the
string, you can set it back the way you prefer afterwards.
2. Date can be entered using "07/06/1999" using double quotes.