Convert Varchar2 datatype to Date datatype
Partha, July 26, 2001 - 7:57 am UTC
Good technique to implement conversion of varchar2 to date
Convert Varchar2 datatype to Date datatype
devadathan@hotmail.com OCP DBA 7.3 & 8.x, August 01, 2001 - 6:43 am UTC
You can do this conversion in little more simpler way like the following :
Create Table DTCONV(DT Varchar(25))
/
Insert Into DTCONV Select To_Char(SYSDATE,'DD-MON-YYYY HH:MI:SS') From Dual
/
Select To_Char(To_Date(DT,'DD-MM-YY HH:MI:SS'),'DD-MM-YY HH:MI:SS') from DTCONV
/
Try this and see...
re: devadathan@hotmail.com OCP DBA 7.3 & 8.x
A reader, August 02, 2001 - 1:18 pm UTC
Maybe you should re-read the question... The question concerns a table that has the date component in one column (as varchar) and the time component in another column (also as varchar). The original question was in regard to combining the two varchar columns and inserting the result into a third column of type date. All your example does is get the sysdate, convert it into char, insert it into a single column of type varchar, select it back out again, and convert it to a date. Using to_date with the concatenation operator is the most obvious solution to the original question.
Very Helpfule
Kimathi, March 27, 2002 - 3:48 pm UTC
You did it again Tom.
Drew
A reader, April 10, 2002 - 1:04 pm UTC
Hi Tom,
I need to know more about the date conversion in Oracle.
We have the following query:
select * from t_torder where to_char(order_date,'DAY')='SUNDAY';
This query does not return any data even though there are data there. However, if we use 'WEDNESDAY', the query works fine. The DAY is right padded. If we do
where to_char(order_date,'DAY')='SUNDAY ';
or
where trim(to_char(order_date,'DAY'))='SUNDAY';
The query works fine. The same thing with MONTH, only the longest month works.
I was asked why and I could not answer clear. Can you please give some explainations? How the date is saved and coverted?
Thanks in advance.
April 10, 2002 - 2:58 pm UTC
It has nothing to do with how the date data is saved (it is saved in a 7 byte binary format with century, year, month, day, hour, minute, second and in 9i upto 11 bytes with fractional seconds.
It has EVERYTHING to do with how the format mask you used formated the data.
Consider:
ops$tkyte@ORA817DEV.US.ORACLE.COM> @test
'"'||TO_CHA '"'||TO_CHA DT
----------- ----------- ---------
"THURSDAY " "THURSDAY" 11-APR-02
"FRIDAY " "FRIDAY" 12-APR-02
"SATURDAY " "SATURDAY" 13-APR-02
"SUNDAY " "SUNDAY" 14-APR-02
"MONDAY " "MONDAY" 15-APR-02
"TUESDAY " "TUESDAY" 16-APR-02
"WEDNESDAY" "WEDNESDAY" 17-APR-02
7 rows selected.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
WEDNESDAY works cause (in our language), it is the longest. Now, a user in Germany might be confused by your issue:
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set nls_language=German;
Session altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> @test
'"'||TO_CHAR '"'||TO_CHAR DT
------------ ------------ ---------
"DONNERSTAG" "DONNERSTAG" 11-APR-02
"FREITAG " "FREITAG" 12-APR-02
"SAMSTAG " "SAMSTAG" 13-APR-02
"SONNTAG " "SONNTAG" 14-APR-02
"MONTAG " "MONTAG" 15-APR-02
"DIENSTAG " "DIENSTAG" 16-APR-02
"MITTWOCH " "MITTWOCH" 17-APR-02
7 rows selected
Since Mittwoch is not as long as Wednesday....
If you use the fm (format modifier) mask -- you'll get what you want. It'll blank trim things. Using the '"' || ... || '"' trick is something I frequently do to see whats up with the data (dump(to_char(dt,'DAY')) would be useful as well)
to_date exceptions
Johan Snyman, April 10, 2002 - 8:50 pm UTC
Tom,
I have to convert date and time varchar2 columns to an Oracle date field while transferring data from one table to the next. The problem is that the data in the varchar2 columns may not be valid dates and times.
I currently solve the problem by writing a PL/SQL function:
FUNCTION checkcalldatetime
(
v_calldate IN VARCHAR2,
v_calltime IN VARCHAR2,
v_datetimefmt IN VARCHAR2
)
RETURN NUMBER
IS
v_dummydate DATE;
BEGIN
v_dummydate := TO_DATE(v_calldate||v_calltime,v_datetimefmt);
RETURN (0);
EXCEPTION
WHEN OTHERS THEN
RETURN (1);
END;
and then calling this function from the insert into ... select ... statement to retrieve only rows with valid date and time columns.
The problem is that this can be slow for large data volumes (probably due to context switches between SQL engine and PL/SQL engine). What would you suggest to speed up this process ?
April 10, 2002 - 9:42 pm UTC
is the format fixed (always the same)? if so, share that with me along with what the valid data would look like -- database version appreciated as well. with CASE we can do some tricky things.
to_date exceptions
Johan Snyman, April 11, 2002 - 8:20 am UTC
Tom,
The date and time format is always the same, i.e.
Calldate format = 'yyyymmdd'
Calltime format = 'hh24:mi:ss'
Passed into the function as 'yyyymmddhh24:mi:ss'
Typical valid data would be:
Calldate : 20020411
Calltime : 14:22:00
We have Oracle 8.1.6.2 Enterprise Edition
April 11, 2002 - 9:55 am UTC
Here is an example then:
scott@ORA817DEV.US.ORACLE.COM> create table t ( calldate number, calltime varchar2(8) );
Table created.
scott@ORA817DEV.US.ORACLE.COM> exec gen_data( 'T', 500 );
PL/SQL procedure successfully completed.
gen_data is just something I have to generate random data -- that generated 500 "bad" rows for us, rows that will not be dates (almost certainly)
scott@ORA817DEV.US.ORACLE.COM> insert into t select to_char(created,'yyyymmdd'), to_char(created,'hh24:mi:ss' )
2 from all_objects;
14773 rows created.
and that created 14,773 "good" rows for us
scott@ORA817DEV.US.ORACLE.COM> create or replace function my_to_date( p_num in number, p_str in varchar2 ) return date
2 as
3 begin
4 return to_date( p_num || p_str, 'yyyymmddhh24:mi:ss' );
5 exception
6 when others then
7 return null;
8 end;
9 /
Function created.
there is the PLSQL way to do this..
scott@ORA817DEV.US.ORACLE.COM> set timing on
scott@ORA817DEV.US.ORACLE.COM> drop table new_t;
Table dropped.
Elapsed: 00:00:00.35
scott@ORA817DEV.US.ORACLE.COM> create table new_t ( x date );
Table created.
Elapsed: 00:00:00.07
scott@ORA817DEV.US.ORACLE.COM> alter session set sql_trace=true;
Session altered.
scott@ORA817DEV.US.ORACLE.COM> insert into new_t
2 select dt from (
3 select case when substr( calltime, 1, 2 ) between '00' and '23'
4 AND
5 substr( calltime, 4, 2 ) between '00' and '59'
6 AND
7 substr( calltime, 7, 2 ) between '00' and '59'
8 AND
9 substr( calltime, 3, 1) = ':'
10 AND
11 substr( calltime, 6, 1) = ':'
12 AND
13 substr( calldate, 1, 4 ) between '1970' and '2020'
14 AND
15 substr( calldate, 5, 2 ) between '01' and '12'
16 then
17 case when substr( calldate, 7, 2 ) between '01' and
18 to_char(last_day(to_date(substr(calldate,1,6),'yyyymm')),'dd')
19 then to_date(calldate||calltime,'yyyymmddhh24:mi:ss')
20 else null
21 end
22 else null
23 end dt
24 from t
25 )
26 where dt is not null
27 /
14773 rows created.
Elapsed: 00:00:02.28
That shows how we can use SQL to "edit" the data -- verify it falls within our limits. I did a nested case to ensure that the YYYY and MM part are validated before we even attemp to use to_date on them...
I used an inline view to make the SQL easier..
Now lets see that with PLSQL
scott@ORA817DEV.US.ORACLE.COM> drop table new_t;
Table dropped.
Elapsed: 00:00:00.35
scott@ORA817DEV.US.ORACLE.COM> create table new_t ( x date );
Table created.
Elapsed: 00:00:00.06
scott@ORA817DEV.US.ORACLE.COM> insert into new_t
2 select dt from
3 (
4 select my_to_date( calldate, calltime ) dt from t
5 )
6 where dt is not null
7 /
14773 rows created.
Elapsed: 00:00:06.18
scott@ORA817DEV.US.ORACLE.COM>
same number of rows, 3x the runtime. Easier to code but runs slower
TKPROF shows the same timings. You might even be able to speed up the process using something like:
scott@ORA817DEV.US.ORACLE.COM> insert into new_t
2 select dt from (
3 select case
when calltime like '__:__:__' AND calldate between 19700100 and 20201231
4 then to_date( calldate || calltime, 'yyyymmddhh24:mi:ss' )
5 else null
6 end dt
7 from t
8 )
9 where dt is not null
10 /
14773 rows created.
Elapsed: 00:00:00.77
depending on "how dirty" your data is. If you can assume that if the colons are in the right place -- you have the time and if the date is between those numbers, you have the date -- you can short circut alot of the evaluation.
to_date exceptions
Johan Snyman, April 11, 2002 - 10:08 am UTC
Tom,
Thanks for a great answer provided in record time !
But how about this one?
DeeeBeee Crazeee, April 26, 2003 - 8:30 am UTC
Hi Tom,
I have a situation wherein I have a column that can hold valid dates as well as junk (including null). The dates are not in a standard format. It could be either mm/dd/yy or mm/dd or yyyy. Ex:
Col_val Shld_return
------------------------
2003 --> 01/01/2003
Junk --> NULL
01/12/2002 --> 01/12/2002
NULL --> NULL
01/12 --> 01/12/2002
Can this be done using SQL? Is there a function to check for valid dates without providing the format?
thanks in advance
April 26, 2003 - 11:43 am UTC
if you can have mm/dd or dd/mm you are really hosed. 01/02 is what date.
You have garbage. garbage in, garbage out.
you can write a PLSQL to try many different date formats, catching the exception when a string fails -- returning a date if it converted, NULL otherwise.
but you have garbage.
huss, December 22, 2003 - 2:31 pm UTC
hi tom.
i was search in how make numbers spells out
i.e making a function that take
'125' => to retrive =>'One Hundred Twenty-Five'
and i found some nice select that says :
SELECT to_char(to_date(125,'J'),'JSP') FROM DUAL ;
and it work great
but
1- i want the output be in arabic language
i try 'NLS_DATE_LANGUAGE = EGYPTIAN' but no use
2- i want to understand what 'J' mean in to_date function
and what 'JSP' mean in to_char function
December 22, 2003 - 2:43 pm UTC
huss, December 22, 2003 - 2:43 pm UTC
huss, December 22, 2003 - 2:46 pm UTC
OOoops !
sorry
i still have the quesion of how make number spell out in Arabic ?!
Clean up data in date format
Sean, January 25, 2004 - 9:28 pm UTC
Hi Tom,
After database migration. Some of data in date field are not what we wanted, such as year 2001 becomes 0001.
I tried to fix it, but the method I used is not very neat.
Here is the sample.
create table t(c date, c1 date);
insert into t(c)
values('01-JAN-0001');
insert into t(c)
values('01-JAN-0080');
insert into t(c)
values('01-JAN-0999');
insert into t(c)
values('01-JAN-2001');
insert into t(c)
values('01-JAN-1999');
update t
set c1 = decode(substr(to_char(c, 'YYYY'), 1, 3), '000',
to_date(to_char(c, 'DD-MON')||'-'||'200'||substr(to_char(c, 'YYYY'), 4)
));
update t
set c1= decode(substr(to_char(c, 'YYYY'), 1, 2), '00',
to_date(to_char(c, 'DD-MON')||'-'||'19'||substr(to_char(c, 'YYYY'), 3)
))
where substr(to_char(c, 'YYYY'), 3,1) <> '0';
update t
set c1 = decode(substr(to_char(c, 'YYYY'), 1, 1), '0',
to_date(to_char(c, 'DD-MON')||'-'||'1'||substr(to_char(c, 'YYYY'), 2)
))
where substr(to_char(c, 'YYYY'), 2,1) <> '0';
SQL> select to_char(c, 'YYYY') c, to_char(c1, 'YYYY') c1 from t;
C C1
---- ----
0001 2001
0080 1980
0999 1999
2001
1999
1999
Thanks so much for your help.
Sean
January 26, 2004 - 6:10 am UTC
looks like someone used 2 character years :(
just
update t set c1 = to_date( to_char(c1,'dd-mon-yy'), 'dd-mon-rr' );
ops$tkyte@ORA9IR2> select * from t;
C C1
-------------------- --------------------
01-jan-0001 00:00:00
01-jan-0080 00:00:00
01-jan-0999 00:00:00
01-jan-2001 00:00:00
01-jan-1999 00:00:00
ops$tkyte@ORA9IR2> update t set c = to_date(to_char(c,'dd-mon-yy'), 'dd-mon-rr' );
5 rows updated.
ops$tkyte@ORA9IR2> select * from t;
C C1
-------------------- --------------------
01-jan-2001 00:00:00
01-jan-1980 00:00:00
01-jan-1999 00:00:00
01-jan-2001 00:00:00
01-jan-1999 00:00:00
Change data in date format
Sean, January 26, 2004 - 3:40 pm UTC
Thanks so much.
But how does 'rr' do this magic?
Sean
January 26, 2004 - 4:09 pm UTC
Please clarify relation between nls_date_format and to_date
Moorthy Rekapalli, February 25, 2004 - 10:15 am UTC
Tom,
Please look at the following and help me understand the relation between nls_date_format and to_date.
I explicitly altered the NLS_DATE_FORMAT at session level and was expecting that TO_DATE will throw an error for not supplying 4 digit year. Instead, it inserted the row and it is neither 1952 nor 2052. But, it is 0052.
SQL> alter session set nls_date_format = 'DD-MON-YYYY';
Session altered.
SQL> create table test
2 (dob_str varchar2(30)
3 ,dob_date date
4 );
Table created.
SQL> insert into test(dob_str, dob_date)
2 values ('01-APR-52', TO_DATE('01-APR-52'));
1 row created.
SQL> select * from test;
DOB_STR DOB_DATE
------------------------------ -----------
01-APR-52 01-APR-0052
Some additional info on my environment:
SQL> select * from v$version;
SQL> select value
2 from nls_database_parameters
3 where parameter = 'NLS_DATE_FORMAT';
VALUE
----------------------------------------
DD-MON-RR
SQL> select * from v$version;
BANNER
-----------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
PL/SQL Release 9.2.0.3.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production
Thanks in advance,
Moorthy Rekapalli
February 25, 2004 - 11:23 am UTC
that is the year 0052, the year 52 is just as valid as the year 100 as is 1965 as is 2525.
You cannot use 2 characters to represent that which takes a MINIMUM of 4 to accurately convey.
Agreed, we use 4 digits for year. But, I need the help on this concept.
Moorthy Rekapalli, February 25, 2004 - 12:46 pm UTC
Tom,
I follow your suggestion. I also suggest everyone to use 4 digits for year (even if they don't ask - I suggest anyway:)
After the IT industry has gone through the effort of fixing the Y2K issue in a hard way, everyone should have learned their lesson.
My question is regarding clearing up the concept. As per Oracle Documentation URL:
</code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/functions137a.htm#1003595 <code>
"
Text description of to_date
TO_DATE ( char [, fmt [, 'nlsparam']] )
If you omit fmt, then char must be in the default date format.
"
My session nls_date_format was DD-MON-YYYY and my database/instance level nls_date_format was DD-MON-RR.
Even if you give the fmt of DD-MON-RR in the TO_DATE, it will interpret as 1952. When fmt is not specified, I was expecting TO_DATE to take the fmt of either session level or database level NLS_DATE_FORMAT setting. Also, we are using the default of AMERICA for NLS_TERRITORY at instance level.
In a nutshell, I was expecting either the year 1952 (if Oracle was taking database level setting) or an ORA- error (if Oracle was taking session level setting) for not supplying the 4 digits for year.
It did not do both. That's why, I want to clearup my understanding. If you can help on this, that will be great.
In any project, there will be several developers. For whatever reason, if a developer is not following 4 digit year standard and trying to insert with two digts, I want database engine to throw an error rather than accepting it, which might have side affects like this.
Again, Thanks for your help.
Moorthy Rekapalli.
Begin Excerpt from Oracle Documentation:
Purpose
TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, then char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer.
The default date format is determined implicitly by the NLS_TERRITORY initialization parameter, or can be set explicitly by the NLS_DATE_FORMAT parameter.
The 'nlsparam' has the same purpose in this function as in the TO_CHAR function for date conversion.
Do not use the TO_DATE function with a DATE value for the char argument. The first two digits of the returned DATE value can differ from the original char, depending on fmt or the default date format.
End Excerpt from Oracle Documentation.
February 25, 2004 - 1:10 pm UTC
the number 52 and 0052 "are the same"
YYYY says "4 digit year"
52 = 0052
that is all, it'll accept both numbers for the year 52.
Just like
1-jan-2004
is just as ok as 01-jan-2004 is...
"In any project, there will be several developers. For whatever reason, if a
developer is not following 4 digit year standard and trying to insert with two
digts, I want database engine to throw an error rather than accepting it, which
might have side affects like this."
design standards, peer reviews, code walkthrus, testing were designed to catch this. if you have developers working in a vacum..... without review, you will have many such issues.
Thanks - now this is crystal clear to me
Moorthy Rekapalli, February 25, 2004 - 2:34 pm UTC
Tom,
Thanks for clarifying. So far, I was under the impression that Oracle will do some kind of string parsing to compare the to_date('char') with nls_date_format setting.
As per my tests and your explanation, it is clear that in YYYY format, it will consider two digit year AS four digit year with leading zeros.
Thanks,
Moorthy.
February 25, 2004 - 2:52 pm UTC
and 3 digits and 1 digit as well!
ops$tkyte@ORA920PC> select to_date( '1' ), to_date( '12'), to_date('123'), to_date( '1234' ) from dual;
TO_D TO_D TO_D TO_D
---- ---- ---- ----
0001 0012 0123 1234
Date Format
Giri, February 25, 2004 - 9:03 pm UTC
Tom,
I need to write function to convert the any give date format to below format
YYYYMMDD
Input format may be
1)DD-MON-YYYY
2)dd/mm/yyyy
3)yyyy/mm/dd
some times column may contain pure characters like
'hi tom'. In this case I need to return 'hi tom' only.
Basically the column is varchar2 data type. It can take any format,But if it is a date filed I need to convert to yyyymmdd.
February 26, 2004 - 8:09 am UTC
and what pray tell happens when the input is actually mm/dd/yyyy. sigh. GIGO rules i guess (google "define: gigo", love that feature of google -- define...)
if it is one of those three, and those three only, I might look at the substr(str,3,1) and decode
ops$tkyte@ORA920PC> select x, to_date(x,decode(substr(x,3,1),
2 '-','dd-mon-yyyy',
3 '/','dd/mm/yyyy',
4 'yyyy/mm/dd')) from t;
X TO_DATE(X
--------------- ---------
01-jan-2004 01-JAN-04
01/01/2004 01-JAN-04
2004/01/01 01-JAN-04
varchar2 date
GIRI, February 26, 2004 - 9:16 am UTC
Tom,
Some times there will be a space betwnn the characters like below
01 - JAN - 2003
01 / JAN / 2003
01 / 12 /2003.
In this case I cannot use substr and decode for character position.
February 26, 2004 - 1:38 pm UTC
you can replace spaces to nothing easily enough.
Drop any zeros on month and day
Partha, June 01, 2004 - 7:51 pm UTC
Tom
I need to drop any zeroes on month and day i.e.
if I have a date like
'01/01/2004' , then I should just display 1/1/2004.
What is the best way to achieve this.
Thanks
June 01, 2004 - 8:46 pm UTC
'fmmm/dd/yyyy'
fm = format modifier = turn off blanks/leading zeros | turn them back on.
Partha, June 01, 2004 - 9:29 pm UTC
SQL> connect scott/tiger
Connected.
SQL> select to_char(sysdate, 'fmmm/dd/yyyy') from dual;
TO_CHAR(SY
----------
6/1/2004
SQL>
Very interesting you are applying the format to the month, and leaving the day format as 'dd', but I see that still the leading zeroes on the day are getting removed. Please explain further.
June 02, 2004 - 7:37 am UTC
fm works as a toggle.
fm - fm is on.... - fm - fm is off.
ops$tkyte@ORA9IR2> select to_char(sysdate,'fmmm/dd/yyyy'),
2 to_char(sysdate,'fmmmfm/dd/yyyy' ),
3 to_char(sysdate,'mmfm/dd/yyyy')
4 from dual;
TO_CHAR(SY TO_CHAR(SY TO_CHAR(SY
---------- ---------- ----------
6/2/2004 6/02/2004 06/2/2004
each time it sees fm, it toggles the whitespace/leading zero suppression flag.
Feedback for "Partha"
A reader, June 02, 2004 - 1:08 am UTC
Partha,
The format modifier "fm" is for the whole date, not just for the month (mm).
Please read the SQL Reference manual. Everything is well explained there.
How to remove leading `+' and 0 from this query.
Ian Matyssik, June 02, 2004 - 5:04 am UTC
SQL> SELECT to_char((sysdate-to_date('1999/01/01','YYYY/MM/DD')) YEAR(5) TO MONTH) from dual;
SQL> SELECT to_char((sysdate-to_date('1999/01/01','YYYY/MM/DD')) YEAR(5) TO MONTH) from dual;
TO_CHAR((SYSDA
--------------
+000000005-05
TO_CHAR((SYSDA
--------------
+000000005-05
Does someone know how to reformat this output to get something like 5-05 or 5 years 5 months. I looked all over the documents on that and could not make it work in 9ir2.
June 02, 2004 - 8:43 am UTC
I'd probably use an inline view (if the computation was "long" as yours is) so as to make using extract easier:
ops$tkyte@ORA9IR2> select ymint,
2 extract( year from ymint ) || ' years, ' ||
3 extract( month from ymint ) || ' months'
4 from (
5 SELECT (sysdate-to_date('1999/01/01','YYYY/MM/DD')) YEAR(2) TO MONTH ymint
6 from dual
7 )
8 /
YMINT
---------------------------------------------------------------------------
EXTRACT(YEARFROMYMINT)||'YEARS,'||EXTRACT(MONTHFROMYMINT)||'MONTHS'
-------------------------------------------------------------------------------
+000000005-05
5 years, 5 months
fm is a switch
Steve, June 02, 2004 - 6:03 am UTC
You have to switch fm on and off - consider:
SQL> select to_char(sysdate,'fmmm/fmdd/yyyy') from dual;
TO_CHAR(SY
----------
6/02/2004
Cheers
Steve
zeros
dxl, August 11, 2004 - 9:51 am UTC
Tom
We have some numbers which represent codes to store in a table. I would like to store these numbers in a "NUMBER" field as you should do!
However these codes may look like:
00034343
008989
0000000787878
and apparently if you strip off the leading zeros they are not valid codes anymore!
So is there a way of storing these codes in a number field which will preserve the leading zeros?? Obviously i could just store them as varchars but I don't want to be hit by any number to varchar conversion issues/errors at a later date when using sql on this field. As far as I'm concerned numbers should be stored as numbers and varchars as varchars.
Many thanks
August 11, 2004 - 12:37 pm UTC
if it has leading zeroes, it is "not a number"
if the leading zeros are relevant then
001
01
1
are all "different codes"
therefore, you have a string.
Check constraint can ensure varchar2 data is 'numeric'
Gary, August 11, 2004 - 7:26 pm UTC
>>if it has leading zeroes, it is "not a number"
Just to add that the questioner can put in a check constraint to ensure that the string only contains the characters 0123456789.
And with regular expressions in 10g, it becomes a lot easier to enforce format masks on VARCHAR2 data.
A reader, September 23, 2004 - 11:36 am UTC
in my flat file i have a string value which maps to a date in my table.
string : '09/22/2004'
Now the nls_date_format is set to 'dd-mon-yy'
In my forms I have a varchar2 variable which stores this string i.e.
lv_date = '09/22/2004'
When I'm trying to insert it into my table column ddate which is a date datatype, I'm getting a invalid month error.
What is the correct way to convert it back into date of format 'dd-mon-yy'.
Thanks
SQL> var vdate varchar2(15);
SQL>
SQL> exec :vdate := '09/22/2004' ;
PL/SQL procedure successfully completed.
SQL> insert into t values (:vdate);
insert into t values (:vdate)
*
ERROR at line 1:
ORA-01843: not a valid month
September 24, 2004 - 9:04 am UTC
insert into t values ( to_date( :vdate, 'mm/dd/yyyy') );
SOS : combine date & time ( time in varchar2 column )
ok, November 04, 2004 - 6:48 am UTC
tom,
i have a table that has two columns actiondate & actiontime
in a leagcy application
actiondate DATE
actiontime varchar2(20)
i have to combine the two columns and insert into one
in the new appliction using :
To_date(ACTIONDATE||' '||ACTIONTIME,'DD-MON-YY HH:MI:SS AM')
but the problem is that the ACTIONTIME field has time in
different formats i.e.
10:56:58 PM len = 11
10:56:59 AM
10:56:59 PM
10:57 AM len=8*
10:57 PM
10:57:00 AM
10:57:00 PM
11:33:08 len=8* ???
11:34 AM
11:35 AM
11:35 AM
11:36 AM
11:36 AM
11:36 AM
11:37:42
11:37 AM
so when i run the above mentioned SQL i get the error
RA-01858: a non-numeric character was found where a numeric was expected
please help me with this conversion
Thanks in advance
November 05, 2004 - 12:04 pm UTC
See, this is EXACTLY why you use
numbers for numbers...
strings for strings....
dates for dates.....
So, what is:
11:33:08
is that 11:33 am or pm -- or will be be in 24 hour format -- ugh. what a crime.
assuming "am" then.
if we have just those three formats -- we can use something like this:
ops$tkyte@ORA9IR2> select substr( decode( instr(x,' '),
2 0, x,
3 substr( x,1, instr(x,' ')-1 ) ) || ':00', 1, 8) ||
4 decode( instr(x,' '), 0, ' AM', substr( x,instr(x,' ') ) ) am_pm,
5 x
6 from t;
AM_PM X
----------------------- ---------------
10:56:58 PM 10:56:58 PM
10:56:59 AM 10:56:59 AM
10:56:59 PM 10:56:59 PM
10:57:00 AM 10:57 AM
10:57:00 PM 10:57 PM
10:57:00 AM 10:57:00 AM
10:57:00 PM 10:57:00 PM
11:33:08 AM 11:33:08
11:34:00 AM 11:34 AM
11:35:00 AM 11:35 AM
11:35:00 AM 11:35 AM
11:36:00 AM 11:36 AM
11:36:00 AM 11:36 AM
11:36:00 AM 11:36 AM
11:37:42 AM 11:37:42
11:37:00 AM 11:37 AM
16 rows selected.
RE : Fractional Seconds
A reader, June 17, 2005 - 7:08 pm UTC
Tom,
I have to write a query that should add '005' fractional seconds to each of the consecutive rows. eg.
My output should be as follows
Row# Time
1 6/17/2005 6:58:25.010 PM
2 6/17/2005 6:58:25.015 PM
3 6/17/2005 6:58:25.020 PM
and so on.
I tried the following query and is not working.
select dt2,
cast(lag(dt2) over(order by dt2) + interval '00:00.005' minute to second as timestamp(3))
from
(
select cast(cast(sysdate as timestamp(3)) + interval '00:00.005' minute to second as timestamp(3)) dt2
from all_objects
order by 1
)
order by dt2
Your response would be of great help.
Thanks
June 17, 2005 - 7:34 pm UTC
why not just add rownum * 0.005 to each row after sorting?
or, is the question really "i want to take the first timestamp and then assign to each subsequent row that timestamp + rownum*0.005"?
Help with the syntax
A reader, June 18, 2005 - 12:11 am UTC
Tom,
Could you pls. help me with the syntax of doing that.
June 18, 2005 - 7:13 pm UTC
I had an or there, I'm trying to tell you "question ambigous, not sure what you really want"
RE : Fractional Seconds
A reader, June 19, 2005 - 12:18 pm UTC
It is 'add .005' fractional seconds to each subsequent row. ie. start with a timestamp value say and and .005 to each row. ie. first row .000, second row .000 + .005, third .000 + .005 + .005 and so on.
June 19, 2005 - 12:35 pm UTC
Ok, I'll be more verbose in my question.
are we adding rownum*0.005 to the timestamp contained in the current row (dt2)
OR
are we for the "first row" using that timestamp value
for the second row using the first rows timestamp + 0.005
for the third row using the FIRST ROWS timestamp + 0.010
and so on
c1 is current row plus N
c2 is first row plus N
tkyte@ORA9IR2W> select username,
2 c1+numtodsinterval(rownum*0.005,'second') c1,
3 c2+numtodsinterval(rownum*0.005,'second') c2
4 from (
5 select username, created,
6 cast(created as timestamp(3)) c1,
7 cast( first_value(created) over (order by created) as timestamp(3)) c2
8 from all_users
9 )
10 /
USERNAME C1 C2
---------- ------------------------------- -------------------------------
SYS 12-MAY-02 04.18.08.005000000 PM 12-MAY-02 04.18.08.005000000 PM
SYSTEM 12-MAY-02 04.18.08.010000000 PM 12-MAY-02 04.18.08.010000000 PM
OUTLN 12-MAY-02 04.18.25.015000000 PM 12-MAY-02 04.18.08.015000000 PM
DBSNMP 12-MAY-02 04.30.49.020000000 PM 12-MAY-02 04.18.08.020000000 PM
WMSYS 12-MAY-02 04.44.32.025000000 PM 12-MAY-02 04.18.08.025000000 PM
ORDSYS 12-MAY-02 05.13.10.030000000 PM 12-MAY-02 04.18.08.030000000 PM
ORDPLUGINS 12-MAY-02 05.13.10.035000000 PM 12-MAY-02 04.18.08.035000000 PM
MDSYS 12-MAY-02 05.13.10.040000000 PM 12-MAY-02 04.18.08.040000000 PM
CTXSYS 12-MAY-02 05.29.54.045000000 PM 12-MAY-02 04.18.08.045000000 PM
XDB 12-MAY-02 05.32.14.050000000 PM 12-MAY-02 04.18.08.050000000 PM
ANONYMOUS 12-MAY-02 05.32.14.055000000 PM 12-MAY-02 04.18.08.055000000 PM
WKSYS 12-MAY-02 05.40.17.060000000 PM 12-MAY-02 04.18.08.060000000 PM
WKPROXY 12-MAY-02 05.40.21.065000000 PM 12-MAY-02 04.18.08.065000000 PM
ODM 12-MAY-02 05.44.03.070000000 PM 12-MAY-02 04.18.08.070000000 PM
ODM_MTR 12-MAY-02 05.44.03.075000000 PM 12-MAY-02 04.18.08.075000000 PM
OLAPSYS 12-MAY-02 05.59.08.080000000 PM 12-MAY-02 04.18.08.080000000 PM
HR 12-MAY-02 06.02.19.085000000 PM 12-MAY-02 04.18.08.085000000 PM
OE 12-MAY-02 06.02.37.090000000 PM 12-MAY-02 04.18.08.090000000 PM
PM 12-MAY-02 06.07.28.095000000 PM 12-MAY-02 04.18.08.095000000 PM
SH 12-MAY-02 06.32.37.100000000 PM 12-MAY-02 04.18.08.100000000 PM
QS_ADM 12-MAY-02 06.59.41.105000000 PM 12-MAY-02 04.18.08.105000000 PM
QS 12-MAY-02 06.59.43.110000000 PM 12-MAY-02 04.18.08.110000000 PM
QS_WS 12-MAY-02 06.59.43.115000000 PM 12-MAY-02 04.18.08.115000000 PM
QS_ES 12-MAY-02 06.59.44.120000000 PM 12-MAY-02 04.18.08.120000000 PM
QS_CBADM 12-MAY-02 06.59.44.125000000 PM 12-MAY-02 04.18.08.125000000 PM
QS_CS 12-MAY-02 06.59.44.130000000 PM 12-MAY-02 04.18.08.130000000 PM
QS_CB 12-MAY-02 06.59.44.135000000 PM 12-MAY-02 04.18.08.135000000 PM
QS_OS 12-MAY-02 06.59.44.140000000 PM 12-MAY-02 04.18.08.140000000 PM
SCOTT 12-MAY-02 08.18.22.145000000 PM 12-MAY-02 04.18.08.145000000 PM
RMAN 12-MAY-02 08.20.55.150000000 PM 12-MAY-02 04.18.08.150000000 PM
TKYTE 08-NOV-04 07.26.35.155000000 PM 12-MAY-02 04.18.08.155000000 PM
TEST 06-DEC-04 10.05.45.160000000 AM 12-MAY-02 04.18.08.160000000 PM
RT_TEST 16-DEC-04 03.38.09.165000000 PM 12-MAY-02 04.18.08.165000000 PM
A 09-JUN-05 06.07.20.170000000 PM 12-MAY-02 04.18.08.170000000 PM
B 09-JUN-05 06.07.20.175000000 PM 12-MAY-02 04.18.08.175000000 PM
35 rows selected.
why 7 bytes for date datatype?
adolf_h, July 10, 2005 - 7:47 am UTC
<quote>
It has nothing to do with how the date data is saved (it is saved in a 7 byte
binary format with century, year, month, day, hour, minute, second and in 9i
upto 11 bytes with fractional seconds.
</quote>
dont you think that date datatype is wasting space unnecessarily? many bits of the 7 byte will never be used. month for eg. must be x < 13 for which 4 bits are sufficient.
why oracle also dosent use popular trick of number of seconds since "base date"?
July 10, 2005 - 9:30 am UTC
answer backwards...
you store the number of seconds since some base date (like unix). Ok, they can store dates back to 1970. That would not work in real life as people want to store things back to 1900 or 47BC or even 4712BC. We need to realistically support a broad range of dates.
Also, there is the issue of performance and ease of doing things. Trunc(date,'mm') becomes a very simple "just consider the fire 3 bytes when comparing", not the first 2.75 bytes or anything like that.
Could you pack it smaller? Probably.
Would it perform better? most likely not even close.
Would it be a lot harder to deal with cross platform and such? probably.
seconds - 0..59, 6 bits
minutes - 0..59, 6 bits
hours - 0..23, 5 bits
days - 1..31, 5 bits
and so on
so you mash those all together -- ugh, bit field from heck to intrepret. trunc becomes hard, everything becomes hard.
back to the seconds since something -- that is computationally the most expensive way to do anything.
people ask "give me everything between 01-jan-2005 and 06-jan-2005"
must now figure out how many 'seconds' since some date 01-jan-2005 is. That is *really hard*
what happened in 1582?
ops$tkyte@ORA9IR2> select to_date('31-oct-1582')-to_date('1-oct-1582') x from dual;
X
---------
20
Or wait, was it really 1752?
[tkyte@desktop tkyte]$ cal 9 1752
September 1752
Su Mo Tu We Th Fr Sa
1 2 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
add in leap years, leap seconds, everything and that number of seconds since something becomes totally unmanageable.
Maybe this would be helpfull for some people
Maurice Kremer, August 05, 2005 - 5:42 am UTC
Hi,
I've had to create a function that could be used in a SQL statement to convert varchar to date. Maybe it is useful to someone else too.
(Validation was intended to be used with table journaling column creationdate and last modification date. But can also be ommited by specifying fixed dates.)
Kind regards,
Maurice
function DATECONVERSION (
p_enteredtext varchar2
,p_firstdate date
,p_lastdate date
)
RETURN date
AS
/*
* Function to convert varchar to date with validation check.
*
* Inputs: varchar2 - text to convert to date
* date - lowerlimit of date
* date - upperlimit of date
*
* Output: date - validated date or null when no conversion was posible
*
* Author: Maurice Kremer
*
* Date Version Author Comment
* 05-Aug-05 0.1 MKr New
*
*/
type t_varchartable
is table of varchar2(50)
index by binary_integer; -- table type definition
v_dateformats t_varchartable; -- table of dateformats
v_converted date; -- varchar date to date
v_validdate date; -- validated date
BEGIN
/* setup dateformats to check
* can be expanded freely
*/
v_dateformats(1) := 'DD-Mon-YY HH24:MI:SS';
v_dateformats(2) := 'MM/DD/YY HH24:MI:SS';
v_dateformats(3) := 'DD/MM/YY HH24:MI:SS';
v_dateformats(4) := 'YY/DD/MM HH24:MI:SS';
v_dateformats(5) := 'YY/MM/DD HH24:MI:SS';
v_dateformats(6) := 'MM/YY/DD HH24:MI:SS';
v_dateformats(7) := 'SS/YY/MM HH24:MI:SS';
v_validdate := null;
for v_index in 1..v_dateformats.count loop
begin
v_converted := null; -- clear field of previous conversion
/*
* try to convert date using specific date format, on error
* loop will continue without raising error
*/
v_converted := to_date(
p_enteredtext
,v_dateformats(v_index)
);
exception
when others then
null; -- conversion failed, just continue
end;
if not v_converted is null -- is date converted
and v_converted < p_lastdate -- validate against p_lastdate
and v_converted > p_firstdate then -- validate against p_firstdate
if nvl(v_validdate,v_converted) = v_converted then
/* date is valid */
v_validdate := v_converted;
else
/* a different second valid date has been found
* routine should quit
*/
return null;
end if;
end if;
end loop;
return v_validdate;
END;
to_date('1') in 10g
Krishna, February 10, 2006 - 2:32 am UTC
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta
PL/SQL Release 10.2.0.1.0 - Beta
CORE 10.2.0.1.0 Beta
TNS for 32-bit Windows: Version 10.2.0.1.0 - Beta
NLSRTL Version 10.2.0.1.0 - Beta
SQL> alter session set nls_date_format='DD-Mon-YYYY';
Session altered.
SQL> select * from nls_database_parameters
2 where parameter='NLS_DATE_FORMAT';
PARAMETER VALUE
----------------------------------------------
NLS_DATE_FORMAT DD-MON-RR
SQL> select to_date('1') from dual;
select to_date('1') from dual
*
ERROR at line 1:
ORA-01840: input value not long enough for date format
What's going on here? Can you please clarify? I was expecting the output 0001!
February 10, 2006 - 12:45 pm UTC
you said "I shall give you a string in the format of dd-mon-rr and you shall convert strings like that into dates"
Then you gave us "1"
"1" is not "dd-mon-rr"
so we said "sorry, the string you gave us is not long enough to match the date format, we expected to find a "-mon-rr" bit - and it is not there. All we know is '1', but we do not know the mon or the year"
oops! Figured it out!
Krishna, February 10, 2006 - 2:36 am UTC
SQL> alter session set nls_date_format='YYYY';
Session altered.
SQL> select to_date('1') from dual;
TO_D
----
0001
D'uh!
February 10, 2006 - 12:46 pm UTC
beware, that is the first day of the CURRENT month in the year 1
(eg: right now, that is 01-feb-0001, one month from now that is 01-mar-0001)
NLS_DATE_LANGUAGE
Karthikeyan, March 16, 2006 - 7:08 am UTC
is there any option to get the NLS_DATE_LANGUAGE format inside the procedure...
or the current system date setting...
March 16, 2006 - 2:18 pm UTC
ops$tkyte@ORA9IR2> create or replace procedure date_format
2 as
3 begin
4 for x in ( select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT' )
5 loop
6 dbms_output.put_line( 'date format: ' || x.value );
7 end loop;
8 end;
9 /
Procedure created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec date_format;
date format: DD-MON-RR
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> alter session set nls_date_format = 'dd-month-yyyy hh24:mi:ss';
Session altered.
ops$tkyte@ORA9IR2> exec date_format;
date format: dd-month-yyyy hh24:mi:ss
PL/SQL procedure successfully completed.
"Convert varchar2 to Date format",
jash, April 12, 2006 - 9:29 am UTC
This is my view. I want INVOICE_CREATE_DATE to be dd-mon-yyyy. how to overcome this issue
CREATE OR REPLACE VIEW test_VDB_UGC_DATA
(TRANSACTION_ID, AIG_COMPANY_CODE, AIG_BRANCH_CODE, AIG_DEPARTMENT_CODE, AIG_GEO_REGION,
AIG_COUNTRY_CODE, GL_MAJOR_CODE, GL_MINOR_CODE, GL_SUB_CODE, GL_ACCOUNT,
GL_ACCOUNT_DESC, R_TYPE, PO_NUMBER, LINEITEM_IDENTIFIER, PRODUCT_CODE,
PRODUCT_DESC, PRODUCT_PRICE, PRODUCT_UOM, TOTAL_LINEITEM_AMOUNT, UNSPSC,
PO_TOTAL_AMOUNT, PO_CREATE_DATE, PO_APPROVAL_DATE, VOUCHER_NUMBER, INVOICE_NUMBER,
INVOICE_AMOUNT,INVOICE_CREATE_DATE,INVOICE_PAID_DATE, PURCHASE_METHOD, CHECK_NUMBER,
CHECK_DESCRIPTION, VENDOR_ID, SYSTEM_SOURCE, DB_UNSPSC, DB_UNSPSC_DESC,
DUNS_NUMBER, DOMESTIC_DUNS_NUMBER, GLOBAL_DUNS_NUMBER, PORTFOLIONAME, AIG_UNSPSC,
SIC_CODE, VENDOR_NAME, FILE_YEAR)
AS
SELECT TRANSACTION_ID ,
AIG_COMPANY_CODE ,
AIG_BRANCH_CODE ,
AIG_DEPARTMENT_CODE,
AIG_GEO_REGION ,
AIG_COUNTRY_CODE ,
GL_MAJOR_CODE ,
GL_MINOR_CODE ,
GL_SUB_CODE ,
GL_ACCOUNT ,
GL_ACCOUNT_DESC ,
R_TYPE ,
PO_NUMBER ,
LINEITEM_IDENTIFIER ,
PRODUCT_CODE ,
PRODUCT_DESC ,
PRODUCT_PRICE ,
PRODUCT_UOM ,
REPLACE(REPLACE(TOTAL_LINEITEM_AMOUNT, ' ', ''), ',', '') AS TOTAL_LINEITEM_AMOUNT,
UNSPSC ,
REPLACE(REPLACE(PO_TOTAL_AMOUNT, ' ', ''), ',', '') AS PO_TOTAL_AMOUNT ,
PO_CREATE_DATE ,
PO_APPROVAL_DATE ,
VOUCHER_NUMBER ,
INVOICE_NUMBER ,
INVOICE_AMOUNT ,
INVOICE_CREATE_DATE ,
INVOICE_PAID_DATE,
PURCHASE_METHOD ,
CHECK_NUMBER ,
CHECK_DESCRIPTION ,
VENDOR_ID ,
SYSTEM_SOURCE ,
DB_UNSPSC ,
DB_UNSPSC_DESC ,
"DUNS Number" AS DUNS_NUMBER ,
"DB Domestic Ultimate DUNS" AS DOMESTIC_DUNS_NUMBER,
"DB Global Ultimate DUNS" AS GLOBAL_DUNS_NUMBER ,
PORTFOLIONAME,
AIG_UNSPSC,
SIC_CODE,
VENDOR_NAME,
FILE_YEAR
FROM DB_UGC_DATA a
WHERE trim(NVL("DB Global Ultimate DUNS", 'p')) <> '044691103'
AND trim(NVL(a.vendor_name, 'n/a')) NOT IN (SELECT trim(vendor_name) FROM TINTERCOMPANY_VENDOR )
AND PORTFOLIONAME NOT LIKE 'Out%'
AND trim(NVL(load_flag, 'N')) = 'N'
April 12, 2006 - 11:11 am UTC
dd-mon-yyyy
that is just a "format", you would
to_char(column,'dd-mon-yyyy')
upon retrieving the data to format it any which way you like. All Oracle dates are 7 byte binary fields containing
the century
the year
the month
the day
the hour
the minute
the second
you use format strings to have that binary data converted to the string of your choice upon retrieval
Varchar to Date
Saif Malik, August 09, 2006 - 9:40 am UTC
Dear Tom
I have a field in my table test1 VARCHAR2(20), now I want to convert it into Date data type. I tried the tips you gave in this thread but they dont seem to work
August 09, 2006 - 11:11 am UTC
well, nothing on this page is about changing datatypes, so I am not surprised it "did not work"
This is one approach:
ops$tkyte%ORA10GR2> create table t
2 as
3 select username, user_id, to_char(created, 'dd-mon-yyyy hh24:mi:ss') created
4 from all_users;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> desc t
Name Null? Type
---------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
CREATED VARCHAR2(20)
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t add created_date date;
Table altered.
ops$tkyte%ORA10GR2> update t set created_date = to_date(created,'dd-mon-yyyy hh24:mi:ss'),
2 created = null;
45 rows updated.
ops$tkyte%ORA10GR2> alter table t drop column created;
Table altered.
ops$tkyte%ORA10GR2> alter table t rename column created_date to created;
Table altered.
ops$tkyte%ORA10GR2> desc t
Name Null? Type
---------------------------------------- -------- ----------------------------
USERNAME NOT NULL VARCHAR2(30)
USER_ID NOT NULL NUMBER
CREATED DATE
dbms_redefinition would be another (search site for that)
Why is TO_DATE('2006','YYYY') non-deterministic?
Duke Ganote, September 14, 2006 - 2:01 pm UTC
September 14, 2006 - 4:28 pm UTC
I don't know "why", just that "it does"
compare time component of date datatype
Karma, January 11, 2007 - 2:59 pm UTC
I have one column defined timestamp in table A and date as another columns in table B. What is the best way to join this table efficiently to compare only time component?
Also how should we index on both tables
Another date string comparison problem
san_mat, January 24, 2007 - 8:18 am UTC
We have a parameter table which contains all parameters for the application. It also stores holidays (they are stored as varchar.
SQL> desc ts_parameter_master
Name Type
----------------------------
PARAM_ID number(3)
PARAM_TYPE varchar2(25)
PARAM_VALUE varchar2(25)
PARAM_KEY varchar2(25)
SQL> select ltrim(trim(to_date(prm_master.param_value,'MM/DD/YYYY'))) date1
2 from ts_apex.ts_parameter_master prm_master
3 WHERE prm_master.param_type = 'Holiday'
4 /
DATE1
----------
01/01/2007
01/26/2007
04/06/2007
08/15/2007
08/28/2007
10/02/2007
11/08/2007
11/09/2007
12/25/2007
9 rows selected.
Now for comparison in one of our validation code, we get two date variables ldate1 & ldate2. We need to compaire these date values with the values we stored in above table as varchar.
Something like the below query >>
1 select ltrim(trim(to_date(prm_master.param_value,'MM/DD/YYYY'))) date1
2 from ts_apex.ts_parameter_master prm_master
3 WHERE prm_master.param_type = 'Holiday'
4 AND ltrim(trim(to_date(prm_master.param_value,'MM/DD/YYYY')))
5* BETWEEN ldate1 AND ldate2
We tried different approach..the error we get is 'ORA-01840'. Please suggest how do we resolve this. The same code works perfect in Unix environment. It gives problem on the windows machine.
Thanks a ton in advance.
Continuation of my previous post...
san_mat, January 25, 2007 - 5:15 am UTC
Its really confusing...
When i try in Unix (following query modified a little bit) it works perfectly...
SQL> conn ts_apex@peacock
Enter password: *******
Connected.
SQL> select ltrim(trim(to_date(prm_master.param_value,'MM/DD/YYYY'))) date1
2 from ts_apex.ts_parameter_master prm_master
3 WHERE prm_master.param_type = 'Holiday'
4 AND ltrim(trim(to_date(prm_master.param_value,'MM/DD/YYYY')))
5 BETWEEN TO_DATE('01/25/2007', 'MM-DD-YYYY') AND TO_DATE('01/29/2007', 'MM-DD-YYYY')
6 /
DATE1
---------------
26-JAN-07
The Original query gives errors in Unix but works in Windows.
SQL> SELECT to_date(prm_master.param_value, 'MM-DD-YYYY') lv_alrdy_app
2 FROM (select * from ts_apex.ts_parameter_master
3 WHERE param_type = 'Holiday') prm_master
4 where to_date(prm_master.param_value, 'mm/dd/yyyy')
5 BETWEEN TO_DATE('01/25/2007', 'MM-DD-YYYY') AND TO_DATE('01/29/2007', 'MM-DD-YYYY');
where to_date(prm_master.param_value, 'mm/dd/yyyy')
*
ERROR at line 4:
ORA-01840: input value not long enough for date format
SQL>
convert timestamp to date
Phil, September 22, 2007 - 7:20 am UTC
Hi Tom
Do I really have to do the following to knock off the subsecond element of a timestamp datatype?
to_date(to_char(s.local_timestamp, 'dd-MON-yyyy mi:ss'),'dd-MON-yyyy mi:ss')
September 26, 2007 - 1:29 pm UTC
or....
ops$tkyte%ORA10GR2> create table t ( x timestamp );
Table created.
ops$tkyte%ORA10GR2> insert into t values ( systimestamp );
1 row created.
ops$tkyte%ORA10GR2> select x, cast(x as date) from t;
X
---------------------------------------------------------------------------
CAST(XASDATE)
--------------------
26-SEP-07 01.23.43.819540 PM
26-sep-2007 13:23:44
to_date, to_what?
Jay, November 01, 2007 - 11:21 am UTC
Hi there, did this by chance while looking at this question:
SQL> select to_date('19-Oct','YYYYMM') from dual;
TO_DATE('19-OCT','YY
--------------------
01-OCT-0019 00:00:00
Can this be explained, or is it a bug?
version 10.2.0.3.0
November 02, 2007 - 12:12 pm UTC
ops$tkyte%ORA10GR2> select to_date('19-Oct','YYYYMM') from dual
2 /
TO_DATE('
---------
01-OCT-19
ops$tkyte%ORA10GR2> select to_date('19-Oct','fxYYYYMM') from dual
2 /
select to_date('19-Oct','fxYYYYMM') from dual
*
ERROR at line 1:
ORA-01862: the numeric value does not match the length of the format item
it is not a bug, they are somewhat 'lax' on the formats unless you say "fx" for format exact.
they try other conversions...
Making it up
Jay, November 05, 2007 - 3:50 am UTC
Thanks Tom - I guess I'll be using "fx" in future!
I understand "lax" might mean turning a space char into a zero or something of that nature, but in my example it looks as though they have taken it too far - that should have raised an error. I'm sure you agree.
Thanks again
The User inputs many Date Formats...
Zoe, November 07, 2007 - 10:03 am UTC
Hi, looking good, just wanted to clarify a few things about date formatting:
At the moment a user has to input any date field in the format of 'DD-MON-YYYY' (UK Dates), else it throws back an 'Invalid Date or Format' Message.
(.pll package that fires during ON-ERROR of any form)
Now, I have seen at a previous company where you can reformat some user input without getting as far as the error message by capturing certain inputs they may use.
Examples are: 'T' (changes to SYSDATE)
'Y' (changes to SYSDATE - 1)
'BM','EM' (first/last day of current month and year)
Now these ones I can do with:
IF get_item_property(name_in('system.cursor_item'),
DATATYPE) IN
('DATE',
'DATETIME')
THEN
IF :system.cursor_value = 'T'
THEN
old_date_formatted := SYSDATE;
format_bool := TRUE;
ELSIF :system.cursor_value = 'Y'
THEN
old_date_formatted := SYSDATE -1;
format_bool := TRUE;
ELSIF :system.cursor_value = 'BM'
THEN
old_date_formatted := '01'||TO_CHAR(SYSDATE,'MON-RRRR');
format_bool := TRUE;
ELSIF :system.cursor_value = 'EM'
THEN
old_date_formatted := LAST_DAY(SYSDATE);
format_bool := TRUE;
etc....
Copy(TO_CHAR(old_date_formatted ),
:System.Cursor_Item);
etc...
BUT: The user may also try to input either just the Day, Day & Month, Day & Month& Year, in a variety of ways that I want to capture and Fix (but warn that it has been fixed) before the error comes up i.e.
Day ('7', '07') --> '07-<current month>-<current year>'
Day-Month ('72', '0702', '07-02', '07/02', '7/2', '07-FEB') --> '07-FEB-<current year>'
(I know if they try '22' they will get 22nd of month and not 2nd Feb)
Day-Month-Year('070205', 07022005 '7/2/05' , '07/02/(20)05')--> '07-FEB-2005'
The things I am coming up with do not seem very neat at the moment:
ELSIF (instr(:system.cursor_value,'/', 1, 1) = 0
OR instr(:system.cursor_value,'-', 1, 1) = 0)
THEN
IF (length( :system.cursor_value) <=2
AND LPAD(substr( :system.cursor_value,1,2), 2,'0')
BETWEEN '01' AND TO_CHAR(LAST_DAY(SYSDATE),'DD')
THEN
old_date_formatted
:= LPAD(:system.cursor_value,2,'0')||TO_CHAR(SYSDATE,'MON-RRRR');
format_bool := TRUE;
ELSIF ...... --got 'DD' done so far and not sure on if this is the right direction to take
Can you help at all as I am sure it can be done as I have seen it work, but I am not sure on what went on behind the scenes to get it to how I envision...?
sanjay, June 02, 2011 - 2:08 am UTC
to get varchar datatype into time :
Select To_Char(To_Date(DT,'DD-MM-YY HH:MI:SS'),'DD-MM-YY HH:MI:SS') from DTCONV
This query is not giving exact time of entry into database.
it is giving same time for all
Thanks in advance....
June 02, 2011 - 9:05 am UTC
what the heck is that for???
look at your format and think for a minute. YY - really? HH? without an AM/PM?? Really?
Not knowing what DT is, or what DT looks like - I cannot comment on this except to say that the date mask you are using looks very very very suspicious.
And it is not likely to give the same value for every row - unless every row started with the same value.
Give a create table
Give inserts
Show us what you get
Tell us what and why you expected something else.
Get varchar datatype into time
sanjay, June 02, 2011 - 2:10 am UTC
to get varchar datatype into time :
Select To_Char(To_Date(DT,'DD-MM-YY HH:MI:SS'),'DD-MM-YY HH:MI:SS') from DTCONV
This query is not giving exact time of entry into database.
it is giving same time for all
Thanks in advance....
How to get latest entry value from database in oracle sql
sanjay, June 02, 2011 - 2:12 am UTC
i have used max function in query but it is giving largest entry from table..
is there any way to find latest entry value from database in oracle sql
Thanks in advance
June 02, 2011 - 9:06 am UTC
you'll really have to be a bit more clear. I have no idea what you are trying to do, and the prior two posts by you only muddy the waters even more.
How to get latest entry value from database in oracle sql
sanjay pandey, June 04, 2011 - 2:59 pm UTC
using MAX() function, it fetch only largest value from table not latest value.
but my question is how to get latest value from table ...
is there any function to get latest value from a table??
June 06, 2011 - 9:23 am UTC
define what the "latest value" is or means?
I'm not sure what you are looking for here.
How to get latest entry value from database in oracle sql
sanjay, June 07, 2011 - 10:46 pm UTC
the below script is for list of jobs are placed on hold last 3 hour by ops for current date.
Required result:-
while running query , it is giving the no. of held time for jobs are held by ops last 3 hour but my question is to get only one latest entry of held time for each job in a table instead of getting no. of held time for each job for current date
Sript:-
select b.filename, b.jobDATE, to_char(to_date(logdate || logtime, 'YYYY-MM-DD HH24:MI:SS'), 'DD-MM-YY HH24:MI:SS') Held_Time,
TRUNC (MOD ((SYSDATE - TO_DATE (MAX (logdate) || MAX (logtime),'YYYY-MM-DD HH24:MI:SS')) * 24,24)) HR,
from cmr a, cms b
where message = 'HELD BY OPS' and
trunc(sysdate) = trunc(to_date(logdate || logtime, 'YYYY-MM-DD HH24:MI:SS'))
and a.orderno=b.orderno
group by b.filename, b.jobDATE,a.logdate, a.logtime
having TRUNC (MOD ((SYSDATE - TO_DATE (MAX (logdate) || MAX (logtime),'YYYY-MM-DD HH24:MI:SS')) * 24,24)) <= 3
June 08, 2011 - 10:34 am UTC
that sql statement is botched.
HH24:MI:SS')) * 24,24)) HR,
from cmr a, cms b
there is something missing there - also I see no count?
This is as clear as mud right now.
a) that is not a list of jobs that are placed on hold for last 3 hours. That is an aggregation by filename, jobdate, logdate and lobtime. It is a LESS THAN full list.
b) it is not giving the number of held jobs.
I don't know what it would mean to "get only one latest entry of held time for each job"
What defines a job.
You better supply
a create table
inserts into table (of meaningful sample data)
if you want me to even look at this. You cannot really write a query without something to test against.
How to get latest entry value from database in oracle sql
sanjay, June 09, 2011 - 2:31 pm UTC
if i am running above SQL query it is giving output like below:
FILENAME JOBDATE HELD_TIME HR
OraclePM_job 20110609 9/6/2011 12:17:55 PM 2
OraclePM_job 20110609 9/6/2011 12:17:05 PM 2
PS_PR_Job 20110609 9/6/2011 11:08:36 AM 3
in this output, i am getting two held time for same job "OraclePM_job" . my question is to get latest held time means only 12:17:55 held time i want to get instead of getting two held time for job "OraclePM_job".Can we filter it to get latest held time...Could you please help me out to get latest held time for specific job. Thanks ...
June 09, 2011 - 4:39 pm UTC
ignoring this entirely, just as you ignored me entirely.
Did you even read what I wrote? All of the way through?
this is trivial, I can do it in my sleep. But I won't, not until you actually read what I wrote and provide what I asked for.
be clear
Venkata, June 10, 2011 - 1:42 am UTC
Dear Sanjay,
Help Tom by providing the following so that he can help YOU
1. a create table statement / a table structure
2. an insert into statement with values to populate/ a select statement which will dissplay the data from your table(s)
and finally what you are doing, a select statement, and what is the error you are getting. this will only help TOM to help you in a better way.
be specific with examples or table structures and queries you are using
Que
Sandip, October 11, 2011 - 2:27 am UTC
i have Query
number varchar2(50)
select number from dual order by number
it gives result like
1
10
2
3
4
5
6
7
8
9
October 11, 2011 - 8:10 am UTC
great, it did it perfectly.
You have successfully sorted a bunch of strings correctly.
did you have a question? if the question is "how to sort these as numbers", order by to_number( string ) (and hope like all get out that you don't have any non-numeric strings in there - how I hate it when people use strings to try and store numbers or dates... :( )
string to date
xton, April 12, 2019 - 5:02 pm UTC
Hi
i have date with time stored as string (030619213000), how can i convert back to date and time like Mar-06-19 09:30:00 PM
April 15, 2019 - 4:32 am UTC
to_date('030619213000',mmddrrhh24miss')
And thanks for the 1-star rating :-(
How do I convert 'Jan 27 2016 9:45AM' to a date format?
Ani, August 15, 2019 - 2:04 pm UTC
August 15, 2019 - 2:29 pm UTC
The following formats will help you:
Jan => Mon
27 => DD
2016 => YYYY
9:45 => HH:MI
AM => AM