Oracle TO_Date query

Good Morning,

This is hopefully a simple question i am just having a hard time wrapping my head around it,

In a database i have the following value
"3474572580"
This value equates to
"2010-02-05 11:03:00.0"

I can translate that value  to the date.time using this query.
"TO_DATE('30/12/1899','DD/MM/YYYY') - (6/24) + ((b.FIELD_NAME/60-360)/60/24)"

What i am looking to do is the reverse, I want to give the query a date such as 2010-02-05 and have it find all values in the db for that date, I assume i would have to use a like statement so i can avoid having to push a time to it.

Any help would be greatly appreciated

Thank You

Defunct-ExodusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
SELECT *
FROM b
WHERE field_name >=
          ((TO_DATE('2010-02-05', 'yyyy-mm-dd') - TO_DATE('30/12/1899', 'DD/MM/YYYY') + 6 / 24)
           * 1440
           + 360)
          * 60
AND   field_name <
          ((  (TO_DATE('2010-02-05', 'yyyy-mm-dd') + 1)
            - TO_DATE('30/12/1899', 'DD/MM/YYYY')
            + 6 / 24)
           * 1440
           + 360)
          * 60
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schwertnerCommented:
((TO_DATE('30/12/1899','DD/MM/YYYY') - TO_DATE('00/00/0000','DD/MM/YYYY')    - (6/24) )*60*24 +360)*(-60)
0
sdstuberCommented:
TO_DATE('00/00/0000','DD/MM/YYYY')  is an illegal date

you'll get an ORA-01847 error from that
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

schwertnerCommented:
Sorry seems I made a mistake

((TO_DATE('30/12/1899','DD/MM/YYYY') - TO_DATE('2010-02-05 11:03:00.0','DD/MM/YYYY')    - (6/24) )*60*24 +360)*(-60)


So oo guery the number for the date 2010-02-05 11:03:00.0
you can try with this select

SELECT b.FIELD_NAME .... and other columns or expressions
FROM the_table_name  b
WHERE   b.FIELD_NAME = ((TO_DATE('30/12/1899','DD/MM/YYYY') - TO_DATE('00/00/0000','DD/MM/YYYY')    - (6/24) )*60*24 +360)*(-60);
0
sdstuberCommented:
TO_DATE('2010-02-05 11:03:00.0','DD/MM/YYYY')  

was probably supposed to be

to_date('2010-02-05 11:03:00','yyyy-mm-dd hh24:mi:ss')

fixed the format, and removed the fractional seconds, otherwise it'll have to be a timestamp type instead of date.


but, more importantly,  the query can't be evaluated with a simple equality.  Since the time column of the table has second-resolution a query for an entire day will require a range of values to be sure it captures all of them.

0
Defunct-ExodusAuthor Commented:
Exactly what i wanted, Returns results within a single range of a day.
Thank you very much.
0
sdstuberCommented:
glad I could help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.