How to convert date and time in SQL Server

Question

I have the following columns in a table:

Signed_In_Date             Signed_Out_Time
11/1/2005 12:00:00 am      11/1/2005 10:27:00PM

I would like to convert them to the following output:

Signed_In_Date      Signed_Out_Time
11/1/2005           10:27:00PM

Is there a function or conversion code in SQL Server that would do it?

Solution 1

Assuming that the columns you're referring to are DATETIME columns, I would use the code below:

Date Only

SELECT CONVERT(VARCHAR(10), GETDATE(), 101)

Time Only

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))

You can see the queries in action / play with them here.

Solution 2

For Sign_In_Date Use

select CONVERT(VARCHAR(10),'11/1/2005 10:27:00PM',108)

Ouput:

11/1/2005 

For Sing_Out_Time

declare @time time
set @time=cast('11/1/2005 10:27:00PM' as Time)
select convert(varchar(10),@time,100)

Output:

10:27PM

Solution 3

try that :

select CONVERT(VARCHAR(10),Signed_Out_Time,108)  ,-- 108 is d/M/yyyy if you want mm/dd/yyy you should use 101
CONVERT(VARCHAR(8),Signed_In_Date,103)