1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle date conversion to sql

Discussion in 'General' started by Pasi, Jan 5, 2018.

  1. Pasi

    Pasi Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    USA
    I have below date that want to convert to SQL but not working?? Not sure how to formulate this?

    FFDT=TO_CHAR(TO_DATE('2016-11-02', 'YYYY-MM-DD'), 'YYYY') --- Oracle

    FFDT = Convert(varchar(10),CONVERT(DATETIME, '2016-11-02'), 'YYYY')--SQL

    FFDT is a date

    Thanks!
    Pasi
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    WHY are you going through all of the work of converting that string to a date then BACK again just to get a smaller string? At worst all you'd need is substring():

    substring( '2016-11-02', 1, 4)

    and at best simple edit the string before you do anything else to it. It seems wasteful of your time and database resources to go through all of the gyrations you're trying to execute to reduce that string by 6 characters.
     
  3. Pasi

    Pasi Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    USA
    Thanks for tip! These were written by some one else and I am trying to convert them to SQL, I had the same question but person is not here anymore..
     
  4. Pasi

    Pasi Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    USA
    Its not working I still get the YYYY-MM-DD?

    upload_2018-1-5_15-31-25.png
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Again you're wasting time on this exercise the way it's written. IF dates are provided as character strings then use SUBSTRING as I showed in a prior response.
     
  6. Pasi

    Pasi Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    USA
    I did use your substring but still get 10 char? not YYYY.
     
  7. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Not if you used it properly -- what I supplied will start at the first character and return 4 characters from that string.

    Post EXACTLY what you have done because your results don't match what was coded.

    When I run this I get the following:

    select substring( '2016-11-02', 1, 4)

    2016

    so the code DOES work.
     
    Last edited: Jan 7, 2018
  8. Pasi

    Pasi Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    USA
    Yeas it works I had the code in WHERE Clause which didn't work, not sure why it wont work in WHERE clause?
     
  9. Rahul Bhogavkar

    Rahul Bhogavkar Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Hyderabad
    Hi,

    Use below query if you want to show only Year.

    SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL

    Thanks.
     

    Attached Files:

  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    That is not transact-sql syntax.You DID read the thread before posting???
     
  11. Pasi

    Pasi Newly Initiated

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    USA
    Thanks for tips but this is Oracle, I got tips form zargon and it works just had it in a wrong place within Query.
    Thanks you all.