Convert Date Into UNIX Timestamp

Dec 17, 2008

how to convert date into UNIX time in oracle ?

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Pivot Table Using UNIX Timestamp

Feb 23, 2010

I am trying to produce results in pivot format. I want to know how many entries per day per month. In other words how many entries on a Monday for each month, how many on a Tuesday for each month and so on.

The main problem I have is that the date column in the database uses UNIX time stamp. I have managed to get the results I want for a particular month. This code selects the entries for last month.

select
max(decode(pdayno, 1, cnt, null)) Sunday,
max(decode(pdayno, 2, cnt, null)) Monday,
max(decode(pdayno, 3, cnt, null)) Tuesday,
max(decode(pdayno, 4, cnt, null)) Wednesday,
[code].......

This code produces the result:

What I need is for another column at the start of the results to give the month so I end up with 12 rows, 1 for each month.

I did try to group by PENTERED(which is the unix time stamp column)
select pentered,
max(decode(pdayno, 1, cnt, null)) Sunday,
max(decode(pdayno, 2, cnt, null)) Monday,
max(decode(pdayno, 3, cnt, null)) Tuesday,
max(decode(pdayno, 4, cnt, null)) Wednesday,
[code]......

This gave me thousands of rows as each UNIX time stamp is unique. Is there a way of grouping on UNIX time stamp.

View 4 Replies View Related

SQL & PL/SQL :: Timestamp To Date

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

SQL & PL/SQL :: Date Conversion From Unix To Oracle

Jun 14, 2010

I have a UNIX shell script as following,

#runs the selected process
${ORACLE_HOME}/bin/sqlplus -s ${USER_PASS} >${TMP_FILE} <<EOF
set pause off
set verify off
set pagesize 0
set linesize 2000
set timing off
[code].........

I am passing a Date to the Oracle Procedure in `date +'%b_%d-%H:%M:%S'` format.

rmsqa813.2 /home/ashlaksh> echo `date +'%b_%d-%H:%M:%S'`
Jun_14-02:26:37

The oracle Procedure's SQL is as following,

SELECT ffdh.fiscal_doc_id INVOICE_ID,
ffdh.location_id LOCATION_ID
FROM fm_fiscal_doc_header ffdh,
fm_schedule fs
[code]........

The above SQL is not getting the data as the Date format "AND ffdh.last_update_datetime <=i_last_update_datetime " is not matching..

Do I need to convert the Date ? ( But i_last_update_datetime is DATE)

View 1 Replies View Related

SQL & PL/SQL :: Extracting Date From Timestamp?

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

How To Get Shorter Date Format (without Timestamp)

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

SQL & PL/SQL :: Query To Get Timestamp From Only One Date Column?

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

SQL & PL/SQL :: How To Get Date From The Timestamp Data Type

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

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 View Related

Extract Date From Timestamp Column?

Jun 17, 2012

how to extract DATE from timestamp column

View 2 Replies View Related

SQL & PL/SQL :: Separating Time And Date Form TimeStamp

Oct 19, 2010

How can i separate the Date and time into two separate fields from the Oracle TIMESTAMP?

View 1 Replies View Related

PL/SQL :: Logic To Compare Date With Standard Timestamp

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

Date To Timestamp Conversion Of Column With 150 Million Rows?

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

How To Convert GMT Date To Local Time Date

Sep 16, 2011

I need to convert the type of GMT to Local time, during data entry. I have a source table and a target table.

To make the insert get the most value, I have no problems:

insert into destination (SELECT * FROM WHERE source pointnumber = (SELECT MAX (pointnumber) FROM source));

But since times are different between the tables, I want to convert the data obtained to GMT -4:30 Time (Caracas - Venezuela), before inserting it.

I can use a function?

View 1 Replies View Related

Adding Time Part To Current Date To Form Timestamp

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

Server Utilities :: How To Load Date Field Along With Timestamp Using Sqlldr

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

Unix Command Run Successfully Through PL/SQL But Showing No Impact In UNIX?

May 19, 2007

I am using Oracle 9i and Unix on my system and trying to execute a UNIX shell command through external procedure in C.I created a shared lib (libextproc.so) for the following function.

int sysrun(char *command)
{
return system(command);
}

This function runs fine when caled through a driver function in C, meaning that the shared lib is fine.In PL/SQL, I have used the following method to invoke a UNIX command:-
create or replace library shell_lib as '/home/ECETRAonsite/oracle/OraHome1/lib/libextproc.so';
/
create or replace function sysrun (syscomm in varchar2)
return binary_integer
as language C
name "sysrun"
library shell_lib
parameters(syscomm string);
/

Now when I call this PL/SQL function to invoke the command, it is run succesfully but does not create the file.

SQL>
1 declare
2 rc number;
3 begin
4 rc := sysrun('/bin/touch /home/ECETRAonsite/oracle/OraHome1/test/sach');
5 dbms_output.put_line('Return Code='||rc);
6* end;
SQL> /
Return Code=0

