Data In Date Format For In Time

Oct 26, 2006

i have data in date format for in time actually it should be 9:30 am and out time at 2:30 pm

SELECT MIN(IN_TIME) AS IN_TIME,MAX(OUT_TIME) AS OUT_TIME FROM EMP_ATND_DETAIL AS ET, EMP_ATND_INFO AS EAI
WHERE ET.EMP_ATND_INFO_ID=EAI.EMP_ATND_INFO_ID AND ATND_DATE = '2006-10-25' AND EMP_ID =24

+---------------------+---------------------+
| IN_TIME | OUT_TIME |
+---------------------+---------------------+
| 2006-10-20 09:30:00 | 2006-10-13 01:30:00 |
| 2006-10-24 02:30:00 | 2006-10-13 06:30:00 |
| 2006-10-24 09:30:00 | 2006-10-13 01:30:00 |
+---------------------+---------------------+

i have should get 9:30 am because that is lessser then 2:30 pm

do i have

SELECT name, emp_id,
min(to_char(IN_TIME, 'DD/MM/YY HH24:MI')) in_time,
max(to_char(OUT_TIME, 'DD/MM/YY HH24:MI')) out_time,
FROM EMP_ATND_DETAIL

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Get Data From A Table In Date And Time Format

Jul 24, 2013

how to get data from a Table in Date and Time format? Curently I'm using To_TIMESTAMP for a date range.

Here is the code Snippet -

Select *
FROM TABLE_Name
WHERE COUNTRY= 'IN'
AND CALC_DATETIME between TO_TIMESTAMP('2013/06/05', 'YYYY/MM/DD HH:MI:SS') AND TO_DATE ('2013/07/08', 'YYYY/MM/DD HH:MI:SS')

View 39 Replies View Related

PL/SQL :: Reset Date In Specific Format (Date / Time)

Dec 23, 2012

I want to reset my date to this format: 12/31/2012 11:59:59 PM - see code below:

