In this article I am going to show how we can show Date and
time in a formatted manner as per our business requirement.
Suppose you have a date in you Data Base like as: 2015-07-08 00:00:00.000 and you
want to show only 2015-07-08
And suppose you have Time in your Data Base like as: 02:28:02.3467545 AM and you
want to show like 02:28:02 AM
I will show this
how we can write our select statement to achieve above Date Time format:
Below I have 2
Tables:
1. Employee
2. EmployeeDetails
Data in my Employee
Image 1.
Data in my
Employee Details Table
Image 2.
Now I will write a join query to fetch data from both
tables:
SELECT E.Name,E.Email,E.ManagerName, ED.JoiningDate,ED.JoiningTime
FROM Employee AS E
INNER JOIN EmployeeDetails AS
ED ON E.ID=ED.Emp_ID
Execute this and see result:
Image 3.
In above result Date and Time format is same as we have in
our Data base. So now we will convert this:
SELECT E.Name,E.Email,E.ManagerName,
CONVERT(VARCHAR(10), ED.JoiningDate, 101) AS JoininDate,
CONVERT(VARCHAR(8), ED.JoiningTime, 108) + ' '+ RIGHT(CONVERT(VARCHAR, ED.JoiningTime, 100), 3) AS JoiningTime
FROM Employee AS E
INNER JOIN EmployeeDetails AS
ED ON E.ID=ED.Emp_ID
Image 4.