Answered by:
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).
Thursday, April 5, 2012 10:17 AM
Answers

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
Thursday, April 5, 2012 10:29 AM
All replies

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 nBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
 Proposed as answer by Naomi N Thursday, April 5, 2012 6:38 PM
Thursday, April 5, 2012 10:28 AMAnswerer 
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
Thursday, April 5, 2012 10:29 AM 
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).
Monday, April 9, 2012 12:47 PM