DECLARE
v_latest_close DATE;
BEGIN
v_latest_close := TO_DATE ('12/31/2012 23:59:59 ','MM/DD/YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE('The new date format is : '|| v_latest_close);
END;

the code above displays only : 12/31/2012 instead of 12/31/2012 11:59:59 PM

View 4 Replies View Related

SQL & PL/SQL :: UTC Date Time Should Be In Format

Mar 1, 2010

iam having a table A where column ABC is a varchar field with varchar2(50).

currently data stored is like this.
02-27-2009 11:01:33
02-27-2009 11:01:46
03-06-2009 09:07:18

now i want UTC Date time should be in format 2010-02-24T17:08:09Z. in the above format.

View 9 Replies View Related

PL/SQL :: Date And Time Format

Dec 6, 2012

converting text values that I have to date and time format (without the millisecs and AM/PM values).

The following is an example:

04-DEC-12 07.47.58.000 AM

or would i need to change this in excel before importing this type of data.

View 6 Replies View Related

SQL & PL/SQL :: Date Time Format

Jan 31, 2013

I am running following statement

select TO_DATE( TO_CHAR(CAST((B.EOAWDTTM_MODIFIED) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') from PS_SR_EX_ER_AW_VW B where rownum < 5

and It gives me the error

ORA-01830: date format picture ends before converting entire input string 01830. 00000 - "date format picture ends before converting entire input string"

View 5 Replies View Related

SQL & PL/SQL :: To Store The Date And Time In GMT Format

May 13, 2010

Our data centers resides in different zones and we would like to store the date and time information in GMT format and it should aware of daylight savings.

Which data type is preferred ? Date or timestamp with timezone or timestamp with local time zone. Do we need to configure any parameter on the database?

View 1 Replies View Related

SQL & PL/SQL :: Date And Time Format Statement

Jan 11, 2012

I would like to do a select to get the current date&time in this format:

2011-10-04T04:20:35.2313103+01:00

do you know how can I do that?

I want to convert this sql statement in this format.

View 12 Replies View Related

Visual Studio :: Set Query Window Default Date Format To Full Date?

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

Forms :: Date Field Gets Disabled When Date Format Is Given?

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

JDeveloper, Java & XML :: Date Time Omits Time Part

Jun 4, 2012

When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.

If I try the same in SQLplus with to_date it works however fails in PL/SQL.

XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>

PL/SQL Extract:

CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......

View 3 Replies View Related

Server Utilities :: How To Get Date And Time In Date Column While Sqlldr

Jun 6, 2012

I am not able to load complete date along with time in the date column. here is my table desc

DESC STAGE
Name Null Type
----------------------------------
TABLE_NAME NOT NULL VARHCAR(20)
RECORDCOUNT NUMBER
CREATED_DATE NOT NULL DATE

my control file is like this

LOAD DATA
APPEND
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
( TABLE_NAME
,RECORDCOUNT
,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"
)

the data gets loaded, but it appears like this in the table
HIGHSCHOOL3080606-JUN-12
MIDDLESCHOOL8768006-JUN-12

BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)

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

PL/SQL :: Entire Date / Time Value In A Date Field

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

SQL & PL/SQL :: Average Of Time In HH:MM:SS Format

Jun 17, 2010

how to calculate the average of the time in th e HH:MM:SS format stored database table.. column contains hundreds of time values and need to table the avergae of it

my col look like,and column is declared as timestamp(6).

MY COL
------
1:13:00
1:06:00
0:43:00
0:47:00
0:32:00
0:19:00
0:39:00
0:46:00
0:56:00
1:39:00

[Code]...

View 29 Replies View Related

PL/SQL :: DATE Format?

Apr 19, 2013

In one of the query used like below is it correct or any need to be correct for date format? currently it's returning two tyoe of sets 1.NUll 2.01-JAN-00

SELECT withdrawn_date
  FROM
csd  where  NVL(csd.withdrawn_date,'01-JAN-1900') = '01-JAN-1900';

View 15 Replies View Related

SQL & PL/SQL :: How To Format Date

Feb 8, 2012

How do I format a date which is this format to 2/18/2012 to 18/2/2012 and still keep the field as Date.

View 3 Replies View Related

Know User Account Locked Time In HH:MM:SS Format?

May 30, 2011

How we know a user account locked time in HH:MM:SS format ?

View 1 Replies View Related

Time Format - Way To Change My Select To Creation

Jul 18, 2012

when I select I have to using AND creation_dt>='17-JUL-12' is there a way to change my select to creation_dt='17-JUL-12' ?
on meantime my local format is 18-JUL-12(18-JUL-12) is thee anything I can do?

View 5 Replies View Related

Date Format Like Dd / Mm / Yyyy

Sep 6, 2012

i see my dates in Oracle 11g like this: 2012-December-30 (in all my tables)

how to configure Oracle that i can see it like this: 30/12/2012

View 1 Replies View Related

How To Change Date Format

Nov 22, 2012

I have a simple question, hope it has a simple answer. I changed the default date format for a SCHEMA using a TRIGGER and AFTER LOGON, so it set the NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'. It does work fine, however, the problem is when I connect using a client with JDBC driver. When using SQL PLUS I get the date in the format specified above YYYY-MM-DD HH24:MI:SS, however, when using a client (Aqua Data 6.5.8 I know it is kinda old) and SQuirrel SQL 3.4.0 I always get the date in the YYYY-MM-DD format. I started to think that the issue is with the JDBC because it works fine when I connect with SQLPLUS.

View 5 Replies View Related

SQL & PL/SQL :: Validate Date Format

Feb 27, 2012

How can I check if date is in correct format ? I have In parameter for function. It is date. Example:

2011-11-01

How can I check this format ?

View 3 Replies View Related

SQL & PL/SQL :: Change Date Format

Nov 9, 2010

I want to change the date format at database level in 10g express edition.

When i try to run the command,

ALTER SYSTEM SET NLS_DATE_FORMAT='DD/MM/YYYY',

it throws error like specified initialisation parametr is not modifiabale with this option.

what are the other ways to chnage the database date format to the desired one.?

View 17 Replies View Related

SQL & PL/SQL :: Date In YYYYMMDD Format?

May 6, 2010

follwing is my structure and data in table. COLA has datatype varchar2.data in table is date..... now i want to convert all the dates in 'yyyymmdd' format.

IS it possible?

SQL> select * from taba;

COLA
---------------
1944-02-10
1982-07-20
08/24/1974
03/14/1957
22-Nov-79

View 6 Replies View Related

SQL & PL/SQL :: DECODE And Date Format?

Jul 25, 2011

While am working with Decode just found something, why it is returning like this.

I Ran this query where i have used all the date format as DD/MM/YYYY
SELECT DECODE(TO_DATE('25/04/2001','DD/MM/YYYY') ,
TO_DATE('01/01/1900','DD/MM/YYYY'), NULL ,
TO_DATE('25/04/2001','DD/MM/YYYY')) TEST
FROM DUAL

Quote:
TEST
-------------
25-APR-01

But the resule i got in DD-MON-YY

i tried searching this forum with searching strings Decode date format.. or similer i found nothing.

View 4 Replies View Related

SQL & PL/SQL :: How To Change Date Format

Feb 18, 2010

I have below data

SQL> desc IMEI
Name Null? Type
----------------------------------------- -------- ----------------------------
MSISDN NOT NULL VARCHAR2(20)
IMEI NOT NULL VARCHAR2(16)
DATE_MOD NUMBER(13)
IMSI VARCHAR2(18)
ICCID VARCHAR2(20)
T_PROF RAW(20)
EXTRA_DATA VARCHAR2(100)

SQL> select DATE_MOD from IMEI;

DATE_MOD
----------
1.2199E+12
1.2348E+12
1.2278E+12
1.2263E+12
1.2278E+12
1.2378E+12
1.2414E+12

How can I change date_mod to normal date format like year/month/day hour:minutes:second ?

View 16 Replies View Related

Forms :: Date Format

Aug 9, 2012

qry:= 'to_date(debtodt, 'dd-mon-yy')< sysdate';
or
qry:= 'debtodt < to_char(sysdate, 'dd-mm-yyyy');
or
qry:= 'to_date(debtodt, 'dd-mon-yy')< to_date(sysdate, 'dd-mm-yy')'

all these are showing error in oracle form.

in database debtodt is in char format , calculating from-- TO_CHAR(add_months(b.FRMDT,b.period)-1,'dd-mm-yyyy')

and also in oracle form it is char,thats why i am changing it either debtodt into date format or sysdate into char format. but it is showing error as: encountered the symbol "DD" when expecting one of these.................

the same query

select * from debar_reg_vu where to_date(debtodt, 'dd-mm-yyyy')<sysdate;

running correctly in database server.

View 1 Replies View Related

SQL & PL/SQL :: How To Find Out Date Format

Feb 6, 2013

I am getting daily basis data from third party in excel format which i am converting into CSV format and then uploading into oracle tables using External tables.Now problem is that every time i getting the dates in diff format i.e. sometimes dd-mon-yyyy , dd/mm/yyyy etc.

Now every time i have to open my code and change it there ...to make it as oracle date format. IS there way i can find out format of date and based on format i can do operations with getting errors every time.

I am storing the TP(Excel date) date into varchar columns only and then varchar2 column value i m inserting/updating into date format using to_Date ().

View 9 Replies View Related

SQL & PL/SQL :: How To Output Date Format

Nov 7, 2010

I'm trying to output 1/31/06 into January 31, 2006.Here is what I have so far

declare
v_dt DATE;
v_tx VARCHAR2(2000);
begin

[code]...

But it still won't display month as January.

View 11 Replies View Related

SQL & PL/SQL :: Correct Format Of Date

Jan 30, 2011

CREATE OR REPLACE VIEW V_CATALOGUER_REPORT
(COUNT, EVENT_USER, TO_DATE, SHORT_NAME)
AS
SELECT COUNT (DISTINCT A.PART_REF),A.EVENT_USER, TO_DATE(A.EVENT_DATE, 'DD-MON-YY'),B.SHORT_NAME FROM J_SUPPLY_CHAIN_HIST A, J_SOURCE B

[code]...

i created one view,i link two tables ,i can see the correct data in this view,but the result from front end application is not correct.i cant see the dates are correct.

View 1 Replies View Related







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