How to convert date and time in SQL Server

Question

I have the following columns in the 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 

Does SQL Server have a function or conversion code? Thank you for your help.

Answer 1

Assuming the columns you are referencing are DATETIME columns, I would use the following code:

Date only

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

Only time

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

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

Answer 2

To use Sign_In_Date

 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) 

Conclusion:

 10:27PM 

Answer 3

try the following:

 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) 

Answer 4

You can use CONVERT to change the date and time format to your desired format:

 SELECT CONVERT(VARCHAR(10),Signed_In_Date,101) as 'Signed_In_Date', CONVERT(VARCHAR(10),Signed_Out_Time,108) as 'Signed_Out_Time'; 

For a more detailed date, follow this link:

http://www.sql-server-helper.com/tips/date-formats.aspx