Two columns with dates (one any day of week, another one WEEK ENDING DATE (SATURDAY) BASED ON 1st co-CodePudding

I have such a situation. I need to have 2 columns 1) Is just pull data from a table (just as it is) r.[RCLDTE] (Day of week) and 2 column) I need to basically look at the first column and make it Saturday of that week.

SELECT r.[RCLDTE] AS 'Day of Week'
,r.[RCLDTE] AS 'Week Ending Day (Saturday)'

Before what I was doing at similar projects I just used this code and added to WHERE statement.

WHERE CONVERT(DATE, CONVERT(CHAR(8), r.[RCLDTE] ))  = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) as date)

This code was changing the dates column to Saturday.

However, here I have a different situation. I need 2 columns 1) as it is and 2) where dates will be Saturdays of the week from r.[RCLDTE] column , as a result from the way how I understand I cannot use WHERE statement because it will affect both columns.

Does someone know how I can leave 1st column as it is and 2nd a column of Saturday.

Please let me know.

Thanks.

CodePudding user response：

To avoid issues when someone changes either DATEFIRST or LANGUAGE settings, you can use this. Also, given that you are storing dates in a numeric column for some reason (you really should provide feedback to whoever owns the system so they can fix it), we have to first try to convert those values to a date (they may not all be valid, which is one of the problems with using the wrong data type):

;WITH t AS 
(
  SELECT *, ProperDate = CASE WHEN ISDATE(CONVERT(char(8), RCLDTE)) = 1
    THEN CONVERT(date, CONVERT(char(8), RCLDTE)) END
  FROM dbo.tablename
)
SELECT [Language] = @@language, [Datefirst] = @@datefirst,
       RCLDTE = CASE WHEN ProperDate IS NULL THEN RCLDTE END, 
       [Day of Week] = ProperDate,
       [Saturday] = DATEADD
       (
         DAY, 
         6 - ((DATEPART(WEEKDAY, ProperDate)   @@DATEFIRST - 1) % 7), 
         ProperDate
       )
FROM t;

Updated db<>fiddle that also demonstrates the handling of garbage data and a version of SQL Server so old that TRY_CONVERT() didn't exist yet (at least 14 years ago).

CodePudding user response：

here is one way :

select 
   r.RCLDTE AS 'Day of Week'
   , dateadd(day, 7 - datepart(weekday, r.RCLDTE) , r.RCLDTE)
from tablename r

db<>fiddle here