Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2015
    Posts
    1

    Unanswered: Working with Date data type.

    I have a table, assetdetail. In that table i have column, name, acquisitionyear with Date as the data type. Here is its visual:

    ------------- ------------- -------------- ------------------
    seq_index | assetcode | assetname | acquisitionyear |
    ------------- ------------- -------------- ------------------
    1 | 02.02.01 | Chair | 2012-01-02 |
    2 | 02.02.02 | Desk | 2013-01-02 |
    3 | 02.02.03 | Table | 2009-01-02 |
    4 | 02.02.04 | Cabinet | 2018-01-02 |

    I want to sort the table so it can show the assetname which its acquisitionyear less than 2012, using acquisitionyear column as the condition, but only the year (2012-01-02 turn into 2012). In short words, i want to make it like: select * from assetdetail where acquisitionyear <= '2012'. So what query i've to do?

  2. #2
    Join Date
    Mar 2015
    Location
    Indonesia
    Posts
    3
    if you want sort by year from date

    Code:
    SELECT * FROM assetdetail WHERE EXTRACT (YEAR FROM acquisitionyear ) <= 2012
    hope that will help

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Another option:

    Code:
    select *
    from assetdetail
    where acquisitionyear  < date '2013-01-01'
    That query will be able to use an index on acquisitionyear
    Last edited by shammat; 03-14-15 at 05:28.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •