×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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!

*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

Adding Time so it has DateTime in the select statement

Adding Time so it has DateTime in the select statement

Adding Time so it has DateTime in the select statement

(OP)
I'm using the Peoplesoft Query Manager and when I select this date field and look at the "View MYSQL" tab then it automatically has the expression "TO_CHAR(A.STARTDATE,'YYYY-MM-DD')" for that date field. Is there a way to change this expression so it is datetime being retrieved instead of just the date?

Thanks

RE: Adding Time so it has DateTime in the select statement

kernal,

Yes, change your "TO_CHAR" statement of
"TO_CHAR(A.STARTDATE,'YYYY-MM-DD')"
to
"TO_CHAR(A.STARTDATE,'YYYY-MM-DD HH24:MM:SS')"

Check out these formats using your sql tool to see how different they look.

Steve N.
State of Ohio, MIS

RE: Adding Time so it has DateTime in the select statement

(OP)
The problem is that our peoplesoft query manager when I select  this field automatically makes it TO_CHAR(A.STARTDATE,'YYYY-MM-DD') and I can't add just the "HH24:MM:SS'" part to it.

Thanks

RE: Adding Time so it has DateTime in the select statement

Hi Kernal,

You can do it - but it is a little tedious.

You need to create an expression where you need to change the date format back to a date time format...  This is a little 'interesting' as you need to effectively create an invalid/pointless sql expression to allow for the sql insert that Query will push into your expression.

So you will need to write something like:

TO_DATE(A.STARTDATE,'YYYY-MM-DD HH24:MM:SS') and add the expression as a field....  The Query will convert it to something like:

TO_DATE(to_char(A.STARTDATE,'YYYY-MM-DD HH24:MM:SS'),'YYY-MM'DD))

Its ugly but should work.
"

RE: Adding Time so it has DateTime in the select statement

(OP)
Thanks notadba. It worked.

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