What happens Next Day??

-
NEXT_DAY

Syntax: NEXT_DAY (start date, day of the week)

Parameters: start date is the date parameter used to find the next weekday. Day of week may be a character value or integer value. Acceptable character values and Integer values are given in the table below along with description for each.

Return type: The NEXT_DAY function returns a date value.

Character value
Integer value
Description
SUNDAY
1
Date of the first Sunday greater than date
MONDAY
2
Date of the first Monday greater than date
TUESDAY
3
Date of the first Tuesday greater than date
WEDNESDAY
4
Date of the first Wednesday greater than date
THURSDAY
5
Date of the first Thursday greater than date
FRIDAY
6
Date of the first Friday greater than date
SATURDAY
7
Date of the first Saturday greater than date

Usage:
According to company's policy, every employee gets the JOINING BONUS on the first Friday after the joining day.  Company's database doesn't have information about the date on which each employee has got his/her JOINING BONUS. Manager needs this data for the reporting purpose.  She called Data analyst. Manager to Data analyst:  Bring me the list of all employees and the date on which each employee has got their JOINING BONUS. Data analyst runs below query to fetch the required data.

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, NEXT_DAY (HIRE_DATE, 'FRI') AS JOINING_BONUS_DATE FROM EMPLOYEES;

Comments

Post a Comment

Popular posts from this blog

Let's learn Left Outer Join!

-
Image
LEFT OUTER JOIN (i) HR Manager wants all the employees’ details regardless of whether they have past Job-history or not. If employee has a job-history then manager also wants job-history details. Manager to data analyst: Bring me the details of employees along with the details of their job history if employee has one. Data analyst runs below query to fetch the required data. SELECT * FROM EMPLOYEES E LEFT OUTER JOIN JOB_HISTORY JH ON E.EMPLOYEE_ID= JH.EMPLOYEE_ID;  (ii) HR manager wants details of departments regardless of department having manager or not. If department has manager then HR manager also wants the details of manager.  Manager to data analyst:  Bring me the details of departments along with the details of manager of the department. Data analyst runs below query to fetch the required data. SELECT * FROM DEPARTMENTS D LEFT OUTER JOIN EMPLOYEES E ON D.MANAGER_ID= E.EMPLOYEE_ID;
Read more

Let's extract STRING!

-
Image
SUBSTR Syntax: SUBSTR ( source string, start position [, number of characters to extract]) Parameters: source string is the string from which sub-string is to be extracted. Start position is the starting position of extraction. Number of characters to extract is the optional parameter. If this parameter is omitted, the SUBSTR function will return the entire string. Return type: The SUBSTR function returns a string value. If number of characters to extract is a negative number, then SUBSTR function will return a NULL value. Usage:  Manager got an offer from mobile phone carrier provider for all the employees of the organization. To avail this offer, Manager has to group the employees based on the area code in their phone number.  Manager to Data analyst: Bring me the list of all employees with first three digits of their phone no. sort the result based on phone no. Data analyst runs below query to fetch the required data. SELECT EMPLOYEE_ID, FIRS...
Read more

Let's learn Cross Join!

-
Image
CROSS JOIN HR manager to data analyst: Bring me the every possible combination of department ids and job ids along with the details for both. Data analyst runs below query to fetch the data.  SELECT * FROM DEPARTMENTS, JOBS; 
Read more