date time help

[ss7415]

the date - time should be 4/13/2006 20:34:21.80

first record
date_add looks like 4/13/2006
time_add look like 20342180

second record
date_add looks like 4/13/2006
time_add look like 20532132


i have date_add and time_add i want combine the two fields . sometimes a record may have the same date, so i need the time to determine the max. i would like to do it in my command , im using crystal xi and oracle9i

 

[SantaMufasa]

SS,

Do you have access to SQL*Plus itself? If so, what are the data types (DATE, VARCHAR2, NUMBER, et cetera) that appear for "date_add" and "time_add" after you issue the command:

describe <tablename>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ss7415]

date_add is a date_add = date and time_add = number

 

[Beilstwh]

is 20342180 equal to 20:34.2180 (8pm 34 minutes, 21 seconds, 80 hundreds of a second?

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

SS,

I am puzzled as to why the application developers created a "TIME_ADD" expression since "DATE_ADD" as a DATE column already stores TIME down to the second. You can see this by issuing the following command:

SELECT TO_CHAR(date_add,'Day, Month DD, YYYY HH24:MI:SS')date_add
FROM <table_name>
WHERE ROWNUM = 1 and date_add IS NOT NULL;

If the TIME component turns out to be "00:00:00", then that just means that the application did not place any specific time into that entry.

Now, to resolve your question at hand, what does the numeric value, "20342180" represent in your application data? Once we determine that, then we can offer code to you that will combine the "DATE_ADD" and "TIME_ADD" values.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ss7415]

yes, but would like to keep same format just add : and .

 

[ss7415]

the date_add has time but all 12:00:00AM


is 20342180 equal to 20:34.2180 (8pm 34 minutes, 21 seconds, 80 hundreds of a second?
yes, but would like to keep same format just add : and .

 

[ss7415]

SELECT TO_CHAR(date_add,'Day, Month DD, YYYY HH24:MI:SS')date_add
FROM int_table
WHERE ROWNUM = 1 and date_add IS NOT NULL;

Wednesday, June 01, 2005 00:00:00

 

[Beilstwh]

The following select would combine the date and time

select TO_DATE(to_char(date_add,'MMDDYYYY')||TO_CHAR(FLOOR(TIME_ADD/100),'FM000000'),'MMDDYYYYHH24MISS') FROM MY_TABLE;

Bill
Oracle DBA/Developer
New York State, USA

 

[ss7415]

im getting data now. thanks. but can i...

for

3/13/2006 11180380

im returning

3/13/2006 11:18:03 AM

is there away i can do it like 3/13/2006 11:18:03.80 AM
or
3/13/2006 11:18:03.80

 

[SantaMufasa]

the date - time should be 4/13/2006 20:34:21.80

Sorry, SS, I didn't read carefully enough in your first post.


To achieve that above visual objective (including milliseconds), then I believe you may want this variation to Bill's fine code:

select to_char(
to_timestamp(to_char(date_add,'mm/dd/yyyy ')||time_add
,'mm/dd/yyyy hh24miss.ff2')
,'mm/dd/yyyy hh24:mi:ss.ff2') "Date/Time" from ss;

Date/Time
----------------------
04/13/2006 20:34:21.80
04/13/2006 20:53:21.32

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]