, January 29, 2004
4:29 PMTo: Multiple recipients of list ORACLE-LSubject:
Re: Date Format: MysteryRajesh,A
look into the docs might help you:In date-format SS means seconds as
of the seconds 00-59 in a minute.S means seconds since midnight, thus
0 - 86399Compiling the statement the
Rajesh,
A look into the docs might help you:
In date-format SS means seconds as of the seconds 00-59 in a
minute.
S means seconds since midnight, thus 0 - 86399
Compiling the statement the longest part is recocnized first.
So:
SS give 06 in your first example.
gives 20, but twice: 2020
Title: RE: Date format is unreadable
to_date('01/01/1970', 'MM/DD/') + time_stamp / 86400
is a DATE expression
formatting of DATE values is done with the TO_CHAR function (see SQL Reference Manual)
for example:
to_char (to_date('01/01/1970',
Title: RE: Date format is unreadable
It works better but it still does give correct output. I'd like to see hour,min,sec as well.
1 select user_name,node_name,to_date('01/01/1970
Ooops. Should have written
to_date('01/01/1970', 'MM/DD/') + TIME_STAMP / 86400
The timestamp is in seconds since 1/1/1970, while Oracle date arithmetic
is in days.
Sorry.
"Nguyen, David M" wrote:
>
> Yes, it is a Unix timestamp. I use your syntax and I got following error.
>
> 1 se
Yes, it is a Unix timestamp. I use your syntax and I got following error.
1 select
user_name,client_ip,node_name,to_date('01/01/1970','MM/DD/')+tig
2 where user_name = 'admin'
3* order by time_stamp
SQL> /
select user_name,client_ip,node_name,to_date('01/01/1970','MM/DD/') +
ti
Title: RE: Date format is unreadable
David,
It's not a date column. Rather it's a number column that's reading as scientific notation. Some databases, like Access, actually store dates as numbers; however, AFAIK Oracle does not.
Those are some large numbers. 1.0397E+12
David,
I presume that your date is a Unix timestamp.
Try to see whether
to_date('01/01/1970', 'MM/DD/') + TIME_STAMP
yields something more meaningful to you ...
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ste
David - This is not a date column. It is some type of number column (see
type number(20) ). You need to find the program that writes to this column
to find what the interpretation is. You may also want to
column time_stamp format
Dennis Williams
DBA, 40%OCP, 100% DBA
L
David, try setting numwidth in sqlplus...
(for example)
1* create table dtest (col1 varchar2(20), col2 number(20))
SQL> /
Table created.
SQL> insert into dtest values('888','');
1 row created.
SQL> select * from dtest;
COL1 COL2
---
Oracle 7.1.3.2 on VAX/VMS
SQL>
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'Mon (MM) DD, ';
ERROR:
ORA-01816: month may only be specified once
- Kirti
-Original Message-
Sent: Monday, February 11, 2002 3:48 PM
To: Multiple recipients of list ORACLE-L
Would any of you who have conven
Hi Jonathan,
I only have 9i and 8.1.7 and in both cases I get the error
ora-01816: month may only be specified once.
John
[EMAIL PROTECTED] wrote:
>Would any of you who have convenient access to pre-9i
>databases be willing to test something for me? It used to be
>possible to specify a date fo
Can't do it in 8i:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.2.0 - 64bit Production
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'Mon (MM) DD, ';
ERROR:
ORA-01816: month may only be specified once
--- Jon
Title: RE: Date format element rule change
8.1.7.2.0 64bit HP-UX 11.0
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'Mon (MM) DD, ';
ERROR:
ORA-01816: month may only be specified once
Alex.
-Original Message-
From: Jonathan Gennick [mailto:[EMAIL PROTECTED]]
Sent: Mon
Doesn't work in 8i. (8.1.6 to be specific).
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'Mon (MM) DD, ';
ERROR:
ORA-01816: month may only be specified once
Marc Perkowitz
Senior Consultant
TWJ Consulting, LLP
847-256-8866 x15
www.twjconsulting.com
- Original Message -
To: "Multiple r
> Sent: Friday, October 26, 2001 7:40 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: date format in ksh
>
> Jared,
>
> Thanks for the reply.
> I am passing a hardcoded date.
> I am not getting a date from the
> "date" command in ksh.
>
Use the /usr/sadm/bin/valdate command and
check the return status. Examples:
--GOOD DATE--
12322:oracle@e6500a> valdate -f "%e-%b-%Y"
1-jan-200112322:oracle@e6500a> echo $?0
--BAD DATE--
12322:oracle@e6500a> valdate -f "%e-%b-%Y"
41-JaN-200112322:oracle@e6500a> echo $?1
The valda
Jacques,
You could have learned Perl in the time it took to write that. :)
Jared
On Friday 26 October 2001 20:20, Jacques Kilchoer wrote:
> > -Original Message-
> > From: Suhen Pather [mailto:[EMAIL PROTECTED]]
> >
> > Thanks for the reply.
> > I am passing a hardcoded date.
> > I am n
WRONG"
fi
HTH,
Regards,
- Kirti Deshpande
Verizon Information Services
http://www.superpages.com
> -Original Message-
> From: Suhen Pather [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, October 26, 2001 7:40 PM
> To: Multiple recipients of list ORACLE-L
> Subject:
Title: RE: date format in ksh
> -Original Message-
> From: Suhen Pather [mailto:[EMAIL PROTECTED]]
>
> Thanks for the reply.
> I am passing a hardcoded date.
> I am not getting a date from the
> "date" command in ksh.
>
> What my ksh does is re
Jared,
Thanks for the reply.
I am passing a hardcoded date.
I am not getting a date from the
"date" command in ksh.
What my ksh does is retrieves $1 and must check if the date format
is in DD-MON-YY eg. 02-FEB-01.
It uses the hardcoded date (passed in as argument 1) in the script.
If the date i
date +%d-%b-%Y
man date.
You may have to look at the man page on a system with more
recent documentation, such as linux. The man page on
Solaris is incomplete.
JARed
Just something to consider, if he goes ahead with this request:
How would you distinguish between the 11 of February 1999 ( 1121999 ) and
the 1st of December 1999 ( 1121999 )?
If he is prepared to enter both digits of the month (at least) then this
could result ion a non-ambigous date - my exampl
Mohammad,
This really depends on the front end tool you are using.
Is it Oracle Forms? You can probably use a trigger in the
form to allow your client to do this. I haven't programmed
Forms in awhile, and can't give detailed directions.
This kind of ridiculous request is exactly the reason I
Mohammed,
Just off the top of my head, I don't think this will work unless you
bring the day, month and year in separately. At first glance I thought
perhaps if you took the length of the date string (less than 6 or more
than eight you've got an error), backed out the last four as the year -
so
set nls_date_format for the session to 'ddmm'.
In this case also he needs to enter date as '09091999' and not '991999'!
Evaluate the pros and cons before making such a change in live db.
Thanks,
Amar Kumar Padhi
-Original Message-
Sent: Sunday, April 29, 2001 12:50 PM
To: Multiple
nes 12 de marzo de 2001 16:06
> Para: Multiple recipients of list ORACLE-L
> Asunto: RE: date format
>
> Hi Sonja,
>
> Unfortunately Oracle does not support milliseconds.
>
> Lisa Rutland Koivu
> Oracle Database Administrator
> Qode.com
> 4850 North Sta
Yes it is, but only with the time series cartridge.
-Original Message-
Sent: Monday, March 12, 2001 9:20 AM
To: Multiple recipients of list ORACLE-L
List hi!
When we want to format data, we can do:
select to_char(sysdate,'dd.mm. hh24:mi:ss') from dual;
Is it possible to form
Title: RE: date format
Hi Sonja,
Unfortunately Oracle does not support milliseconds.
Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319
V: 954.484.3191, x174
F: 954.484.2933
C: 954.658.5849
http://www.qode.com
29 matches
Mail list logo