×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

I need a CONVERT syntax example

I need a CONVERT syntax example

I need a CONVERT syntax example

(OP)
Hi all,

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.

Can anyone help?

Stuart

RE: I need a CONVERT syntax example

Stuart,

I'm not a Crystal person really but what you want is easy enough to do with SQL.

Is there a way you can edit the SQL query that Crystal generates?

If there is, add something like this to the WHERE clause
Where DATE_FIELD Between START_DATE And END_DATE

Mike
michael.j.lacey@ntlworld.com
Cargill's Corporate Web Site

RE: I need a CONVERT syntax example

(OP)
Hi Mike,

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.

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.

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.

Thanks

Stuart

RE: I need a CONVERT syntax example

I do not believe the CONVERT is the answer to your problem.  TO_CHAR is not really right either.  What you want in your where clause is a TO_DATE function.  Oracle default date format is usually DD-MON-YY (the dba can change it).  If you want to use any other date format, you must tell Oracle what that format is.  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.  But the method still is relevant):

WHERE DATE_FIELD BETWEEN TO_DATE(StartDate, 'dd/mm/yyyy') AND TO_DATE(EndDate, 'dd/mm/yyyy')

Oracle stores dates that is completely independent of the host os.  You are not dealing with UNIX, but Oracle.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close