CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005datetime
joe
joe

Reputation: 1473

How to convert date and time in SQL Server

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?

Upvotes: 4

Views: 16558

Answers (4)

user1082916
user1082916

Reputation:

You can use CONVERT to change datetime format to your own desirable 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 more date format, go over this link:

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

Upvotes: 1

Kishore Kumar
Kishore Kumar

Reputation: 12874

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

Upvotes: 2

Royi Namir
Royi Namir

Reputation: 148524

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)

Upvotes: 1

James Hill
James Hill

Reputation: 61802

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.

Upvotes: 4

Related Questions