CODEWITHSUNDEEP
sqlsql-serverdateformattingformat
Santosh Jadi
Santosh Jadi

Reputation: 1527

Convert SQL Server Date Format

I'm using this query:

select convert(nvarchar(MAX), getdate(), 100);

It's returning 

Aug 16 2018  3:45PM

I'm trying to get this date format instead:

16 AUG 15:45

Please help me achieve it. Thanks

Upvotes: 1

Views: 104

Answers (5)

Sainadh G
Sainadh G

Reputation: 11

Try this

Query:

select UPPER(FOrmat(GETDATE(),'dd MMM HH:mm'))

Result: 16 AUG 17:04

enter image description here

Upvotes: 1

Thom A
Thom A

Reputation: 95561

This gets you what you want, and without using the awfully slow FORMAT function:

SELECT D, CONVERT(varchar(6),DATEADD(HOUR, -1, D),13) + ' ' + CONVERT(varchar(5),CONVERT(time(0),DATEADD(HOUR, -1, D)),14)
FROM (VALUES(CONVERT(datetime2(0),'2018-08-16T15:45:00'))) V(d);

Edit: Seems the OP moved the goals posts again (initially they wanted the time changed from 01:38 AM to 13:38, and then 03:45 PM to 14:45), and as a result DATEADD isn't required. I haven't bothered removing this though, as it was correct at the time; and I don't trust the goal posts won't move again.

Upvotes: 2

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use upper and 'dd MMM HH:mm', 'en-US' format

SELECT Upper( FORMAT( getdate(), 'dd MMM HH:mm', 'en-US' ))

it reutrns 16 AUG 11:03

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try this: with format function

SELECT upper(FORMAT( getdate(), 'dd MMM HH:mm', 'en-US'))

Upvotes: 2

ravi polara
ravi polara

Reputation: 572

Try this

upper case 'Aug' to 'AUG'

SELECT UPPER( FORMAT( GETUTCDATE(), 'dd MMM HH:mm', 'en-US' ) )

Upvotes: 1

Related Questions