CODEWITHSUNDEEP
sqlt-sqlstored-proceduressql-server-2012
SBB
SBB

Reputation: 8970

TSQL Convert Date Format

I have a date format that I need to retrieve from a normal datetime format. A plugin that I am usin requires it to be in the following format: 

Jan 08 2015 12:30

I have been looking through this page which contains all of the formats but I cant see anything that is like that. http://www.sql-server-helper.com/tips/date-formats.aspx

I played around with dateparts and was able to do this for the time:

CONVERT(VARCHAR, DATEPART(hh, a.meetingDate)) + ':' + RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, a.meetingDate)), 2)

I just feel that there would be a more simple way than doing a date part for every single piece.

Any thoughts?

Upvotes: 1

Views: 310

Answers (1)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

This should do it, by looking for double spaces and replacing with a space and a zero.

SELECT REPLACE(CONVERT(CHAR(17), GETDATE(), 100), '  ', ' 0');

Replace the GETDATE with the actual date column you wish to convert.

Note that this returns 12h (AM/PM) time. If you need 24h time, it gets a little uglier.

SELECT SUBSTRING(CONVERT(CHAR(17), GETDATE(), 113), 4,  4) +
       SUBSTRING(CONVERT(CHAR(17), GETDATE(), 113), 1,  3) +
       SUBSTRING(CONVERT(CHAR(17), GETDATE(), 113), 8, 10);

Upvotes: 3

Related Questions