Sent by: cc:
[EMAIL PROTECTED] Subject: Re: SV: Date Format: Mystery
Friday, January 30, 2004, 2:24:25 AM, Jesper Haure Norrevang ([EMAIL PROTECTED]) wrote:
JHN> Certainly som conversion is going on here. This might be the reason why
JHN> there has been confusion about 7 or 8 bytes in a DATE datatype.
That's really interesting, that switch between 7 and 8
bytes. Or
lto:[EMAIL PROTECTED] På vegne af Jesper
Haure NorrevangSendt: 30. januar 2004 08:24Til: Multiple
recipients of list ORACLE-LEmne: SV: Date Format:
Mystery
Rajesh,
SYSDATE is of datatype DATE (that's what the documentation says), i.e.
it contains century, year, month, day, hour, mi
esper Haure Nørrevang
-Oprindelig meddelelse-Fra: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] På vegne af Pillai,
RajeshSendt: 30. januar 2004 01:59Til: Multiple
recipients of list ORACLE-LEmne: RE: Date Format:
Mystery
Carel-Jan,
This explanation helps. Thanks a
lot.
, 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
Y-MM-DD HH24:MI:SS AM') from dual;
ORA-01821: date format not recognized
4) select to_char(sysdate,'-MM-DD HH24:MI:SSSS AM') from dual;
ORA-01821: date format not recognized
5) select to_char(sysdate,'-MM-DD HH24:MI:SS AM') from dual;
result = 2004-
Folks:
>From what I gather, Oracle stores dates as the number of elapsed days since
Jan 1, 1968. When I query a column of type 'DATE', it returns me the
default format, dd-mon-yy. I know I can use to_char(date_col,'MMDD')
etc... to define many output formats.
What can I do to get the raw in
ould
work.
HTH!
Aleem
-Original Message-
Sent: Wednesday, March 12, 2003 1:20 AM
To: Multiple recipients of list ORACLE-L
Subject:sql query: to_date() :ORA-01830: date format picture ends
before
Hi...
I am getting the following error with a query like this
select *
ke this
BR> select * from cust where
BR> to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
BR> to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
BR> ORA-01830: date format picture ends before converting entire input string
BR> What is the prob
; Hi...
>
> I am getting the following error with a query like this
>
> select * from cust where
> to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
> to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
>
> ORA-01830: date format picture ends befor
"Basavaraja, Ravindra" wrote:
>
> Hi...
>
> I am getting the following error with a query like this
>
> select * from cust where
> to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
> to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS&#
to_date('01-FEB-03
07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Weiss, Rick
INET: [EMAIL PROTECTED]
Fat Cit
g error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
What is the problem?
Thanks
--
Plea
Title: RE: sql query: to_date() :ORA-01830: date format picture ends before
Why are you comparing a date to a char?
select *
from cust
where to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') = '01-FEB-03 07:53:28'
/
should work it appears that your NLS_DATE_F
Hi...
I am getting the following error with a query like this
select * from cust where
to_char(DATECREATED,'DD-MON-YYY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
Correction
I am using
select * from cust where
to_char(DATECREATED,'DD-MON-YY HH24:MI:SS') =
to_date('01-FEB-03 07:53:28','DD-MON-YY HH24:MI:SS')
ORA-01830: date format picture ends before converting entire input string
> -Original Messag
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
---
I try to query a report to check user activity but I could
not read date column. Is there a way to reformat the column to be
readable? Date colum calls "time_stamp" as shown below.
SQL> desc user_activity_log
Name
cipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: PL/SQL Date Format
Jared,
SELECT df1('2002-13-01') FROM dual; -- :)
CREATE OR REPLACE PROCEDURE set_expire_date (
p_user_group_id IN NUMBER DEFAULT NULL,
p_product_id
Jared,
SELECT df1('2002-13-01') FROM dual; -- :)
CREATE OR REPLACE PROCEDURE set_expire_date (
p_user_group_id IN NUMBER DEFAULT NULL,
p_product_id IN VARCHAR2 DEFAULT NULL,
p_expire_dateIN VARCHAR2 DEFAULT NULL
)
IS
ld_dummy
]
01/07/2003 05:38 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:RE: PL/SQL Date Format
Not good, Jared. Try this:
SQL> select df1('01-JAN-03') from dual;
DF1('01-J
e date is '||to_char(local_date, '-MM-DD')); <== changed this
EXCEPTION WHEN INVALID_NUMBER
THEN dbms_output.put_line('Invalid Date
format'); dbms_output.put_line('Format must be
-MM-DD ('||to_char(sysdate, '-MM-DD')|
Title: RE: PL/SQL Date Format
Not good, Jared. Try this:
SQL> select df1('01-JAN-03') from dual;
DF1('01-J
-
03-JAN-01
Oops! I don't know how you could do this other than to parse the string like you did and look for invalid_num exceptions. Or force the
uot; <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/07/2003 03:20 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject:PL/SQL Date Format
Okay, PL/SQL programmers, a lowly dba is in need o
e_date, 1,
4); v_expire_month := substr(p_expire_date,
6,2); v_expire_day := substr(p_expire_date,
9,2); v_expire_date := to_date(p_expire_date,
'-MM-DD'); dbms_output.put_line('Expire date is
'||to_char(v_expire_date, '-MM-DD'));
--
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, July 30, 2002 1:38 AM
> Hi,
> The default date format for an instance is dd-mon-.
> Ex when issue the following statement "Select to_char(sysdate) from dual;"
> The o
-Original Message-
Sent: Tuesday, July 30, 2002 11:08 AM
To: Multiple recipients of list ORACLE-L
Hi,
The default date format for an instance is dd-mon-.
Ex when issue the following statement "Select to_char(sysdate) from dual;"
The output is 01-Jan-2002.
I want to change t
I checked my Registry..
Nls_date_format is not set in the registry..
-Original Message-
From: Yechiel Adar [SMTP:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 5:18 PM
To: Multiple recipients of list ORACLE-L
Subject:Re: how to change nls date format.
Did you check
Did you check the registry on the machine that you execute sqlplus on to see
if there is nls_date_format there?
Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Tuesday, July 30, 2002 7:38 AM
> Hi,
> The default dat
Hi,
The default date format for an instance is dd-mon-.
Ex when issue the following statement "Select to_char(sysdate) from dual;"
The output is 01-Jan-2002.
I want to change this format to mm/dd/.
According to Oracle documentation this can be changed @ sesion level by the
Alexander,
Your example below is very helpful, and explains why I
recalled that duplicating date elements was possible. I
probably used TO_CHAR to test this in the past, while the
other day I was using ALTER SESSION.
My theory on why you can't duplicate elements when setting
NLS_DATE_FORMAT is t
Title: RE: Re[4]: Date format element rule change
Jonathan,
You are right.
From FM:
For input format models, format items cannot appear twice,
and format items that represent similar information cannot be combined.
For example, you cannot use 'S' and 'BC' in th
Title: RE: Re[2]: Date format element rule change
You can, using to_char, but not in "alter ... nls ..."
SQL> select to_char(sysdate,'Mon (MM) DD, ') from dual;
TO_CHAR(SYSDATE,'
-
Feb (02) 11, 2002
1 row selected.
Alex.
-Original
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 format element twice in a format
model. For example, in the following the month is specified
twice:
ALTER SESSION SET NLS_DATE_FORMAT = 'Mon (MM) DD, ';
In
To everyone who took the time to test my date format
question and reply, I'd like to express my thanks. I'm
working with Steven Feuerstein to revise a chapter in his
book on PL/SQL, and the book stated that you could repeat
elements in a date format string. I knew that wasn't true
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 speci
be specified once
--- Jonathan Gennick <[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 format element twice in a format
> model. For example, in the foll
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
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, February 11, 2002 3:48 PM
> 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 format element twi
> 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.
>
Message-From: Suhen Pather
[mailto:[EMAIL PROTECTED]]Sent: Thursday, October 25,
2001 8:00 PMTo: Multiple recipients of list
ORACLE-LSubject: date format in ksh
List,
Sorry to deviate from the topic,
but
I am writing a korn shell script
that would exit if the date forma
ing 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)
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.
I
cc:
Sent by: Subject: date format in ksh
[EMAIL PROTEC
List,
Sorry to deviate from the topic, but
I am writing a korn shell script that would exit if the date
format is
incorrect.
I just need something to compare the date format passed in with DD-MON-.
Eg.
I am passing an argument to my ksh script being the date.
If the
:06
À : Multiple recipients of list ORACLE-L
Objet : Re: How set Date format Globally
I believ that you can also set this in the init.ora file, I know that I do:)
KK
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, July 11,
(16 bits) in
all clients.
Best Regards,
Nabila Mekkaoui
DBA Oracle
-Message d'origine-
De : Vladimir Begun [mailto:[EMAIL PROTECTED]]
Envoyé : mercredi 11 juillet 2001 09:56
À : Multiple recipients of list ORACLE-L
Objet : Re: How set Date format Globally
On Jul 11, 2001 at 12:35:21
Note: 93924.1 "Advisory Warning of change to
NLS_DATE_FORMAT Default values"
HTH,
-- Anita
--- Vladimir Begun <[EMAIL PROTECTED]>
wrote:
> On Jul 11, 2001 at 12:35:21AM, Rangachari Sundar
> wrote:
> > Hi,
> >
> > I have a requirement. Everytime when i ma
How set Date format Globally
>
> Hi,
>
> I have a requirement. Everytime when i make a session to the database i
> want the
> Date Format to beas follows
>
> '-mm-dd hh:mi:ss'
>
> The existing default format is 'dd-mon-' and so I
CLE-L
Objet : Re: How set Date format Globally
On Jul 11, 2001 at 12:35:21AM, Rangachari Sundar wrote:
> Hi,
>
> I have a requirement. Everytime when i make a session to the database i
want the
> Date Format to beas follows
>
> '-mm-dd hh:mi:ss'
>
> The exist
On Jul 11, 2001 at 12:35:21AM, Rangachari Sundar wrote:
> Hi,
>
> I have a requirement. Everytime when i make a session to the database i want the
> Date Format to beas follows
>
> '-mm-dd hh:mi:ss'
>
> The existing default format is 'dd-mon-y
Hi,
I have a requirement. Everytime when i make a session to the database i want the
Date Format to beas follows
'-mm-dd hh:mi:ss'
The existing default format is 'dd-mon-' and so I tried the following steps.
ALTER SESSION set NLS_DATE_FORMAT='-mm-dd
Hi,
I have a requirement. Everytime when i make a session to the database i want the
Date Format to beas follows
'-mm-dd hh:mi:ss'
The existing default format is 'dd-mon-' and so I tried the following steps.
ALTER SESSION set NLS_DATE_FORMAT='-mm-dd hh:m
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
hi oracle gurus,
my client is very weird,he wants to enter the date in the format 'dd-mm-'
without pressing space instead of delimiters,e.g. he presses
'991999' and wants oracle to change it to '09-09-1999' .!!!
Can I do it for him?
If anyone knows how,I'll be very grateful.
he
> milliseconds. Will that
> work?
>
> Prakash
>
> -Original Message-
> Sent: Wednesday, April 04, 2001 9:56 PM
> To: Multiple recipients of list ORACLE-L
>
>
> I used bcp to get the database from a sybase
> database,
> and plan to load the data back to
got the following defualt date
format from Sybase:
Apr 3 2001 10:29:47:000AM
What's the corresponding date format in Oracle?
('Mon dd hh:mi:ss:???AM')
And did anyone migrate the database from Sybase or SQL
Server to Oracle before?
I used bcp to get the database from a sybase database,
and plan to load the data back to Oracle. The only
problem is that I got the following defualt date
format from Sybase:
Apr 3 2001 10:29:47:000AM
What's the corresponding date format in Oracle?
('Mon dd hh:mi:ss:???AM&
You can build a java procedure that it has a functionality more granular
than DATE format.
For 100th seconds you have dbms_utility.get_time and v$timer.
I think that 9i has it.
Regards.
.
> -Mensaje original-
> De: Koivu, Lisa [SMTP:[EMAIL PROTECTED]]
> Enviado el: lu
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
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 format it even further (like miliseconds)?
TIA,
Sonja
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?ISO-8859-2?Q?Sonja_=A9
75 matches
Mail list logo
