Oracle SQL - DATE greater than statement

Question

As the title says  I want to find a way to check which of my data sets are past 6 months from SYSDATE via query   SELECT   FROM OrderArchive WHERE OrderDate  lt    31 Dec 2014     I ve tried the following but it returns an error saying my date format is wrong  However  inserting the data I used that date format as requested intended and had no issues      Error at Command Line   10 Column   25      Blockquote      Error report -      SQL Error  ORA-01861  literal does not match format string   01861  00000 -   literal does not match format string        Cause     Literals in the input must be the same length as literals in              the format string  with the exception of leading whitespace    If the               FX  modifier has been toggled on  the literal must match exactly               with no extra whitespace        Action    Correct the format string to match the literal

User · Answer

you have to use the To Date   function to convert the string to date   http   www techonthenet com oracle functions to date php

User · Answer

As your query string is a literal  and assuming your dates are properly stored as DATE you should use date literals   SELECT   FROM OrderArchive WHERE OrderDate  lt   DATE  2015-12-31    If you want to use TO DATE  because  for example  your query value is not a literal   I suggest you to explicitly set the NLS DATE LANGUAGE parameter as you are using US abbreviated month names  That way  it won t break on some localized Oracle Installation   SELECT   FROM OrderArchive WHERE OrderDate  lt   to date  31 Dec 2014    DD MON YYYY                               NLS DATE LANGUAGE   American

User · Answer

You need to convert the string to date using the to date   function  SELECT   FROM OrderArchive WHERE OrderDate  lt   to date  31-Dec-2014   DD-MON-YYYY      OR   SELECT   FROM OrderArchive WHERE OrderDate  lt   to date  31 Dec 2014   DD MON YYYY      OR  SELECT   FROM OrderArchive WHERE OrderDate  lt   to date  2014-12-31   yyyy-MM-dd      This will work only if OrderDate is stored in Date format  If it is Varchar you should apply to date   func on that column also like   SELECT   FROM OrderArchive     WHERE to date OrderDate  yyyy-Mm-dd    lt   to date  2014-12-31   yyyy-MM-dd

[sql] Oracle SQL - DATE greater than statement

Examples related to sql

Examples related to oracle

Examples related to date

Examples related to format