PL/SQL procedure successfully completed.I have verified that the path for 'touch' is correct.Following are my configuration files.
listener.ora
-------------
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =

[code]...

View 1 Replies View Related

How To Convert String To Date

Apr 15, 2007

I have a date saved in varchar2 colomn.

how can I convert the vaules into date, so I can use the date to compare data.

Ex: I have the value '20-03-2007 05:31:29', but this value is saved as varchar2.

how can I take from this value the date '20-03-2007' as date format?

View 6 Replies View Related

SQL & PL/SQL :: Convert To Numeric / Date

Jan 30, 2013

I currently have a table with a VARCHAR column which is used to store notes. Currently the notes read something like 'Verified 01/01/2012'. I am trying to convert it to a date column so I can run reports using the date (select between dates etc).

I have tried with the substr function but since the records are all different doesn't really work. (plus that doesn't make it a date so not sure it would work for searching).

how to proceed?

View 3 Replies View Related

SQL & PL/SQL :: Convert Datetime To Date?

Nov 8, 2012

I know this question has been asked several times. but i am starting out and i am struggling to get my head aroung it.I would like to convert datetime column of Oracle source(RPT.SHIPMENT_VW) to date while loading to sql Here is my

SELECT SHIPMENT_NBR,RECEIVED_DATE_TIME, RCVD_AT_DATE_GMT_FK
FROM RPT.SHIPMENT_VW
where WHERE RECEIVED_DATE_TIME = TO_DATE(TO_CHAR(:fromdate, 'DD/MM/YYYY'))
here RECEIVED_DATE_TIME is in 'DD/MM/YY hh:mi:si' fromat which, i want to convert to 'DD/MM/YY'
the above code throwing an error ORA:01843 not a valid month

if I use where clause like:WHERE (RECEIVED_DATE_TIME = TO_DATE(TO_CHAR(:fromdate, 'DD/MM/YYYY'), 'DD/MM/YY'))then its not retrieving any data

View 6 Replies View Related

SQL & PL/SQL :: How To Convert Number To Date

Oct 28, 2011

how can convert this number 00001021992 to this format

1-02-1992

i used thie query but no result

select substr(to_date('00001021992','dd-mm-yyyy'),(6,6)) from dual;

View 6 Replies View Related

SQL & PL/SQL :: Convert Date To Numeric

Oct 8, 2012

I currently have a field which stores a date (date employer started with the company). I want to create a view which stores this as number of days employed (e.g. if star date 1st October view will say 8). How to convert a date to a number?

View 14 Replies View Related

SQL & PL/SQL :: Convert YYYYQ To DATE?

Jun 20, 2012

I have an requirement as follows.

I have an column QTR_DATE which contains data like below

20123
20111

Which means YYYYQ ( Year with Quarter) and i need to get date of first SUNDAY of the quarter. Example

20123 has to be converted to 20120701 which is 2012-July-01.

query to getting it without using any functions/proc?

View 5 Replies View Related

PL/SQL :: How To Convert / Truncate Date

May 30, 2013

How to convert/trunc date

Example
source
column name CONFIRMED_DTE
16-APR-09 12.00.00.000000000 AM

target
column name CONFIRMED_DTE
16-APR-09

I want to date like 16-APR-09 from 16-APR-09 12.00.00.000000000 AM. In a columns have null values and 16-APR-09 in source and target

View 8 Replies View Related

SQL & PL/SQL :: To Convert Date Into Particular Format

Oct 18, 2012

I want to convert the date in to a particular format,

1)
2011-03-04T01:45:12
I want to convert the above date value into the below format.
04-MAR-11 01.45.12.000000000 AM

2)
2011-03-04T01:45:12+0006
The above date format into
04-MAR-11 01.45.12.0006 AM

View 5 Replies View Related

SQL & PL/SQL :: Difference Between Sysdate / Current Date / Current And Local Timestamp

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

DATE-ADD In Oracle - Convert Functionality?

Oct 28, 2011

I need to convert below functionality in Oracle. At present it is working with SQL server

current_end_time TIMESTAMP,
current_start_time TIMESTAMP
current_duration int
current_end_time := DATEADD(second, current_duration/10, current_start_time)

View 1 Replies View Related

Convert DateTime Field To Date

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

SQL & PL/SQL :: How To Convert Date To Specific Format

Oct 11, 2012

i am using one stored procedure where in one variable which is declare as date value is coming like that '10-OCT-12 11.30.54 AM' and i am inserting this value in one table which has one column vdate with date datatype but it is not inserting there.

View 16 Replies View Related

SQL & PL/SQL :: Convert Date From MMDDCCYY To YYYYMMDD?

Dec 28, 2010

I have imported data from a fixed length format file into an external table in oracle which has several date fields in the format MMDDCCYY. I want to convert this into YYYYMMDD and insert it into another table in oracle with date field. A subset of the values to be converted is given below:

DOB
01051152
10581111
08151160
01031151
05131151
01111126

[code]....

View 16 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved