Number of days or weekdays between two days in ORACLE

SELECTcount(HIREDATE)FROM
(SELECT TO_DATE(FIRSTDATE,'DD.MM.YYYY') + level - 1HIREDATE FROM dual
 CONNECTBY level <= TO_DATE(LASTDATE,'DD.MM.YYYY')- TO_DATE(FIRSTDATE,'DD.MM.YYYY'))
where to_char(HIREDATE, 'd') in (6,7)

if you remove count, it will give the list of dates between. (6,7) is the number of days in a week. Monday is 1, Tuesday is 2, if we change it as (1,2,3,4,5), it returns weekdays.

Advertisements
This entry was posted in ORACLE. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s