Comparing Dates in Oracle SQL

Question

I m trying to get it to display the number of employees that are hired after June 20  1994  Select employee id  count    From Employee Where to char employee date hired   DD-MON-YY    gt  31-DEC-95    But I get an error saying   quot JUN  invalid identifier   Please help  thanks

User · Answer

Single quote must be there  since date converted to character    Select employee id  count    From Employee Where to char employee date hired   DD-MON-YY      31-DEC-95

User · Answer

Conclusion   to char works in its own way   So   Always use this format YYYY-MM-DD for comparison instead of MM-DD-YY or DD-MM-YYYY or any other format

User · Answer

You can use trunc and to date as follows   select TO CHAR  g FECHA   DD-MM-YYYY HH24 MI SS   fecha salida  g NUMERO GUIA  g BOD ORIGEN  g TIPO GUIA  dg DOC NUMERO  dg    from ils det guia dg  ils guia g where dg NUMERO GUIA   g NUMERO GUIA and dg TIPO GUIA   g TIPO GUIA and dg BOD ORIGEN   g BOD ORIGEN and dg LAB CODIGO   56  and trunc g FECHA   gt  to date  01 02 15   DD MM YY   order by g FECHA

User · Answer

31-DEC-95 isn t a string  nor is 20-JUN-94  They re numbers with some extra stuff added on the end  This should be  31-DEC-95  or  20-JUN-94  - note the single quote     This will enable you to do a string comparison   However  you re not doing a string comparison  you re doing a date comparison  You should transform your string into a date  Either by using the built-in TO DATE   function  or a date literal   TO DATE    select employee id   from employee  where employee date hired  gt  to date  31-DEC-95   DD-MON-YY     This method has a few unnecessary pitfalls   As a horse with no name noted in the comments  DEC  doesn t necessarily mean December  It depends on your NLS DATE LANGUAGE and NLS DATE FORMAT settings  To ensure that your comparison with work in any locale you can use the datetime format model MM instead  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     select employee id   from employee  where employee date hired  gt  to date  31-12-1995   DD-MM-YYYY     Date literals  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 must specify your date in the format YYYY-MM-DD and you cannot include a time element   select employee id   from employee  where employee date hired  gt  date  1995-12-31    Remember that the Oracle date datatype includes a time elemement  so the date without a time portion is equivalent to 1995-12-31 00 00 00   If you want to include a time portion then you d have to use a timestamp literal  which takes the format YYYY-MM-DD HH24 MI SS  FF0-9   select employee id   from employee  where employee date hired  gt  timestamp  1995-12-31 12 31 02    Further information  NLS DATE LANGUAGE is derived from NLS LANGUAGE and NLS DATE FORMAT is derived from NLS TERRITORY  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 ALTER SESSION syntax  For instance   alter session set nls date format    DD MM YYYY HH24 MI SS     This means    DD numeric day of the month  1 - 31 MM numeric month of the year  01 - 12   January is 01   YYYY 4 digit year - in my opinion this is always better than a 2 digit year YY as there is no confusion with what century you re referring to  HH24 hour of the day  0 - 23 MI minute of the hour  0 - 59 SS second of the minute  0-59   You can find out your current language and date language settings by querying V NLS PARAMETERSs and the full gamut of valid values by querying V NLS VALID VALUES   Further reading   Format models     Incidentally  if you want the count    you need to group by employee id  select employee id  count      from employee  where employee date hired  gt  date  1995-12-31   group by employee id   This gives you the count per employee id

User · Answer

from your query   Select employee id  count    From Employee  Where to char employee date hired   DD-MON-YY    gt   31-DEC-95     i think its not to display the number of employees that are hired after June 20  1994  if you want show number of employees  you can use   Select count    From Employee  Where to char employee date hired   YYYMMMDDD    gt  19940620    I think for best practice to compare dates you can use   employee date hired  gt  TO DATE  20-06-1994    DD-MM-YYYY    or to char employee date hired   YYYMMMDDD    gt  19940620

[sql] Comparing Dates in Oracle SQL

Examples related to sql

Examples related to oracle

Examples related to date-comparison