Round Dates

-


ROUND

Syntax: ROUND ( source date [, date precision format ] )

 Parameters: source date is the date which is to be rounded. Data precision format is the unit of measure to apply for rounding. It is an optional parameter. If date precision format is omitted, the round function will round to the nearest day. Date precision formats include century (CC), year (YYYY), quarter (Q), month (MM), week (W), day (DD), hour (HH), and minute (MI).

 Return Type: The ROUND function (as it applies to dates) returns date value.

 Usage: According to company's policy, every month employees get shopping coupons. For the employees who have joined the organization after 15th of the month will start getting their shopping coupons from the next month. Manager wants the months from which employees get their shopping coupons. Manager to Data analyst: Bring me the list of all employees with the month from which employee is getting their shopping coupons. Data analyst runs below query to fetch the required data.

 SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, TO_CHAR ( ROUND ( TO_DATE ( HIRE_DATE, 'DD-MON-RR'), 'MONTH' ), 'MON') FROM EMPLOYEES;



Comments

Post a Comment

Popular posts from this blog

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

Full Outer Join!

-
Image
FULL OUTER JOIN:  (i) HR manager to data analyst:   Bring me the details of employees and their corresponding department details and the details of department who doesn’t have any employee. SELECT * FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID= D.DEPARTMENT_ID; (ii) HR manager to data analyst: Bring me the details of job history with its corresponding job details and the details of job which doesn’t have job history. SELECT * FROM JOB_HISTORY JH FULL OUTER JOIN JOBS J ON JH.JOB_ID= J.JOB_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, FIRST_NAME, L
Read more