Show Date and Time in a Formatted Manner in SQL Server

Introduction

Assume you have a date in your database, such as 2015-07-08 00:00:00.000, and you want to show only 2015-07-08. Assume you have a time in your database, such as 02:28:02.3467545 AM, and you want to show it like 02:28:02 AM. I will show how to write a select statement to get the preceding Date and Time format.

I have the following 2 Tables:

1. Employee
2. EmployeeDetails

Data in my Employee

Data in my Employee Details Table

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 the result as in the following.

In the preceding result, the Date and Time format are the same as we have in our database. So now we will convert like 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