CODEWITHSUNDEEP
sqlsql-serverstringdatetime
q phan
q phan

Reputation: 99

SQL convert to week day and hh:mm

Not sure can we convert datetime field column, for example:

Contactdate
2020-03-12 16:20:34.000
2020-01-01 00:52:34.000

become

contact date
Wednesday 12:52
Thursday  04:20

Upvotes: 0

Views: 36

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

DECLARE @d table(Contactdate datetime);

INSERT @d VALUES('2020-03-12 16:20:34.000'),('2020-01-01 00:52:34.000');

SELECT contact = DATENAME(WEEKDAY, Contactdate), 
       date    = CONVERT(CHAR(5), Contactdate, 108)
FROM @d;

I tend to stay away from FORMAT() - while convenient and flexible, it does not scale in my testing.

Upvotes: 2

GMB
GMB

Reputation: 222582

In SQL Server, you can use format() for this:

select format(contactdate, N'dddd hh\:mm') formated_date
from mytable

Demo on DB Fiddle:

select contactdate, format(contactdate, N'dddd hh\:mm') formated_date
from (values 
    (cast('2020-03-12 16:20:34.000' as datetime)), 
    (cast('2020-01-01 00:52:34.000' as datetime))
) as t(contactdate)

contactdate             | formated_date  
:---------------------- | :--------------
2020-03-12 16:20:34.000 | Thursday 04:20 
2020-01-01 00:52:34.000 | Wednesday 12:52

Upvotes: 1

Related Questions