Show Date and Time in a Formatted Manner in SQL Server

Assume you have a date in you database such as 2015-07-08 00:00:00.000 and you want to show only 2015-07-08 and 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 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:

  1. SELECT  
  2. E.Name,  
  3. E.Email,  
  4. E.ManagerName,  
  5. ED.JoiningDate,  
  6. ED.JoiningTime  
  7. FROM  
  8. Employee AS E  
  9. INNER JOIN EmployeeDetails AS ED ON E.ID = ED.Emp_ID;  
Execute this and see result as in the following:



In the preceding result the Date and Time format is the same as we have in our database. So now we will convert like this:
  1. SELECT  
  2. E.Name,  
  3. E.Email,  
  4. E.ManagerName,  
  5. CONVERT(  
  6. VARCHAR(10),  
  7. ED.JoiningDate,  
  8. 101  
  9. AS JoininDate,  
  10. CONVERT(  
  11. VARCHAR(8),  
  12. ED.JoiningTime,  
  13. 108  
  14. ) + ' ' + RIGHT(  
  15. CONVERT(VARCHAR, ED.JoiningTime, 100),  
  16. 3  
  17. AS JoiningTime  
  18. FROM  
  19. Employee AS E  
  20. INNER JOIN EmployeeDetails AS ED ON E.ID = ED.Emp_ID  
Executed