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


ADVERTISEMENT

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

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

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 :: 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

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 Date Into UNIX Timestamp

Dec 17, 2008

how to convert date into UNIX time in oracle ?

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

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

Jun 12, 2011

I have a string in this format '2011/06/01 00:00:00'. How do I convert this to date format.I tried to_date,to_char and they give errors invalid number & literal does not math format string. I don't have much control on the string since that is the way is comes from the application.

View 3 Replies View Related

SQL & PL/SQL :: Convert Varchar2 To Date Or Number

Jun 18, 2011

I created table Contains then following columns

cheq varchar2(50)
date_due varchar2(50)

and data entry in this columns

cheq 500,1500,5000 all values numbers in this columns

date_due 1-1-2012 , 15-9-2010 all values in this columns date

i want sum the column "cheq"

when used this code but it's not working

select sum(to_number(cheq))
from table_name but the code not working

second column "date_due"

i want search between to date i used this code but also not working

select cloumn1,cloum2
from table_name
where to_char(date_due,'dd-mm-yyyy')
between to_char(date_due,'dd-mm-yyyy') and
(date_due,'dd-mm-yyyy')

but not work

View 13 Replies View Related

PL/SQL :: How To Convert Number (15 Characters) To Date

Jun 23, 2013

One table ACCOUNT_T.CREATED_T is number (38). It is mapped to /ACCOUNT's PIN_FLD_CREATED_T, which is timestamp. ACCOUNT_T table has one record, CREATED_T = 1362063600. This field is displayed at the GUI (Customer Center) as Feb 28, 2013 So my question, how to convert the number value (1362063600) to a date value (Feb 28, 2013)?

View 4 Replies View Related

SQL & PL/SQL :: Convert VARCHAR2 To Date Data Type

Jun 13, 2012

I have problem to convert simply varchar to date data type.

For example: 2012-05-28 22:36:08 and I would like to get format 28.5.2012 22:36:08

However I try to do it I get always some errors.

select '2012-05-28 22:36:08',
to_date( '2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS') ,
to_char('2012-05-28 22:36:08', 'dd.mm.yyyy HH24:MI:SS')
from dual

View 7 Replies View Related

SQL & PL/SQL :: Convert A String In Date Obtaining Just A Year?

Nov 12, 2012

basically I have to insert in a date field a string that is a year(four characters).

The problem is that I have to convert a string in date obtaining just a year. How can i do this?

View 14 Replies View Related

SQL & PL/SQL :: Convert Date Format To Other (International) Timings

Oct 31, 2011

the below request.

My company has many products, whenever customer purchase any product purchase timestamp should be inserted into product_details table .

Note: timestamp of UK, Philippines, Singapore, India time should be inserted automatically as my company is world based company .

I have tried to convert the date format to other(uk,singpore,manalia) timings but i couldn't get proper reply.

SELECT to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM') INDIA,
to_char(new_time(sysdate, 'EST', 'GMT'),'DD-MON-YYYY HH:MI:SS AM') D2 ,
to_char(new_time(sysdate, 'PST', 'GMT'),'DD-MON-YYYY HH:MI:SS AM') D3 ,
to_char(new_time(sysdate, 'EST', 'PST'),'DD-MON-YYYY HH:MI:SS AM') D4
FROM dual

I got the below output.

INDIA D2 D3 D4
----------------------- ----------------------- ----------------------- -----------------------
31-OCT-2011 06:06:16 PM 31-OCT-2011 11:06:16 PM 01-NOV-2011 02:06:16 AM 31-OCT-2011 03:06:16 PM

View 9 Replies View Related

Forms :: Convert It Into Standard Date Format?

Jun 3, 2010

i have create one standard Calender from that i pickup month date and year separately like 2/6/1987 now i want to convert it into standard date format how to convert it and pass to another block....

View 3 Replies View Related

PL/SQL :: Convert Date - Persian To Georgian Calendar

Oct 20, 2013

How  to set date persian to georgian calender.

View 1 Replies View Related

SQL & PL/SQL :: Date Value Should Convert Into Local Time Zone?

Mar 6, 2012

I have a UI which is java and database in oracle 10g and database resides in India. Now the user use this application across the world. Date related value stores in IST format.

Now the requirement is whenever any user open the application in USA ,then date value should convert into their local time zone. So is there any way in oracle to convert and show the date value according to their local time zone.

View 12 Replies View Related

SQL & PL/SQL :: Convert As Procedure Based On Input Date Parameter

Dec 12, 2012

i have the below query

select to_char(report_date, 'YYYY MM Mon'), count(1) no_of_times
from (
select to_date('&&YYYYMMDD', 'YYYYMMDD')+rownum report_date
, mod(rownum,14) mod_result
from all_objects

[code]...

need to convert as procedure based on input date parameter.I will pass the input date from java environment and need to see the sql query output in front end.

View 7 Replies View Related

SQL & PL/SQL :: Convert Year / Quarter Number To Date Format

Oct 5, 2010

I have year/quarter number field (200903 3-rd quarters of 2009) and I need to convert to data format.

View 5 Replies View Related

Convert DATE To EPOCH Time Taking Care Of Daylight Savings?

Nov 8, 2012

How can I convert a DATE to EPOCH time taking care of daylight savings?

I tried with this code but there is a difference of 36000 seconds. eg:Sysdate_To_Epoch('04-Sep-2012') gives 1346716800 whereas it should give 1346680800.

CREATE OR REPLACE FUNCTION Sysdate_To_Epoch(v_date IN DATE)
RETURN NUMBER
IS

[Code]....

View 1 Replies View Related

SQL & PL/SQL :: How To Get Max (value) With Its Corresponding Datetime

Sep 22, 2013

This is my table

ID timestamp value
1 2013-09-09 01:09:00.000 1234
2 2013-09-09 02:00:00.000 123
1 2013-09-09 03:09:00.000 1233
2 2013-09-09 21:09:00.000 125

I need to find max(value) with its corresponding time stamp .. this table has approximately 500000 records with 180 distinct IDs. Need to find max(value) group by IDs.

Expected result:

ID timestamp value
1 2013-09-09 01:09:00.000 1234
2 2013-09-09 21:09:00.000 125

We have a query but its returns 00 in hh:mm:ss instead of exact timestamp.

View 15 Replies View Related

SQL & PL/SQL :: How To Get The Time From Datetime

Oct 12, 2012

i am using one stored procedure there in one variable value is coming like this: Jan 1 1900 6:00AM

from this i want time how to get the time from that.

View 3 Replies View Related

SQL & PL/SQL :: Set Datetime Globally

Jun 23, 2011

I am facing problem to set date & time globally in oracle. I set date-time many time but it is on session base.

View 28 Replies View Related

Forms :: Datetime Format

Mar 31, 2011

I have written the following assignment statement. :dshift_hdr.startdate := to_date(sysdate,'DD-MM-RR HH12:MI:SS');

the startdate is a display item. Its data type property has been set to datetime and the formatmask is DD-MM-RRRR HH12:MI:SS but the result of above assignment statement is 31-03-2011 12:00:00 i.e. it does not take the actual current time.

i have promised to install the application to the client tommorrow.

View 3 Replies View Related







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