Adding Time so it has DateTime in the select statement 1

[kernal]

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

 

[sln007]

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

 

[kernal]

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

 

[notadba]

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.

 

[kernal]

Thanks notadba. It worked.