[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

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

0
Defunct-Exodus
Asked:
Defunct-Exodus
  • 4
  • 2
1 Solution
 
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
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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