Re: [PHP-DB] Retrieving a date from Oracle. Please help!
Thank you ALL!
It turned out that setting NLS_DATE_FORMAT environment variable is enough to
solve the problem. It's possible to get it worked right from PHP code, for
instance:
putenv("NLS_DATE_FORMAT=HH24:MI:SS DD.MM.");
PS>
TO_DATE function didn't help
I didn't try to ALTER SESSION or change Oracle ini params
Maxim
Best regards,
Maxim Bubnov,
Software Engineer,
Stelt Telecom NN
- Original Message -
From: "Ford, Mike [LSS]" <[EMAIL PROTECTED]>
To: "'maxim'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Thursday, May 23, 2002 8:39 PM
Subject: RE: [PHP-DB] Retrieving a date from Oracle. Please help!
> > -Original Message-
> > From: maxim [mailto:[EMAIL PROTECTED]]
> > Sent: 23 May 2002 17:00
> >
> > Hi all!
> > The problem is I can't get expected datetime value from
> > SELECT query on
> > Oracle table that has a field of type DATE. Seems like
> > datetime looses its
> > time part.
>
> The default format for dates returned from Oracle tends to be something
like 'DD-MON-YY', thus giving only a short-form date. There are several
ways of changing this:
>
> * Use the TO_DATE function in your select, e.g.
> $sql = "select TO_DATE(A_DATE, 'DD-Mon- HH24:MI:SS') as
DATE_TIME from A_TABLE"
>
> * Use ALTER SESSION to set the default format for the current session
(NOTE: I haven't tried this (yet!)):
> $stmt = OCIParse("ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon-
HH24:MI'");
> OCIExecute($stmt);
>
> * Change the default value of the Oracle initialization parameter
NLS_DATE_FORMAT (I haven't tried this either, as I don't have administrative
control of my Oracle database!).
>
> Mike
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Retrieving a date from Oracle. Please help!
Hi,
ALTER SESSION SET NLS_DATE_FORMAT="-MM-DD HH24:MI:SS"
bye,
maxim wrote:
> Hi all!
> The problem is I can't get expected datetime value from SELECT query on
> Oracle table that has a field of type DATE. Seems like datetime looses its
> time part.
>
> The code is like as follows:
> ...
> $sql = "select * from A_TABLE";
> ora_parse($curs, $sql);
> ora_exec($curs);
> ...
> ora_fetch_into($cursor, $r, ORA_FETCHINTO_NULLS);
> foreach ($r as $key => $value) {
> print "$key => $value \n";
> }
> ...
>
> This displays date part only. May be special PHP or Oracle ini parameters or
> environment vars required? Any help appreciated.
>
> Best regards,
> Maxim Bubnov,
> Software Engineer,
> Stelt Telecom NN
>
>
>
--
--
--
E-mail: [EMAIL PROTECTED]
JAWA Management Software GmbH
Liebenauer Hauptstr. 200
A-8041 GRAZ
Tel: ++43-(0)316-403274-12
Fax: ++43-(0)316-403274-10
GSM: ++43-(0)676-93 96 698
homepage: http://www.jawa.at
- privat ---
E-mail: [EMAIL PROTECTED]
homepage: http://www.inode.at/mbretter
--
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Retrieving a date from Oracle. Please help!
> -Original Message-
> From: maxim [mailto:[EMAIL PROTECTED]]
> Sent: 23 May 2002 17:00
>
> Hi all!
> The problem is I can't get expected datetime value from
> SELECT query on
> Oracle table that has a field of type DATE. Seems like
> datetime looses its
> time part.
The default format for dates returned from Oracle tends to be something like
'DD-MON-YY', thus giving only a short-form date. There are several ways of changing
this:
* Use the TO_DATE function in your select, e.g.
$sql = "select TO_DATE(A_DATE, 'DD-Mon- HH24:MI:SS') as DATE_TIME from
A_TABLE"
* Use ALTER SESSION to set the default format for the current session (NOTE: I
haven't tried this (yet!)):
$stmt = OCIParse("ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon- HH24:MI'");
OCIExecute($stmt);
* Change the default value of the Oracle initialization parameter NLS_DATE_FORMAT (I
haven't tried this either, as I don't have administrative control of my Oracle
database!).
Cheers!
Mike
-
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services,
JG125, James Graham Building, Leeds Metropolitan University,
Beckett Park, LEEDS, LS6 3QS, United Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Retrieving a date from Oracle. Please help!
> -Original Message-
> From: maxim [mailto:[EMAIL PROTECTED]]
> Sent: 23 May 2002 17:00
>
> Hi all!
> The problem is I can't get expected datetime value from
> SELECT query on
> Oracle table that has a field of type DATE. Seems like
> datetime looses its
> time part.
The default format for dates returned from Oracle tends to be something like
'DD-MON-YY', thus giving only a short-form date. There are several ways of changing
this:
* Use the TO_DATE function in your select, e.g.
$sql = "select TO_DATE(A_DATE, 'DD-Mon- HH24:MI:SS') as DATE_TIME from
A_TABLE"
* Use ALTER SESSION to set the default format for the current session (NOTE: I
haven't tried this (yet!)):
$stmt = OCIParse("ALTER SESSION SET NLS_DATE_FORMAT = 'DD-Mon- HH24:MI'");
OCIExecute($stmt);
* Change the default value of the Oracle initialization parameter NLS_DATE_FORMAT (I
haven't tried this either, as I don't have administrative control of my Oracle
database!).
Cheers!
Mike
-
Mike Ford, Electronic Information Services Adviser,
Learning Support Services, Learning & Information Services,
JG125, James Graham Building, Leeds Metropolitan University,
Beckett Park, LEEDS, LS6 3QS, United Kingdom
Email: [EMAIL PROTECTED]
Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Retrieving a date from Oracle. Please help!
Hi all!
The problem is I can't get expected datetime value from SELECT query on
Oracle table that has a field of type DATE. Seems like datetime looses its
time part.
The code is like as follows:
...
$sql = "select * from A_TABLE";
ora_parse($curs, $sql);
ora_exec($curs);
...
ora_fetch_into($cursor, $r, ORA_FETCHINTO_NULLS);
foreach ($r as $key => $value) {
print "$key => $value \n";
}
...
This displays date part only. May be special PHP or Oracle ini parameters or
environment vars required? Any help appreciated.
Best regards,
Maxim Bubnov,
Software Engineer,
Stelt Telecom NN
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Retrieving a date.
In article <[EMAIL PROTECTED]>, Ken Thompson wrote: > Great, that explains better than "most" of the tutorials I've read. > It is unfortunate that most if not all reference documentation expect the > reader to have a college degree just on their subject in order to understand > "wot in 'ell" they're saying. > if thats the impression you have, you're not looking hard enough. There are a particularly good (read, "understandable") bunch of tutorials at: hotwired.lycos.com/webmonkey/programming/php/tutorials but, simply plugging "php" "mysql" "tutorial" (without the quotes) into google (or whatever search engine you prefer) should turn up all the information that you could possibly use. -- Rgds Ralph -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Retrieving a date.
> > Ok? > Great, that explains better than "most" of the tutorials I've read. > It is unfortunate that most if not all reference documentation expect the > reader to have a college degree just on their subject in order to understand > "wot in 'ell" they're saying. > I've done pretty well in figuring out many thing on my own but sometimes ya > just need an expert to chip in and make yer day. > My thanks, My pleasure. In fact the MySQL manual is a cut above many/most 'out there', but as you say it is a reference manual, ie a set of rules and regulations to describe the functionality - more like a dictionary than an encyclopedia - and not many people's idea of relaxing bed-time reading! By contrast, the tutorial sites are designed to 'teach' the use of such functionality. Taking a PHP example, I noted the LIST() and FOR EACH construct in the manual, and pretty much said "yes, ok, so", but it was only when I worked through a tutorial which put them together that I saw a neat and powerful way to manipulate associative arrays (ok, maybe that says more about me than reference manuals and tutorials, but...). If you have done some programming before, or if you are confident in your ability to pick up the basics, then I recommend "PHP and MySQL Web Development" by Welling and Thomson, SAMS, to you. It has a good tutorial style (beyond presuming introductory PHP/programming knowledge) and presents the combination of PHP and MySQL in a series of practical applications/scenarios. I found it very good - but then I have used SQL before and several other programming languages. If the starting point is not a problem, it should also appeal to your interest/approach. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Retrieving a date.
On Sunday 03 March 2002 02:48 am, DL Neil wrote:
> Ken,
>
> > > Check out DAYOFMONTH() and MONTH() and a wealth of other useful date
> > > functions. RTFM: 6.3.4 Date and Time Functions
> >
> > There's a few interesting items here OK, Thanks for the pointer.
>
> Go for it...
>
> > > The answer to your question about AUTO_INCREMENT 'reset' can be
>
> found at
>
> > > 6.5.3 CREATE TABLE Syntax under "table_options".
> >
> >
> > EH? It says what?? I'm so green that this makes NO sense at all.
> >
> > Regards,table_options:
> > TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM |
>
> MYISAM }
>
> > or AUTO_INCREMENT = #
>
> You asked:
> > Off Topic, I saw something about resetting the auto-increment
>
> counter, but
>
> > now I can't find reference to it. Can some kind soul enlighten me???
>
> Sorry to overwhelm you with jargon - it can be difficult to assess a
> person's capabilities over the email. So by way of a general answer may
> I point you at the MySQL and PHP web site home pages, and from there to
> their links to tutorial books and web sites. These will help you with
> concepts and examples.
>
> The reference to "6.5.3 CREATE TABLE Syntax" is in the electronic manual
> at http://www.mysql.com/doc/C/R/CREATE_TABLE.html. The CREATE TABLE
> command enables you to (re-)build a table by defining its 'schema'
> (definitions, rules and/or constraints). The very next section of the
> manual, 6.5.4 ALTER TABLE Syntax deals with making changes to a table's
> structure.
>
> If you are starting from scratch the former applies. If amending an
> existing table, then the latter is of more interest. Logically enough
> both follow much the same rules in terms of what you can/can't do.
>
> Reading the CREATE TABLE command 'template', we see:
>
> CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
> [(create_definition,...)]
> [table_options] [select_statement]
>
> "table options" is therefore something that may be included almost at
> the end of the command. "may" is indicated by the square brackets =
> optional. So a 'bare' command would include the words CREATE and TABLE,
> followed by a table name, and then defining one or more fields within
> parentheses. eg
>
> CREATE TABLE Cinfo
> ( Cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
> Fname TEXT)
>
> The contents of the parentheses - a list of fields and their
> definitions, are templated by the two lists: "create_definition" and
> "type". Once the fields are defined (and the parentheses closed) you MAY
> then decide to add further descriptions of the table. These are defined
> in the "table_options" definitions, and include an additional
> specification of interest to you:
>
> AUTO_INCREMENT = #
>
> If you add this clause to the above and replace the # with a suitable
> integer, you can define the starting sequence number to be used in the
> ID column, eg:
>
> CREATE TABLE Cinfo
> ( Cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
> Fname TEXT)
> AUTO_INCREMENT = 1001;
>
> Hope that's enough to get you re-started. If you plug "AUTO_INCREMENT"
> into the electronic manual's search facility you find a ton of stuff
> showing you how to use such columns/series - and a few warnings.
>
> Ok?
> =dn
Great, that explains better than "most" of the tutorials I've read.
It is unfortunate that most if not all reference documentation expect the
reader to have a college degree just on their subject in order to understand
"wot in 'ell" they're saying.
I've done pretty well in figuring out many thing on my own but sometimes ya
just need an expert to chip in and make yer day.
My thanks,
--
Ken Thompson, North West Antique Autos
Payette, Idaho
Email: [EMAIL PROTECTED]
http://www.nwaa.com
Sales and brokering of antique autos and parts.
Linux- Coming Soon To A Desktop Near You
Registered Linux User #183936
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Retrieving a date.
Ken,
> > Check out DAYOFMONTH() and MONTH() and a wealth of other useful date
> > functions. RTFM: 6.3.4 Date and Time Functions
> There's a few interesting items here OK, Thanks for the pointer.
Go for it...
> > The answer to your question about AUTO_INCREMENT 'reset' can be
found at
> > 6.5.3 CREATE TABLE Syntax under "table_options".
>
> EH? It says what?? I'm so green that this makes NO sense at all.
>
> Regards,table_options:
> TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM |
MYISAM }
> or AUTO_INCREMENT = #
You asked:
> Off Topic, I saw something about resetting the auto-increment
counter, but
> now I can't find reference to it. Can some kind soul enlighten me???
Sorry to overwhelm you with jargon - it can be difficult to assess a
person's capabilities over the email. So by way of a general answer may
I point you at the MySQL and PHP web site home pages, and from there to
their links to tutorial books and web sites. These will help you with
concepts and examples.
The reference to "6.5.3 CREATE TABLE Syntax" is in the electronic manual
at http://www.mysql.com/doc/C/R/CREATE_TABLE.html. The CREATE TABLE
command enables you to (re-)build a table by defining its 'schema'
(definitions, rules and/or constraints). The very next section of the
manual, 6.5.4 ALTER TABLE Syntax deals with making changes to a table's
structure.
If you are starting from scratch the former applies. If amending an
existing table, then the latter is of more interest. Logically enough
both follow much the same rules in terms of what you can/can't do.
Reading the CREATE TABLE command 'template', we see:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
"table options" is therefore something that may be included almost at
the end of the command. "may" is indicated by the square brackets =
optional. So a 'bare' command would include the words CREATE and TABLE,
followed by a table name, and then defining one or more fields within
parentheses. eg
CREATE TABLE Cinfo
( Cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Fname TEXT)
The contents of the parentheses - a list of fields and their
definitions, are templated by the two lists: "create_definition" and
"type". Once the fields are defined (and the parentheses closed) you MAY
then decide to add further descriptions of the table. These are defined
in the "table_options" definitions, and include an additional
specification of interest to you:
AUTO_INCREMENT = #
If you add this clause to the above and replace the # with a suitable
integer, you can define the starting sequence number to be used in the
ID column, eg:
CREATE TABLE Cinfo
( Cid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Fname TEXT)
AUTO_INCREMENT = 1001;
Hope that's enough to get you re-started. If you plug "AUTO_INCREMENT"
into the electronic manual's search facility you find a ton of stuff
showing you how to use such columns/series - and a few warnings.
Ok?
=dn
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Retrieving a date.
On Saturday 02 March 2002 16:13, DL Neil wrote:
> Ken,
>
> Check out DAYOFMONTH() and MONTH() and a wealth of other useful date
> functions. RTFM: 6.3.4 Date and Time Functions
There's a few interesting items here OK, Thanks for the pointer.
> The answer to your question about AUTO_INCREMENT 'reset' can be found at
> 6.5.3 CREATE TABLE Syntax under "table_options".
EH? It says what?? I'm so green that this makes NO sense at all.
Regards,table_options:
TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
or AUTO_INCREMENT = #
> =dn
--
Ken Thompson, North West Antique Autos
Payette, Idaho
Email: [EMAIL PROTECTED]
http://www.nwaa.com
Sales and brokering of antique autos and parts.
Linux- Coming Soon To A Desktop Near You
Registered Linux User #183936
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Retrieving a date.
Ken,
Check out DAYOFMONTH() and MONTH() and a wealth of other useful date
functions. RTFM: 6.3.4 Date and Time Functions
The answer to your question about AUTO_INCREMENT 'reset' can be found at
6.5.3 CREATE TABLE Syntax under "table_options".
Regards,
=dn
> I have a test table with the ususal customer information in it along
with a
> field for the date of birth. I'm trying to return records of any
customers
> whose birthdates are the same day and month as the current date.
> Here's the query & result:
> mysql> SELECT * FROM Cinfo WHERE BirthDate = "date('m d')";
> Empty set (0.00 sec)
> Here's what is returned with a global query:
> mysql> select * from Cinfo;
>
+---+--+---+-+---++-
-+--+--+++
> | FirstName | LastName | Address | City| State | Zip
|
> HomePhone| WorkPhone| CellPhone| BirthDate | id |
>
+---+--+---+-+---++-
-+--+--+++
> | Joe | Blow | 1200 High St. #12 | St.Looy | UT| 844110
|
> 801-111-2299 | 801-111-3456 | 801-213-8956 | 1959-03-01 | 1 |
> | | | | | |
|
>| | || 2 |
> | | | | | |
|
>| | || 3 |
> | | | | | |
|
>| | || 4 |
>
+---+--+---+-+---++-
-+--+--+++
> 4 rows in set (0.00 sec)
>
> I have a feeling it's the date format in the table. I tried making it
a date
> field but just got the current date. What am I doing wrong?
> Off Topic, I saw something about resetting the auto-increment
counter, but
> now I can't find reference to it. Can some kind soul enlighten me???
> --
>
>
> Ken Thompson, North West Antique Autos
> Payette, Idaho
> Email: [EMAIL PROTECTED]
> http://www.nwaa.com
> Sales and brokering of antique autos and parts.
>
> Linux- Coming Soon To A Desktop Near You
> Registered Linux User #183936
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Retrieving a date.
I have a test table with the ususal customer information in it along with a
field for the date of birth. I'm trying to return records of any customers
whose birthdates are the same day and month as the current date.
Here's the query & result:
mysql> SELECT * FROM Cinfo WHERE BirthDate = "date('m d')";
Empty set (0.00 sec)
Here's what is returned with a global query:
mysql> select * from Cinfo;
+---+--+---+-+---++--+--+--+++
| FirstName | LastName | Address | City| State | Zip|
HomePhone| WorkPhone| CellPhone| BirthDate | id |
+---+--+---+-+---++--+--+--+++
| Joe | Blow | 1200 High St. #12 | St.Looy | UT| 844110 |
801-111-2299 | 801-111-3456 | 801-213-8956 | 1959-03-01 | 1 |
| | | | | ||
| | || 2 |
| | | | | ||
| | || 3 |
| | | | | ||
| | || 4 |
+---+--+---+-+---++--+--+--+++
4 rows in set (0.00 sec)
I have a feeling it's the date format in the table. I tried making it a date
field but just got the current date. What am I doing wrong?
Off Topic, I saw something about resetting the auto-increment counter, but
now I can't find reference to it. Can some kind soul enlighten me???
--
Ken Thompson, North West Antique Autos
Payette, Idaho
Email: [EMAIL PROTECTED]
http://www.nwaa.com
Sales and brokering of antique autos and parts.
Linux- Coming Soon To A Desktop Near You
Registered Linux User #183936
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
