moe
moe

Reputation: 5249

How to convert field to show date and time in SQL

I have column that looks like this:

Jan  8 2013  2:47PM

but I want to convert and show something like this:

01/08/2013 2:47 PM

I have tried something like this but it does not show the time:

select convert (date, Date,101)as MyDate

Upvotes: 1

Views: 404

Answers (3)

RandomUs1r
RandomUs1r

Reputation: 4190

if your original field is a varchar: you can do something like this:

select CONVERT(varchar(10), CAST('Jan  8 2013  2:47PM' AS DATETIME), 101) + ' ' + RIGHT(CONVERT(VARCHAR, 'Jan  8 2013  2:47PM', 100), 7)

Just replace the string with your field.

Upvotes: 2

Martin
Martin

Reputation: 16423

SQL Server does not provide a way of doing this using a single CONVERT statement, but you can use the following to achieve the objective:

SELECT CONVERT(VARCHAR, date, 101) + RIGHT(CONVERT(VARCHAR, date, 100), 8)

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

You can try something like this:-

   Convert(datetime, '01/08/2013', 103)

Upvotes: 1

Related Questions