I need a CONVERT syntax example

[StuartJ]

Hi all,<br><br>I am not a programmer of any great skill. I write reports using crystal 8. I connect to an oracle 7 DB where all of the dates are stored as unix dates. I want the server to restrict the information returned by comparing my start date and end date to the date field and then returning the data I want. I use parameters in crystal to determine the start date and end date. I suspect the CONVERT function will help me, but I don't know. <br><br>Can anyone help?<br><br>Stuart

 

[MikeLacey]

Stuart,<br><br>I'm not a Crystal person really but what you want is easy enough to do with SQL.<br><br>Is there a way you can edit the SQL query that Crystal generates?<br><br>If there is, add something like this to the WHERE clause<br><FONT FACE=monospace><b>Where DATE_FIELD Between START_DATE And END_DATE</font></b><br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[StuartJ]

Hi Mike,<br><br>Basically my reports have parameters that ask the clients for the dates they wish to see the data from - until. i.e. start date and end date. The sql however does not reflect the selection example that you specified because the date parameters in my reports are based on formulas, and in the machine system date format which is dd/mm/yyyy for the UK. <br><br>The date on the oracle server in the data base is of the UNIX date format. I have tried the simple path of altering my formula to a unix date format but the SQL in crystal doesn't reflect this. Therefore I want to directly alter the sql in my reports to make the oracle server do the date conversion before passing the data back to my reports. <br><br>I have been informed that I now need a TO_CHAR section as CONVERT won't do it. I hope that is a bit clearer now. <br><br>Thanks<br><br>Stuart

 

[CRoberts]

I do not believe the CONVERT is the answer to your problem.&nbsp;&nbsp;TO_CHAR is not really right either.&nbsp;&nbsp;What you want in your where clause is a TO_DATE function.&nbsp;&nbsp;Oracle default date format is usually DD-MON-YY (the dba can change it).&nbsp;&nbsp;If you want to use any other date format, you must tell Oracle what that format is.&nbsp;&nbsp;For example, within Crystal one could code the TO_DATE function to handle your input date parmeters as follows (I don't know Crystal, so I don't know how to specify your parameters.&nbsp;&nbsp;But the method still is relevant):<br><br>WHERE DATE_FIELD BETWEEN TO_DATE(StartDate, 'dd/mm/yyyy') AND TO_DATE(EndDate, 'dd/mm/yyyy')<br><br>Oracle stores dates that is completely independent of the host os.&nbsp;&nbsp;You are not dealing with UNIX, but Oracle.<br><br>