Q. How to create function and retrieve the data using function?

Step 1. Create Table Students

CREATE TABLE Students
(
    Id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    DOB datetime NOT NULL,
    total_score INT NOT NULL,    
 )

Step2. Insert Some Record in this table.

 INSERT INTO Students
VALUES (1, 'Firoz', 'male', '01-FEB-1991', 500), 
(2, 'Hari', 'Male', '02-FEB-1974', 545), 
(3, 'Sara', 'Female', '07-MAR-1988', 600), 
(4, 'Laura', 'Female', '22-DEC-1981', 400), 
(5, 'Alan', 'Male', '29-JUL-1993', 500), 
(6, 'Kate', 'Female', '03-JAN-1985', 500), 
(7, 'Joseph', 'Male', '09-APR-1982', 643), 
(8, 'Mice', 'Male', '16-AUG-1974', 543), 
(9, 'Wise', 'Male', '11-NOV-1987', 499), 
(10, 'Elis', 'Female', '28-OCT-1990', 400),
(11, 'Subba Rao', 'Male', '16-AUG-1974', 543), 
(12, 'Himaja', 'Male', '11-NOV-1987', 499), 
(13, 'Tamanna', 'Female', '28-OCT-1990', 400); 

Step 3. Use This Query for select only year of birth.

SELECT name, DATENAME(YEAR, DOB) AS BIRTH_YEAR
FROM Students

Step 4. When execute this bellow query then you will get name of day ,date,name of month and years.

SELECT
 name,
  DATENAME(DW, DOB)+ ', '+
  DATENAME(DAY, DOB)+ ' '+
  DATENAME(MONTH, DOB) +', '+
  DATENAME(YEAR, DOB) AS DOB
FROM Students

Step 5. Now create on function and keep name and DATENAME(....) Inside the function .

CREATE FUNCTION GetFormattedDate
 (
 @DateValue AS DATETIME
 )
RETURNS VARCHAR(MAX)
AS
BEGIN
RETURN
  DATENAME(DW, @DateValue)+ ', '+
  DATENAME(DAY, @DateValue)+ ' '+
  DATENAME(MONTH, @DateValue) +', '+
  DATENAME(YEAR, @DateValue)

END

Step 6. Retrieve the function name using select query.

SELECT
 name,
 dbo.getFormattedDate(DOB) as DateOfBirth
FROM Students