• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1795
  • Last Modified:

oracle convert date to number

I have a prod issue where  a code segment has the date comparision to input date and it failed on the executionl

I have table A with column birth_date as defined as date

in the code segment we do

birth_date <= '01-MAY-2006'

the value for birthdate is 01-MAY-2006 but has time component 14:05:26

how do I see in the table what are the rows that has date in date time format?

and then I want to fix the problem..

first to find the length of the column I did

select * from tableA where length(birth_date) > 10 , I got none I hope to see all the time fields showing up..

Any help here please..
1 Solution
mahjagAuthor Commented:
Oracle version is 8i.
Guy Hengel [angelIII / a3]Billing EngineerCommented:

birth_date <> trunc(birth_date)
mahjagAuthor Commented:
Thanks, this show the rows that have time column?

do you know why prod should fail while comparing  dates from input field to date column in the table?

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Mark GeerlingsDatabase AdministratorCommented:
Oracle "date" columns are actually stored in the database as a type of number column.  In Oracle9 and 10, I think the actual lenghth is seven bytes, it may have been one or two bytes shorter in Oracle8i, but that doesn't matter, since you won't work with the internal number anyway.

You can use "to_char" when you select a date to display any portion of the date and/or time.  Oracle allows dozens of diiferent valid format masks for dates to see: year, quarter, month, week, day, hour, minute, second, etc. and any combination of those that you want.

If you want all date columns to always be displayed in a particular format (unless you override it with "to_char" and a different mask)  you can set your NLS_DATE_FORMAT to that format mask.

You always have to be careful in Oracle when working with dates, since date columns or PL\SQL variables of "date" datatype may contain a time portion, but you may or may not see the time portion depending on your NLS_DATE_FORMAT setting, or the format mask you use with "to_char".  Also, be careful if you do date comparisons or date arithmetic in SQL statements and you provide a charcter string like: '01-MAY-2006' and expect Oracle to an implict conversion to a date datatype so it can evaluate the expression.  It is always safer to explicity convert character strings like this to a "date" (something like this: to_date('01-MAY-2006','DD-MON-YYYY') so you wan't be surprised or disappointed by Oracle's implict data conversion that may not always be what you expect.
mahjagAuthor Commented:
I actually had to_date when i did this in sql, but my code segment is from package and I just cut and paste the lines since I dont need to_date

in the code segment we do

birth_date <= '01-MAY-2006'

to display length of date column what should I do?

>>length of date column
     as mark said, internally, date is taking 8 bytes (oracle 9i). depending on how you set nls_date_format, you get different length value.
    there is no such concept "length" for date type itself.

    when you do length(date1), actually it is doing length(to_char(date1)) which evaluates the lenght of display string of date type and you can always change the display format as we mentioned above.

    hope it makes sense.

Mark GeerlingsDatabase AdministratorCommented:
This kind of code is very dangerous in Oracle:
birth_date <= '01-MAY-2006'

If "birth_date" is a date column, this forces Oracle to do an implicit data conversion since '01-MAY-2006' is a character string.  Sure, it looks to a human like a date, but Oracle either has to convert that to a date, or convert the date column value to a character string before it can determine which rows meet or do not meet the condition.

If you want to find records that have a non-midnight time component on the date, you can do this:
select * from [your_table]
where birth_date > trunc(birth_date)

This will find any records that have a time component different than midnight (which is the default or "blank" time value).

Or if you want to see the times, do this:
select to_char(birth_date,'DD-MON-YYYY HH24:MI:SS') "Birth_date"
from [your_table]

This will show you both the date and the time for each record.
Mark GeerlingsDatabase AdministratorCommented:
A safer way to write comparisons in Oracle is with an explicit conversion like this:

birth_date <= to_date('01-MAY-2006','DD-MON-YYYY')

But that can still be problematic if the birth_date records actually have a non-midnight time component, since records for any time on May 1 later than midnight would still be excluded.  You could do:
trun(birth_date) <= to_date('01-MAY-2006','DD-MON-YYYY')

But that has disdvantages too, since it will prevent the use of an index on birth_date.  That may not be a problem if this table is small or this column is not indexed, but this approach on indexed columns is a problem.  (There are work-arounds for this too, if you need them.)
mahjagAuthor Commented:
It is the indexed column in that table,

Ok, can I not do to_num(to_char(datefield,'DD-MON-YY'))
Mark GeerlingsDatabase AdministratorCommented:
No, you don't want to do any operators on an indexed column value!  If you, that prevents use of the index.  (Unless you have a function-based index and your database is configured to support function-based indexes, but that functionality is not turned on by default, nor do I recommend it for someone new to Oracle.)

You need something like this to allow use of an index on "birth_date" plus find records that have a non-midnight time portion on the day provided:
birth_date <= to_date('01-MAY-2006 23:59:59','DD-MON-YYYY HH24:MI:SS')

birth_date <= to_date('01-MAY-2006','DD-MON-YYYY') + 86399/86400

This works since there are 86,400 seconds in a day, and this will add one less than that many seconds to the date value provided, so it finds any records with a non-midnight time on that day.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now