SQL & PL/SQL

Hi, Rajesh,

4227594 wrote:
Hi All,
I have a date column which has below value
ASUM_D_PYC
06/01/2009 1:00:00 AM
06/01/2010
06/01/2010 1:00:00 AM
06/01/2012 1:00:00 AM
06/01/2016
06/01/2020
06/01/2007
I would like to create a query to find the columns that has time stamp value ( ie ones with 06/01/2009 1:00:00 AM )
CREATE TABLE TEMP_JRNL
(
 ASUM_D_PYC DATE
) ;
 Insert into TEMP_JRNL
 (ASUM_D_PYC)
Values
 (TO_DATE('06/01/2009 01:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEMP_JRNL
 (ASUM_D_PYC)
Values
 (TO_DATE('06/01/2010 01:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEMP_JRNL
 (ASUM_D_PYC)
Values
 (TO_DATE('06/01/2012 01:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT ;
 SELECT * FROM TEMP_JRNL;
ASUM_D_PYC
06/01/2009 1:00:00 AM
06/01/2010
06/01/2010 1:00:00 AM
06/01/2012 1:00:00 AM
06/01/2016
I tried using length. However it didnt help
SELECT D asum_d_pyc , LENGTH(asum_d_pyc )
 FROM TEMP_JRNL
 WHERE phdr_c_ein='030462301'
 GROUP BY asum_d_pyc ;
Any thoughts?
This data inserted by 2 difference sources. One source inserts without timestamp and another with timestamp.
ThankYou
Rajesh

Thanks for posting the CREATE TABLE and INSERT statements, but I don't believe they correspond to the table you're actually using.  For example,

• You're only inserting 3 rows, but you're getting 5 rows of output.
• The values displayed seem to be strings, not DATEs.
• The query references a column (phdr_c_ein) that isn't in the table.

Please post CREATE TABLE and INSERT statements that correspond to the rest of your message.  Also, pot the complete, exact results you want from the given sample data.

In Oracle, TIMESTAMP is a datatype similar to DATE, but capable of noting fractions of a second.  Don't use the word TIMESTAMP for anything else; it will cause lots of confusion. If you want to talk about the hours, minutes and seconds, then say "hours, minutes and seconds".

All DATEs in Oracle have hours, minutes and seconds.  (The default value is 00:00:00, that is,12:00:00 AM.)  If you have some rows in a table include hours, minutes and seconds, but others don't, then that column is not a DATE; it's probably a string.

If you have a string column called asum_d_pyc that contains some 10-charcter strings (such as '06/01/2010') and some longer strings (such as '06.01/2009 1:00 AM'), and you want to display only the values longer than 10 characters, then you can use LENGTH like this:

WHERE   LENGTH (asum_d_pyc)  > 10

Thank you. I had tried that  earlier,. It didnt work.

WHERE   LENGTH (asum_d_pyc)  > 10

It shows all.

I had tried group by

SELECT   asum_d_pyc , LENGTH(asum_d_pyc )

    FROM TEMP_JRNL

    GROUP BY   asum_d_pyc;

4227594 wrote:
Thank you. I had tried that earlier,. It didnt work.
WHERE LENGTH (asum_d_pyc) > 10
It shows all.
I had tried group by 
SELECT asum_d_pyc , LENGTH(asum_d_pyc )
 FROM TEMP_JRNL
 GROUP BY asum_d_pyc;

Okay, so what are the results you want from the given sample data, that is, 3 rows, all with 01:00:00 as the hours minutes and seconds?

If that sample data isn't a very good test (e.g., all the rows meet the selection criteria), then post INSERT (and CREATE TABLE, if necessary) statements for some better sample data.

From the questions and answers so far, I believe your column's data type is indeed DATE, and the reason all your dates have "length greater than ten" is that when you call the LENGTH function, it requires a string input.

So, Oracle - stupidly in my opinion - instead of throwing an error to alert you to the data type mismatch, will try to convert the date to string on its own (and, again - with no warning to you whatsoever). To convert from date to string, it uses your NLS_DATE_FORMAT session parameter, which may be set to something like 'mm/dd/yyyy hh:mi:ss AM'. Even the dates "without a timestamp" (proper terminology: with time-of-day set to the default 00:00:00) will be formatted according to that model, so all will have "length" greater than 10.

If you want to only retrieve the dates that have non-zero (non-midnight) time-of-day, you can do it like this:

...where assum_d_pyc != trunc(assum_d_pyc)

TRUNC() truncates away the time-of-day; so the LHS and RHS will only be equal when the date already has time-of-day of midnight. All the other dates (with a non-midnight time of day) will satisfy the inequality in the WHERE clause.