• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Convert system date in sql union query in Crystal 9 from JDE

I have a sql union query that pulls data from the beginning of the month (minimum (month to date)) to the end of the month (maximum (month to date)). I converted the crystal code to Julian, which comes out to 106182 and 106212 respectively. What I need is a sql line that I can use in my union query the will pull the system date (from either Crystal or JDE) and convert it to Julian date format so it won't hard code the julian date for the beginning of the month and the end of the month. When August 1st comes, the beginning date needs to switch automatically to 106213 and the end date to whatever the Julian equivalent for Aug. 31, 2006 is. Does this make sense? For example, in access the system date is "date()" which can be used against JDE. What would I use for Crystal SQL?

Thank you!



sorry that no one's answered this yet. Hopefully you did a SEARCH and found the answer. If you SEARCH the forums for keywords +Julian +"SQL Server" you will find several methods for normalizing Julian Dates in SQL.

So create your query/view in SQL that normalizes the dates ... then point crystal to the query/view (its treated the same as a base table).

Does that answer your questions?
Thank you Larry. I found that JDE has a current date table; F55365A.ONDTEJ that I could use in a Crystal SQL query to pull a range of data, ONDTEJ+-31 to give me a 2 month range of data from todays date, pull that data into Crystal and use the Crystal CurrentMonth syntax to filter the data I needed. Thank you.