StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><code>31-DEC-95</code> isn't a string, nor is <code>20-JUN-94</code>. They're numbers with some extra stuff added on the end. This should be <code>'31-DEC-95'</code> or <code>'20-JUN-94'</code> - note the single quote, <code>'</code>. This will enable you to do a string comparison.</p> <p>However, you're not doing a string comparison; <em>you're doing a date comparison</em>. You should transform your string into a date. Either by using the built-in <a href="http://docs.oracle.com/database/121/SQLRF/functions219.htm#SQLRF06132" rel="noreferrer"><code>TO_DATE()</code></a> function, or a <a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF51062" rel="noreferrer">date literal</a>.</p> <h2>TO_DATE()</h2> <pre><code>select employee_id from employee where employee_date_hired > to_date('31-DEC-95','DD-MON-YY') </code></pre> <p>This method has a few unnecessary pitfalls</p> <ul> <li>As a_horse_with_no_name noted in the comments, <code>DEC</code>, doesn't necessarily mean December. It depends on your <a href="http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#i1006894" rel="noreferrer"><code>NLS_DATE_LANGUAGE</code></a> and <a href="http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#i1006817" rel="noreferrer"><code>NLS_DATE_FORMAT</code></a> settings. To ensure that your comparison with work in any locale you can use the <a href="http://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212" rel="noreferrer">datetime format model</a> <code>MM</code> instead </li> <li>The year '95 is inexact. You know you mean 1995, but what if it was '50, is that 1950 or 2050? It's always best to be explicit</li> </ul> <pre><code>select employee_id from employee where employee_date_hired > to_date('31-12-1995','DD-MM-YYYY') </code></pre> <h2>Date literals</h2> <p>A date literal is part of the ANSI standard, which means you don't have to use an Oracle specific function. When using a literal you <em>must</em> specify your date in the format <code>YYYY-MM-DD</code> and you cannot include a time element.</p> <pre><code>select employee_id from employee where employee_date_hired > date '1995-12-31' </code></pre> <p>Remember that the Oracle date datatype includes a time elemement, so the date without a time portion is equivalent to <code>1995-12-31 00:00:00</code>.</p> <p>If you want to include a time portion then you'd have to use a timestamp literal, which takes the format <code>YYYY-MM-DD HH24:MI:SS[.FF0-9]</code></p> <pre><code>select employee_id from employee where employee_date_hired > timestamp '1995-12-31 12:31:02' </code></pre> <h2>Further information</h2> <p><a href="http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#i1006894" rel="noreferrer"><code>NLS_DATE_LANGUAGE</code></a> is derived from <a href="http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#i1006480" rel="noreferrer"><code>NLS_LANGUAGE</code></a> and <a href="http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#i1006817" rel="noreferrer"><code>NLS_DATE_FORMAT</code></a> is derived from <a href="http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#i1006575" rel="noreferrer"><code>NLS_TERRITORY</code></a>. These are set when you initially created the database but they can be altered by changing your inialization parameters file - only if really required - or at the session level by using the <a href="http://docs.oracle.com/database/121/SQLRF/statements_2015.htm#SQLRF00901" rel="noreferrer"><code>ALTER SESSION</code></a> syntax. For instance:</p> <pre><code>alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS'; </code></pre> <p>This means:</p> <ul> <li><code>DD</code> numeric day of the month, 1 - 31</li> <li><code>MM</code> numeric month of the year, 01 - 12 ( January is 01 )</li> <li><code>YYYY</code> 4 digit year - in my opinion this is <em>always</em> better than a 2 digit year <code>YY</code> as there is no confusion with what century you're referring to.</li> <li><code>HH24</code> hour of the day, 0 - 23</li> <li><code>MI</code> minute of the hour, 0 - 59</li> <li><code>SS</code> second of the minute, 0-59</li> </ul> <p>You can find out your current language and date language settings by querying <code>V$NLS_PARAMETERSs</code> and the full gamut of valid values by querying <code>V$NLS_VALID_VALUES</code>.</p> <h2>Further reading</h2> <ul> <li><a href="http://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00212" rel="noreferrer">Format models</a></li> </ul> <hr> <p>Incidentally, if you want the <code>count(*)</code> you need to group by <code>employee_id</code></p> <pre><code>select employee_id, count(*) from employee where employee_date_hired > date '1995-12-31' group by employee_id </code></pre> <p>This gives you the count <em>per</em> <code>employee_id</code>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload