Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
Tag: dates
Truncating a timestamp to the second
We had an interesting AskTom question recently, about why certain TRUNC commands would generate an error when applied to a TIMESTAMP value. At first glance, TRUNC seems fine. SQL> select trunc(localtimestamp,’MM’) to_the_month from dual; TO_THE_MO ——— 01-OCT-16 1 row selected. SQL> select trunc(localtimestamp,’YYYY’) to_the_year…
The simple fix to date queries
We had question in the OpenWorld panel about why queries on date columns are “always slow”. Well….they aren’t but here’s a common cause of that misconception. Let’s create a table as a copy of DBA_OBJECTS, and index the CREATED column (which is a date)….
Datatypes for DATES
Richard Foote has written a post about not using the DATE datatype for storing dates. So I’ve come up with a revolutionary system to store dates as well…using the very efficient RAW datatype. Here’s a demo SQL> create table t ( x raw(7) );…
Those pesky dates as strings
You might be thinking “Nothing is more frustrating that encountering a string column that is full of dates”. But there is something worse than that…and that is, when that string column is full of potential dates, and your job is to sift out the…
Is a year a leap year ?
This post seems timely given that yesterday was Feb 29. In almost every case I can think of, you should be relying on native Oracle date functions to perform any kind of date arithmetic. This is perhaps one of the very very few exceptions…
Automatic date formats
Notice in all of the examples below that the date string does not match the format mask. When a date conversion fails, Oracle tries some similar formats to try succeed. This is actually documented as well here Original Format Element Additional Format Elements…
NUMBER data type…what harm can it do ?
There’s a somewhat sour discussion going on based attached to the video at https://www.youtube.com/watch?v=jZW-uLb52xk Whether you agree or disagree with the video or the comments, or (sadly) the animosity in them, it does lead to an interesting bit of investigation when it comes to…
