Date / Time related queries

http://viralpatel.net/blogs/useful-oracle-queries/

Here’s a list of 40+ Useful Oracle queries that every Oracle developer must bookmark. These queries range from date manipulation,getting server info,get execution status,calculate database size etc.

Date / Time related queries

1. Get the first day of the month

   Quickly returns the first day of current month. Instead of current month you want to find first day of month where a date falls,replace SYSDATE with any date column/value.

2. Get the last day of the month

   This query is similar to above but returns last day of current month. One thing worth noting is that it automatically takes care of leap year. So if you have 29 days in Feb,it will return 29/2. Also similar to above query replace SYSDATE with any other date column/value to find last day of that particular month.

3. Get the first day of the Year

   First day of year is always 1-Jan. This query can be use in stored procedure where you quickly want first day of year for some calculation.

4. Get the last day of the year

   Similar to above query. Instead of first day this query returns last day of current year.

5. Get number of days in current month

   Now this is useful. This query returns number of days in current month. You can change SYSDATE with any date/value to know number of days in that month.

6. Get number of days left in current month

   Below query calculates number of days left in current month.

7. Get number of days between two dates

   Use this query to get difference between two dates in number of days.

   Use second query if you need to find number of days since some specific date. In this example number of days since any employee is hired.

8. Display each months start and end date upto last month of the year

   This clever query displays start date and end date of each month in current year. You might want to use this for certain types of calculations.

9. Get number of seconds passed since today (since 00:00 hr)

10. Get number of seconds left today (till 23:59:59 hr)

    Data dictionary queries

11. Check if a table exists in the current database schema

    A simple query that can be used to check if a table exists before you create it. This way you can make your create table script rerunnable. Just replace table_name with actual table you want to check. This query will check if table exists for current user (from where the query is executed).

12. Check if a column exists in a table

    Simple query to check if a particular column exists in table. Useful when you tries to add new column in table using ALTER TABLE statement,you might wanna check if column already exists before adding one.

13. Showing the table structure

    （编辑：安卓应用网）

    【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!