Want to Calculate a Date with System Date less than two working days

Question

Having one Day from DB and calculated that date with Current date is Less than 2, 4 and ... consecutive working days.

Holidays and the Week ends wants to omit here. Can any one give the Query format for Oracle 10 version.

With Thanks,

C.R.Thilahar.

---

CRThilahar(Want to become a GreatLeader to ALL).

Answer 1

What holidays? I do not think I and you have same holidays:-) I thin the best is to create a Calendar table where you specify what holiday or even what first day of the week

CREATE TABLE dbo.Calendar 
( 
    dt SMALLDATETIME 
        PRIMARY KEY CLUSTERED, 
         
    isWeekday AS CONVERT(BIT, CASE 
        WHEN DATEPART(DW, dt) IN (1,7) 
        THEN 0 ELSE 1 END), 
 
    isHoliday BIT NOT NULL DEFAULT 0, 
 
    Y AS CONVERT(SMALLINT, YEAR(dt)), 
     
    FY AS CONVERT(SMALLINT, CASE 
        WHEN MONTH(dt) < 5 THEN YEAR(dt)-1 
        ELSE YEAR(dt) END), 
 
    Q AS CONVERT(TINYINT, CASE 
        WHEN MONTH(dt) < 2 THEN 4 
        WHEN MONTH(dt) < 5 THEN 1 
        WHEN MONTH(dt) < 8 THEN 2 
        WHEN MONTH(dt) < 11 THEN 3 
        ELSE 4 END), 
 
    M AS CONVERT(TINYINT, MONTH(dt)), 
    D AS CONVERT(TINYINT, DAY(dt)), 
    DW AS CONVERT(TINYINT, DATEPART(DW, dt)), 
 
    monthname AS CONVERT(VARCHAR(9), DATENAME(MONTH, dt)), 
    dayname AS CONVERT(VARCHAR(9), DATENAME(DW, dt)), 
 
    W AS CONVERT(TINYINT, DATEPART(WK, dt)) 
) 
GO
INSERT Calendar(dt) 
    SELECT DATEADD(day, n, '20000101') 
        FROM numbers 
        WHERE n <= 10957 
        ORDER BY n

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

First of all this is not an Oracle discussion forum.

Second; your question: since you don't provide much details on the table definitions sample data and expected results I suggest you to use a auxiliary Calendar table to get the desired output. The below articles may be helpful. You can add Holiday details to the table as well to include/exclude holiday dates...

Dan Guzman's Calendar Table and Date/Time Functions

Calculating Today's Businesday (MSDN Forum post)

sql server get absent table (MSDN Forum post)

Serach online for 'Calender tables in SQL' or 'Calendar tables in Oracle' to get numerous examples on this.

- Krishnakumar S

Answer 3

Hallo Uri Dimant,

    Thanks for ur answer. but i can't use your query, because i'm using Sql server editor. so when i run this shown error message.

Krishnakumar,

   Thanks to give the links for studying and getting to know about this. All the links are very useful. Sorry for posting the Oracle question here.

My problem is comparing two dates and find there how many days difference from one another(want to find Except the holidays here). For this i want to created a calendar in Oracle DB and given the current date and the ID creation date to calculated the date difference between that and to send a mail.

Thanks for ur links and Help.

With regards,

C.Rajandra Thilahar.

---

CRThilahar(Want to become a GreatLeader to ALL).