How to show day name in SQL Server?

Question

I have a query on the SQL server

I want to display as below:

CDATE      | CDAY
2019-04-01 | Monday
2019-04-02 | Tuesday
...        | ......
2019-04-30 | Tuesday

But I found error as below:

Conversion failed when converting date and/or time from character string.

Please if someone can help

DECLARE @V_DATE DATE = GETDATE()

;WITH CTE_DATE AS (
        SELECT  DATEADD(dd,-(DAY(@V_DATE)-1),@V_DATE) CDATE,
        DATENAME(dw, CONVERT(varchar, DATEADD(dd,-(DAY(@V_DATE)-1),@V_DATE))) CDAY
        UNION ALL
        SELECT  DATEADD(dd,1,CDATE),
        DATENAME(dw, CONVERT(varchar, DATEADD(dw,1,CDAY)))
        FROM    CTE_DATE
        WHERE   DATEADD(dd,1,CDATE) <= DATEADD(dd,-(DAY(DATEADD(mm,1,CDATE))),DATEADD(mm,1,CDATE))

    )
    SELECT * FROM CTE_DATE

Chronometer

Take smaller steps. Start with a basic, single SELECT. – Uninhibited

Use the cte to generate dates only, fix the day name in the main query. – Uninhibited

I think you may be able to remove the part where you convert the date to varchar also, depending on what you require. – Peptonize

Answer 1

Your problem is:

DATENAME(dw, DATEADD(dw, 1, CDAY))

I think you intend:

DATENAME(dw, DATEADD(dw, 1, CDATE))

I would write the CTE as:

WITH CTE_DATE AS (
        SELECT  DATEADD(day ,-(DAY(@V_DATE)-1),@V_DATE) as CDATE, 
                DATENAME(dw, DATEADD(day, -(DAY(@V_DATE) - 1), @V_DATE)) as CDAY
        UNION ALL
        SELECT  DATEADD(day, 1, CDATE),
                DATENAME(dw, DATEADD(dw, 1, CDATE))
        FROM    CTE_DATE
        WHERE   DATEADD(day, 1, CDATE) <= DATEADD(day, -(DAY(DATEADD(month, 1, CDATE))), DATEADD(month, 1, CDATE))

    )
    SELECT *
    FROM CTE_DATE;

Here is a db<>fiddle.

You don't describe what you want the code the code to do. It has unnecessary conversions to string and might be needlessly complicated for what you want to do.

Vagabond

baik terima kasih. ini sangat membantu – Chronometer

Answer 2

You can shortly use datename() function( used since v.2008 )

  select datename( weekday, getdate() ) as day

  day
  ------
  Friday  -- > "for today(2019-04-26)"

Demo

or as in your case :

with t(cdate) as
(
 select '2019-04-01' union all
 select '2019-04-02' union all
 select '2019-04-30'    
)    
select cdate, datename( weekday, cdate  ) as cday
  from t;

+----------+-------+
| cdate    | cday  |
+----------+-------+
|2019-04-01|Monday |
|2019-04-02|Tuesday|
|2019-04-30|Tuesday|
+----------+-------+

Pelson

Answer 3

No need to convert to varchar in order to get weekday.

 UNION ALL
        SELECT  DATEADD(dd,1,CDATE),
        DATENAME(dw, CONVERT(varchar, DATEADD(dw,1,CDAY))) -- No need to convert to varchar in order to get weekday.
        FROM    CTE_DATE
        WHERE   DATEADD(dd,1,CDATE) <= DATEADD(dd,-(DAY(DATEADD(mm,1,CDATE))),DATEADD(mm,1,CDATE))

you can directly get it using datename function.

  DECLARE @V_DATE DATE = GETDATE()

    ;WITH CTE_DATE AS (
            SELECT  DATEADD(dd,-(DAY(@V_DATE)-1),@V_DATE) CDATE,
                    DATENAME(dw, CONVERT(varchar, DATEADD(dd,-(DAY(@V_DATE)-1),@V_DATE))) CDAY
            UNION ALL
            SELECT  DATEADD(dd,1,CDATE),
                    DATENAME(dw,  DATEADD(dd,1,CDATE)) -- modified 
            FROM    CTE_DATE
            WHERE   DATEADD(dd,1,CDATE) <= DATEADD(dd,-(DAY(DATEADD(mm,1,CDATE))),DATEADD(mm,1,CDATE))

        )
        SELECT * FROM CTE_DATE

Coral

Care to spend 30 seconds explaining what you have done? – Meier

This looks very similar, it would help readers if you explained the changes you applied to the answer. – Peptonize

Answer 4

No need to fix the day names inside the CTE, use it only to generate the dates.

DECLARE @V_DATE DATE = GETDATE()

WITH CTE_DATE AS
(
    SELECT  DATEADD(day ,-(DAY(@V_DATE)-1),@V_DATE) as CDATE
    UNION ALL
    SELECT  DATEADD(day, 1, CDATE)
    FROM    CTE_DATE
    WHERE   DATEADD(day, 1, CDATE) <= DATEADD(day, -(DAY(DATEADD(month, 1, CDATE))), DATEADD(month, 1, CDATE))
)
SELECT CDATE, DATENAME(dw, CDATE) FROM CTE_DATE

Uninhibited

Answer 5

No need to CONVERT dates. Use the FORMAT function. And use EOMONTH function:

DECLARE @V_DATE DATE = DATEADD(DAY, 1, EOMONTH(GETDATE(), -1));
WITH CTE_DATE AS (
    SELECT @V_DATE CDATE

    UNION ALL

    SELECT DATEADD(dd, 1, CDATE)
    FROM   CTE_DATE
    WHERE  DATEADD(dd, 1, CDATE) <= EOMONTH(@V_DATE)
)
SELECT CDATE, FORMAT(CDATE, 'dddd') AS CDAY, FORMAT(CDATE, 'ddd') AS CDAYSHORT
FROM CTE_DATE

Gaylegayleen