Date_Add or Date_Sub Trying to minus a year

ahackett
ahackett Member
in Connectors

Hi Guys,

I'm trying to minus a year from date field coming from Netsuite.

This is what I have but it doesn't seem to be working?

SELECT
DATE_SUB(ENDING, INTERVAL 1 YEAR) 'Last Year'
FROM ACCOUNTING_PERIODS

Any suggestions would be great.

Andrew

Best Answer

  • PodiumMason
    PodiumMason Contributor
    Answer ✓

    Oh, I see you're using the NetSuite Analytics Connector. 

     

    I'm not too familiar with what functions are available. 

     

    I did a quick Google search and found this function that might be helpful: 

     

    ADD_MONTHS( {today},-12 )

     

    I believe you insert the column name into the brackets, or replace the example column name and brackets with the column name. You could experiment and see what works.

     

    Reference: https://stackoverflow.com/questions/39131715/what-sql-functions-are-available-in-netsuite-saved-searches

     

    Let me know if you get it figured out!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Answers

  • PodiumMason
    PodiumMason Contributor

    Hey Andrew,

     

    You could try Date_add, I'm assuming you're doing this in MySQL?

     

    SELECT

    DATE_ADD(`Ending`, Interval -1 Year) as `Last Year`

    FROM ACCOUNTING_PERIODS

     

    Is there a specific error that's being thrown? That might be useful to better understand what you're seeing. 

     

    Hope this is helpful!

     

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • ahackett
    ahackett Member

    Hey,

     

    Thanks for the prompt reply, the error is ; 

     

    [NetSuite][SuiteAnalytics Connect JDBC Driver][OpenAccess SDK SQL Engine]Syntax Error in the SQL statement.[10104]

     

    I have attached the screenshot.

     

    Regards

     

    Andrew 

  • PodiumMason
    PodiumMason Contributor
    Answer ✓

    Oh, I see you're using the NetSuite Analytics Connector. 

     

    I'm not too familiar with what functions are available. 

     

    I did a quick Google search and found this function that might be helpful: 

     

    ADD_MONTHS( {today},-12 )

     

    I believe you insert the column name into the brackets, or replace the example column name and brackets with the column name. You could experiment and see what works.

     

    Reference: https://stackoverflow.com/questions/39131715/what-sql-functions-are-available-in-netsuite-saved-searches

     

    Let me know if you get it figured out!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • ahackett
    ahackett Member

    Thank you so much!! 

     

  • PodiumMason
    PodiumMason Contributor

    @ahackett Andrew

     

    No problem! Glad you got it worked out!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • Ana
    Ana Member

    hello :)


    this could be use for calculate YOY growth?

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @Ana I strongly recommend against building reporting logic into your connectors. reporting logic is something that would change regularly and should be something a little closer to the hands of the business analyst.


    extract your data from your source system as cleanly as possible, then build any reporting functionality using dataflows, views or in analyzer.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories