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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
Dec 17, 2008
how to convert date into UNIX time in oracle ?
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
Oct 20, 2013
How to set date persian to georgian calender.
View 1 Replies
View Related
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
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
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
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
Jun 1, 2010
I'm trying to work out how to take a table like this:
IDDate
12502-Feb-07
12516-Mar-07
12523-May-07
12524-May-07
12525-May-07
33302-Jan-09
33303-Jan-09
33304-Jan-09
33317-Mar-09
And display the data like this:
IDPeriodPeriod StartPeriod End
125102-Feb-0702-Feb-07
125216-Mar-0716-Mar-07
125323-May-0725-May-07
333102-Jan-0904-Jan-09
333217-Mar-0917-Mar-09
As you can see, it's split the entries into date ranges. If there is a 'lone' date, the 'period start' and the 'period end' are the same date.
View 13 Replies
View Related
Apr 12, 2010
I have a two date fields in my form; valid from date and expiry date.
Currently my valid from date has an inital value property of $$date$$ which automaitcally brings up todays date.
I need my expiry date to automatically show a date 15 years after this date?
View 8 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
Oct 24, 2013
When I run a query form the the Query Window in Visuial Studios 2012 all the date fields truncated to 'mm/dd/yyyy', but i need the full date returned. I am able to get full date from TO_char(MyDateField, 'yyyy-mm-dd hh24:mi:ss'), but if I do TO_DATE(MyDateField, 'yyyy-mm-dd hh24:mi:ss') it only returns 'mm/dd/yyyy'. I'm sure this is a simple setting in Visual studios but I cant find it to save my life. Is there there a way to have the full date returned by default?
View 0 Replies
View Related