I've connected NS to PowerBI through ODBC and I'm trying to write a SQL query to return transactions after a specific date. I keep getting an error message. I know the date is returned from NS as '10/01/2020 120000 AM’ but how do I query that through SQL?

WHERE dates BETWEEN ‘20201021’ and ‘20201021’

Thanks but that still didn't work. I got it to run connect when I used current_date() but it didn't actual return any results, which isn't right because there are definitely transaction with today's date. I think has something to do with the timestamp but I can't quite figure it out

FYI Netsuite time stamps are always a challenge. Our BI guy complains about this a lot. sorry not a answer to your problem.

No problem, I appreciate the help any way. I'm very new to BI/ODBC so the fact that I could even connect it today is a marvel. I appreciate knowing that this is something that is annoying for others and not just me and my BI-ignorance. Thank you!

So much bad advice. Use the

TO_DATE

function.

select * from transactions where trandate>=TO_DATE('2020/10/05', 'yyyy/mm/dd')

. By using this, there's 0 ambiguity surrounding what date you actually mean

@PNJ THIS FINALLY WORKED! I can't tell you how many different versions of this I tried yesterday and I kept running into errors. Thank you SO much!

You're welcome

@Becca A You should take a few minutes and check out https://www.tacticalconnect.com/. Using ODBC is slow and doesn't expose all the information you may need.

@Becca A happy to chat! I have no doubt we will save you loads of hours.