Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

Oracle Dates, Timestamps and Intervals - An overview of the usage of dates, timestamps and intervals in Oracle databases.



Timestamp to Date said...

Hello Tim,

I was looking for an option to convert timestamp data type to date. As my search lead me to your article may I suggest to add the following to it?

How to transfer timetamp to date format example:
select CAST(systimestamp AS DATE) cSYSDATE from dual;

Tim... said...

Hi.

Thanks for spotting the omission. I've added a two-way conversion into the article.

Cheers

Tim...

chaitu said...

Hi Tim,

While explaining the TRUNC and ROUND with year format model(YY), you've given the explanation that it rounds to July 1st. But, It rounds to Jan 1st.
May be a typo error. Please check it.

Thanks.

Dave from NJ said...

Hi, your example for NEXT_DAY(date, day) indicates that the next Monday after Oct 7, 2004, would be Dec 7, 2004, but I think this is wrong. The next Monday would be Oct 11, 2004. I tested with:
SELECT SYSDATE-3399,
NEXT_DAY(SYSDATE-3399, 'MONDAY')
FROM dual;

Tim... said...

Hi.

Yes. You're correct. Not sure how that happened. Doesn't even look like a dodgy copy/paste. :(

BTW, I tested it with this.

SELECT TO_DATE('07-OCT-2004','DD-MON-YYYY'),
NEXT_DAY(TO_DATE('07-OCT-2004','DD-MON-YYYY'), 'MONDAY')
FROM dual;

Cheers

Tim...

PS. I corrected the date. Thanks. :)

Dave from NJ said...

Update: my comment was because I was reading 10/07/2004 as Oct 7, 2004, and because I was reading 12/07/2004 as Dec 7, 2004. This would match the "normal" US convention of mm/dd/yyyy. However I think your examples were dd/mm/yyyy so they were July 10, 2004, and July 12, 2004. And these dates were correct because July 12 IS the next Monday. Your new date 11/07/2004 is now wrong, sorry!

Tim... said...

LOL. I just took your dates and plugged them in to the SQL. I didn't even think to check my original example. :)

I think I should go through and redo the tests, using a format mask of 'DD-MON-YYYY' to save confusion. :)

Cheers

Tim...
Prepared Statement Error: Table './oraclebasecms/cms_page_comment_uuids' is marked as crashed and should be repaired