Date_Add or Date_Sub Trying to minus a year

ahackett

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

PodiumMason

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!

PodiumMason

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!

 

ahackett

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

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!

ahackett

Thank you so much!! 

 

PodiumMason

@ahackett Andrew

 

No problem! Glad you got it worked out!

Ana

hello :)

this could be use for calculate YOY growth?

jaeW_at_Onyx

@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.