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

Laura Spinnett

Member
Hello,
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!
 
Laura,

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.
 
Back
Top