SQL & PL/SQL :: Getting Date Portion Of Timestamp Field?
May 19, 2011
I have a timestamp field in an oracle table. The data in that field looks like this.
19-MAY-2011 10.55.21.628206000 AM
I want to query the data in this field by only date portion (not the time portion).
Something like this.
Select * from mytable where archivedate = to_date('19-may-2011','dd-mon-yyyy')
this query doesn't return any data. But actually there is data for 19-may-2011 (along with time portion) in that field.
how to query based on only date portion?
View 4 Replies
ADVERTISEMENT
Apr 17, 2013
I have table named purchage with 2 columns (order_no number,order_date date) in my database. I want to load the data from a file into that table. The below is the file format
100,4/3/2013 1:18:18 AM
101,4/3/2013 1:18:18 AM
102,4/3/2013 1:18:18 AM
103,4/3/2013 1:18:18 AM
104,4/3/2013 1:18:18 AM
105,4/3/2013 1:18:18 AM
106,4/3/2013 1:18:18 AM
how to load the date filed along with the time stamp.
View 2 Replies
View Related
Aug 30, 2012
I am trying to run a query that will count the number of each occurrence of a combined month and day. Birthdays are formatted as: 1947-07-26 00:00:00
I need to query everyone in the system who share the same month & day, but not year & time. For example:
1927-07-26 00:00:00
1927-07-26 00:00:00
1987-07-26 00:00:00
1965-07-26 00:00:00
1981-03-14 00:00:00
would give me a Count of 4 for 7/26 and a Count of 1 for 3/14
View 5 Replies
View Related
Mar 24, 2011
I am trying to insert values in the timestamp field
system@id.world> create table t(t1 timestamp,t2 timestamp, t3 timestamp, t4 date);
Table created.
system@id.world> insert into t values(timestamp'2011-03-24 11:03:00.05','12-mar-2011 11.03.00.055',systimestamp,localtimestamp);
1 row created.
system@id.world> insert into t values(timestamp'2011-03-24 14:03:00.05','12-mar-2011 14.03.00.055',systimestamp,localtimestamp);
1 row created.
system@id.world> select t1 from t;
T1
---------------------------------------------------------------------------
24-MAR-11 11.03.00.050000
24-MAR-11 14.03.00.050000
system@id.world> select t2 from t;
T2
---------------------------------------------------------------------------
12-MAR-11 11.03.00.055000
12-MAR-11 14.03.00.055000
system@id.world> select t3 from t;
T3
---------------------------------------------------------------------------
24-MAR-11 11.29.04.491927
24-MAR-11 11.29.17.085396
system@id.world> select t4 from t;
T4
---------
24-MAR-11
24-MAR-11
system@id.world> select * from nls_instance_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
[Code]...
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
17 rows selected.
system@id.world> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_TERRITORY AMERICA
NLS_CURRENCY $
[Code]...
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
[Code]...
20 rows selected.
system@id.world> show parameter nls_timestamp_fo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_timestamp_format string
system@id.world> create table newt(t1 timestamp);
Table created.
system@id.world> select localtimestamp from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
24-MAR-11 11.31.07.667296
system@id.world> insert into newt values('12-jan-2010 11:00:00:068801');
insert into newt values('12-jan-2010 11:00:00:068801')
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
system@id.world> insert into newt values('12-jan-2010 11.00.00.061');
1 row created.
system@id.world> insert into newt values('12-jan-2010 15.00.00.061');
1 row created.
system@id.world> select * from newt;
T1
---------------------------------------------------------------------------
12-JAN-10 11.00.00.061000
12-JAN-10 15.00.00.061000
system@id.world>
My questions are
1) what is significance of '.' here? insert into t values(timestamp'2011-03-24 11:03:00.05','12-mar-2011 11.03.00.055',systimestamp,localtimestamp);
2) while inserting using "values(timestamp'2011-03-24 11:03:00.05'" where this yyyy-mm-dd hh24:mi:ss.FF format comes from?
3)what is role of nls_timestamp_format of nls_database_parameters in this? how it allowed me to insert value "'12-mar-2011 14.03.00.055'" which has hh24 format?
4) Does the format of localtimestamp is decided by nls_database_parameters?
Note : I have not set nls_date_format in my session
I tried to read the link below but could not understand above
[URL]....
View 1 Replies
View Related
Jul 26, 2011
From ETL to Oracle, I have stored the timestamp in varchar2 as '30-MAY-11 06.30.00.000000 PM'. Now I need to convert this varchar2(timestamp format) to date. I used:
select TRUNC('30-MAY-11 06.30.00.000000 PM','dd/mm/yyyy hh24:mi') from dualBut, it doest work. get date format?
View 7 Replies
View Related
Jan 18, 2012
How to extract date from a timestamp data
for example
Input-15/06/2010 05:30:00.000000
output: 15/06/2010
View 3 Replies
View Related
Sep 11, 2009
I get data from one table into a gridview and one of the columns is a date. I get it with date and time stamp but I want solely the date in my gridview.
This is what I get: 2009-08-04 00:00:00
This is what I want: 2009-08-04
View 3 Replies
View Related
Dec 17, 2008
how to convert date into UNIX time in oracle ?
View 1 Replies
View Related
Apr 10, 2013
I want to write a query to get the time stamp from only one date column,
I tried using a group by clause but getting error "not a group by exp."
Below is the query
SELECT ProdID,ProdRequestID, SUBSTR((max(EVENTTIMESTAMP) - min(EVENTTIMESTAMP)), 18,2)Execution_Time
FROM LOG_TIMESTAMPS where ProdID = 1680988889
group by ProdRequestID
ProdID||ProdRequestID ||EVENTTIMESTAMP
[code]....
In the above i am looking for a diference on ProdRequestId,
Output
ProdIDProdRequestIDEVENTTIMESTAMP
16809888892013-04-09T02-56-34.5025kqcxy03
View 24 Replies
View Related
Oct 13, 2011
How can i get just date from the timestamp data type.
Suppose i have a column timestamp with has data like "2011-05-16 16:19:22.579764-07" when i select from table i just want the date like 2011-05-16.
View 6 Replies
View Related
Jun 17, 2012
how to extract DATE from timestamp column
View 2 Replies
View Related
Oct 19, 2010
How can i separate the Date and time into two separate fields from the Oracle TIMESTAMP?
View 1 Replies
View Related
Feb 3, 2013
I want to retrieve the data which is "n" moths old.
To compare that I want use only SYSDATE without timestamp. I want to use standard timestamp '23:59:59' along with SYSDATE.
The condition should be as below.
UPDATE_DATE <= ADD_MONTHS(15/01/2013 23:59:59,-3)
UPDATE_DATE <= ADD_MOTHS(30/01/2013 23:59:59,-4)
UPDATE_DATE<=ADD_MONTHS(sysdate||' '||'23:59:59',-3);
View 6 Replies
View Related
Nov 14, 2011
I have a table called Customer_Type with following fields
Customer_type ,Active_date, Inactive_date
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011
Tbale 2:Customer
Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
The sql statements are below
CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),
[Code]....
View 5 Replies
View Related
Oct 5, 2010
Due to some business requirements a table field needs to change from date to timestamp in order to handle the millisecs.
1>When i alter the row , for a table with 150 million recs will there be a conversion. Is there a recommended way to convert the field. Mind you this field is used as a part of composite PK.
2> There is a interfacing application which connects and copies the data to its system and is using the date type, will that application be able to continue to work without any changes, if it does not care about the millisecs.
3> Will there be performance impact on an existing application that uses the date field to sort
4> Will DB need more space due to the change
View 3 Replies
View Related
Dec 3, 2012
Version : 4.1.1, I have a tabular form on a DB table. One of the columns is a date field. When the user hits the "add Row" button on the tabular form, I want the Date field to be defaulted to sysdate. Here is what I have tried so far,
1. Created a "hidden" item P1_SYSDATE and populated the default value with sysdate. After this, under the DB tabular report date field, I used default type - Item/application on this page and entered P1_sYSDATE
2. Instead of populating the default value of the P1_SYSDATE hidden item, I created a before regions process and added
:P1_SYSDATE := sysdate
and added P1_SYSDATE to default type of the tabular date field with default type as "ITem/application on this page.
I get the error
ORA-01790: expression must have same datatype as corresponding expression
I tried to_Char(sysdate,'dd-mon-yyyy') and then converting it back to to_date. still no luck.
View 1 Replies
View Related
May 17, 2011
I have a column where I have time stamp with date and time(8-May-10 10:20:55 Am) from that i will get only time(10:20:55 Am)[query for this mentioned below] Now i need to append the time to current date and insert into a Timestamps column only .
so my result should be 17-May-11 10:20:55 Am(Assuming current Date is 17-May-11) . I tried search it by i found a way in sql server by using DataAdd and DataDiff .
query to get the time is:select to_char(to_date('11-MAY-11 05.00.00 PM', 'DD-Mon-YY HH:MI:SSAM'),'HH24:MI:SS') "Time Now" from dual
View 1 Replies
View Related
Apr 11, 2013
I have a small prolem thats best described like this....
a table called TONY with a field named VISITED (date as YYYYMMDD).
We want to populate the field TIMESTAMP (Last visited timestamp, 18 digits) using midnight or 00:00:00 on VISITED value.
Something like:
UPDATE TONY SET TIMESTAMP = �(whatever the formula is involving VISITED).
but i cannot figure out the best way to derive the TIMESTAMP value...
it's a date to epoch conversion, and i can find many examples of Epoch to date, but thats the wrong way around for me i'm afraid!
Oracle 11gR2 by the way...
View 4 Replies
View Related
Oct 3, 2012
A field named xxx_date is a text item which we have to enter manually so as to update a record in that particular date. This is a mandatory field without which we cannot continue the data entry..
I am getting this error while trying to update the record
FRM-40509 :Oracle error :unable to update record
I have kept the enabled = yes
required=no
data type=Date.. in the property pallet
View 2 Replies
View Related
Sep 24, 2011
I have a date field that should be filled everyday with today's date and I need to get the days that were not entered.
i.e. :
CREATE TABLE TRY_F (DAT DATE);
INSERT ALL
INTO TRY_F VALUES (to_date('01/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('02/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('04/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('05/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('06/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('08/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('10/01/2011','DD/MM/YYYY'))
INTO TRY_F VALUES (to_date('14/01/2011','DD/MM/YYYY'))
SELECT * FROM DUAL;
I need a smart way of getting the dates that were missed in DAT.
View 4 Replies
View Related
May 21, 2013
I am having problems with the XMLTable function. I cant get it to see the entire date/time value in a date field. This wont work
select x1.* from XMLTABLE('/DOCUMENT' passing xmltype('<DOCUMENT><STR>abc def ghi</STR><NUM>1234</NUM><DT>2013-02-17T04:24:02</DT></DOCUMENT>') columns STR varchar2(25), NUM number, DT date) x1;
However if I change the DT tag to just the date only "2013-02-17" it works. Why wont Oracle see the entire date/time format even if its ISO 8601 compliant?
Oracle DB: 11.2.0.3.0
View 3 Replies
View Related
Feb 21, 2012
Using Oracle SQL Developer 2.1.1.64 to run the queries & Oracle 11g.
I have two numbers in two colomns of an oracle table(colomn a & colomn b). I am trying to divide colomn a/colomn b and putting the results in colomn c & also in colomn d (all in the same table) using update commands
Eg: UPDATE MTOTABLE_PWELD pw SET pw.WELDO=(pw.pipe_length/12000);
But here is the real issue. In colomn d I only need the integer portion of the division value.
For example , when I divide colomn a/colomn b , let us assume that we are getting a value of 2.56. Then I want the value of 2 to go to colomn d.
I tried round((colomn a/colomn b),0). But it rounds off 2.56 to 3. I dont want that. I need the exact integer portion of the value to be seperated.
View 2 Replies
View Related
May 30, 2012
What is the difference between the following . In my schema all are giving the same results with some different format
SQL> SELECT sysdate , current_date , current_timestamp , localtimestamp from dual;
SYSDATE CURRENT_DATE CURRENT_TIMESTAMP LOCALTIMESTAMP
----------- ------------ ------------------------------------------------- -------------------------------------------------
5/30/2012 8 5/30/2012 8: 30-MAY-12 08.27.22.037703 AM -04:00 30-MAY-12 08.27.22.037703 AM
View 1 Replies
View Related
Jun 11, 2010
I want to force a datetime field to display as date only. How can I do this? This is so when prompting for a value for this field a user doesn't have to also enter the time. At the moment the prompt returns nothing when entering only a date as it does not match any value as they all have times also.
View 12 Replies
View Related
Oct 18, 2010
I have a table and below are the rows:
create table employee_rev(employee_id number, month_name varchar2(10), month_end date, financial_year date, revenue_amt number)
Begin
insert into employee_rev values(111, 'DEC-09', '31-DEC-2009', '01-APR-2009', 1300);
insert into employee_rev values(111, 'JAN-10', '31-JAN-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'FEB-10', '28-FEB-2010', '01-APR-2009', 800);
insert into employee_rev values(111, 'MAR-10', '31-MAR-2010', '01-APR-2009', 1000);
insert into employee_rev values(111, 'APR-10', '30-APR-2010', '01-APR-2010', 1000);
insert into employee_rev values(111, 'MAY-10', '31-MAY-2010', '01-APR-2010', 1100);
insert into employee_rev values(111, 'JUN-10', '30-JUN-2010', '01-APR-2010', 2100);
End;
I also need a YTD (Year to Date) field at the last which is sum of current month revenue_amt and sum(revenue_amt) for previous months for a particular financial_year_date.
The output should be:
Person_ID Month End Date Year Date Rev YTD
111 Dec-09 31-Dec-09 1-Apr-09 1300 1300
111 Jan-10 31-Jan-10 1-Apr-09 1000 2300
111 Feb-10 28-Feb-10 1-Apr-09 800 3100
111 Mar-10 31-Mar-10 1-Apr-09 1000 4100
111 Apr-10 30-Apr-10 1-Apr-10 1000 1000--change in financial year
111 May-10 31-May-10 1-Apr-10 1100 2100
111 Jun-10 30-Jun-10 1-Apr-10 2100 4200
I guess this should be achievable using some analytical functions, but I am unable to get the desired output.
View 7 Replies
View Related
Mar 23, 2011
I'm trying to return a field in a date format, but my minutes keep returning as 03. See example below:
TO_CHAR (i.editdate, 'mm/dd/yyyy HH24:MM:SS') AS "Date",
Actual data field in the table contains:
10/27/2010 1:07:42 PM
Data returned is:
10/27/2010 13:03:42
Why are the minutes incorrect?
View 10 Replies
View Related
Jan 25, 2012
I have a field (called Date_Time) which displays for example 1/31/2005 12:00:00 AM. I would like to run a query that converts that value to '200501' in a created field.
I tried the following below but I keep having problems.
select Account_Number, Date_Time,
concat(year(Date_Time), month(Date_Time)) as Date_Time_Modified
from table
where Account_Number = xxxx
View 5 Replies
View Related
Feb 16, 2010
I have a display item form field. The field's data type is date. It is also a database field that corresponds to a date field in the table.
I have set the format mask on the form field to
MM/DD/YYYY HH24:MI:SS
I can populate that field with sysdate in when-new-form-instance.
However, if I try to get the date and the time in the field, it just populates the date with the time of 00:00:00.
(02/16/2010 00:00:00 for example).
I have tried several to_char and to_date variations but can't seem to get the current time into that field.
View 5 Replies
View Related
Oct 9, 2012
In an Application Procedure, I'd like to reference some items that are local to the current page. Obviously, hard-coding like the following won't work unless it happens to be on Page 25.
:f105_blah := :p25_blah;
So how can I write this line of PL/SQL code so that the "25" is automatically set to the number of the current page?
View 13 Replies
View Related
Jan 14, 2012
I have a field " Tran_date " with datatype Date . It contains date as well as time . How can I get the time . I tried with it as below :
SELECT TO_CHAR(TRAN_DATE,'DD-MON-YYYY HH24:MI:SS') FROM ABC
WHERE COMP=1 AND
BRANCH=1 AND
LOC_CODE='12' AND
TRAN_DATE='12-JAN-2012' ;
VALUE IS COMING LIKE THIS :
12-JAN-2012 00:00:00
ALTHOUGH THERE IS TIME . Its not zero .
View 3 Replies
View Related
