显示日期为dddd Mmmm d，yyyy SQL

Trying to show the date as Thursday, August 2, 2018 format. Its stored as 20180802 in the table. The column type is set to char. Any help is appreciated.

试图将日期显示为2018年8月2日星期四格式。它在表中存储为20180802。列类型设置为char。任何帮助表示赞赏。

I'm using SQL Server 2014.

我正在使用SQL Server 2014。

Thanks

3 个解决方案

#1

Use the following with DateName and convert functions :

使用以下DateName和转换函数:

select DateName( month , q.dt )+' '+convert(char,day(q.dt))+', '+convert(char,year(q.dt))
  from        
     (
      select convert(date,'20180802') as dt
     ) q;

SQL Fiddle Demo

SQL小提琴演示

With respect to your last comment make your query as :

关于您的上一条评论,请将您的查询设为:

select DateName( weekday , q.dt )+' '+
       DateName( month , q.dt )+' '+
       convert(char,day(q.dt))+', '+convert(char,year(q.dt)) as "Date"
  from        
     (
      select convert(date,'20180802') as dt
     ) q;

Date
------------------------
Thursday, August 2, 2018

#2

Adding to Barbaros Özhan response,

加入BarbarosÖzhan的回应,

SELECT DateName(dw,q.dt) +', '+DateName(month, q.dt)+' '+CONVERT(VARCHAR, DAY(q.dt))+', '+CONVERT(VARCHAR, YEAR(q.dt))
FROM
(
    SELECT CONVERT(DATE, '20180802') AS dt
) q;

#3

Since you are 2014, you can use format().

自2014年起,您可以使用format()。

Format() has some great functionality, but it is not known to be a performer.

Format()有一些很棒的功能,但不知道它是一个表演者。

Also, since your "date" is stored as a string, I would suggest try_convert() or try_cast() to allow for any unexpected data.

此外,由于您的“日期”存储为字符串,我建议使用try_convert()或try_cast()来允许任何意外数据。

Example

Select format(try_convert(date,'20180802'),'dddd, MMMM d, yyyy')

Returns

Thursday, August 2, 2018

#1