time or date from timestamp

Evan Cutler
Evan Cutler used Ask the Experts™
on
Greetings,
I need  a case statement against a timestamp...
I need to return a date if the timestamp is not "today"
otherwise return a time if the timestamp is "today"

help?
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hello arcee123,

Following should help you.

SELECT (

CASE datefield
WHEN DATE( datefield) = CURDATE( )
THEN TIME( datefield)
ELSE DATE( datefield)
END
)
FROM tbl


Thank you.

Amar Bardoliwala
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Just to clarify.  The timestamp datatype is not a date/time.
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
Thank you Amar,
Happy New Year...
how do I format the Date and Time to make them more readible?

thanks
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
hi,


TO_CHAR('20100901', 'MM/YY')
 
This gives the following result:
 
09/10


TO_DATE('20100901', 'YYYYMMDD')
 
This gives the following result:
 
01-SEP-10

refer this links might helps to resolve your issue....

http://www.ehow.com/how_6960278_convert-date-timestamp-oracle-sql.html

for sql i am not sure but this might works for you....

SELECT CAST('0100901' AS DATETIME)

once you have that you can easily perform other functions...
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
Thanks guys.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial