CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Hallowen
Hallowen

Reputation: 133

Return Friday if day is weekend (Saturday-Sunday) in SQL Server

I am storing history data as a date type (2019-10-10) in SQL Server. I would like to check and return to Friday if day is Saturday or Sunday.

There is no data at the weekend. That is why I want to get Friday's data if it's weekend.

Upvotes: 1

Views: 9703

Answers (7)

hossein andarkhora
hossein andarkhora

Reputation: 798

for specific date use this command

DATENAME(dw,CAST(YourSpecificDate AS DATE))

so you can check your date as like as this command :

select 
 case when DATENAME(dw,CAST(YourSpecificDate AS DATE)) in
 ('Saturday','Sunday') then 'Friday' else DATENAME(dw,CAST('' AS DATE)) end

Upvotes: 0

Hallowen
Hallowen

Reputation: 133

Days are equals to the numbers in sql server:

7 -> Sunday
1 -> Monday
2 -> Tuesday
3 -> Wednesday
4 -> Thursday
5 -> Friday
6 -> Saturday

DATEPART(DW, GETDATE()) gives you current day as a number.

Use it in IF AND ELSE in ms sql server.

FOR EXAMPLE:

If day is Saturday(6) use minus 1 to get friday.

IF(DATEPART(DW, GETDATE()) = 6)
SELECT * FROM table WHERE date=dateadd(day, -1, cast(GETDATE() as date))

Upvotes: -1

HABO
HABO

Reputation: 15816

( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1 will always return an integer from 1 to 7 with 1 corresponding to Sunday regardless of the setting of DateFirst or Language.

You can use the following code to push weekend dates back to the prior Friday:

-- Get the current date (without time).
declare @Today as Date = GetDate();
-- Move Saturday or Sunday dates back to the prior Friday.
select @Today as Today,
  case ( @@DateFirst + DatePart( weekday, @Today ) - 1 ) % 7 + 1
    when 1 then DateAdd( day, -2, @Today ) -- Sunday.
    when 7 then DateAdd( day, -1, @Today ) -- Saturday.
    else @Today end as ReportDate;

Upvotes: 6

Himanshu
Himanshu

Reputation: 3970

You need an update as below

        Update table set column=(select 
          column
         From table where to_char(date, 
         'Day') ='FRI') where to_char(date, 
         'Day') LIKE 'S%' ;

Upvotes: 0

VBoka
VBoka

Reputation: 9083

Hope this helps:

Create table for test:

create table HistorySave(Datum date);

Insert some test values:

Insert into HistorySave values (CONVERT(DATETIME, '2012-06-05', 102));
Insert into HistorySave values (CONVERT(DATETIME, '2012-06-04', 102));
Insert into HistorySave values (CONVERT(DATETIME, '2012-06-03', 102));
Insert into HistorySave values (CONVERT(DATETIME, '2012-06-09', 102));

Check what are the days in the inserted data

SELECT DATENAME(DW, CONVERT(DATETIME, DATUM, 102)) FROM HistorySave;

Check the data before update:

select * from HistorySave;

Update statement:

UPDATE HistorySave  
SET Datum = 
    CASE  
        WHEN DATENAME(DW, CONVERT(DATETIME, Datum, 102)) = 'Saturday' 
            THEN (CONVERT(DATETIME, Datum, 102) -1)
        WHEN DATENAME(DW, CONVERT(DATETIME, Datum, 102)) = 'Sunday' 
            THEN (CONVERT(DATETIME, Datum, 102) -2)
        ELSE 
            DATUM
    END

Check the data after update:

select * from HistorySave;

Demo:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=358aaefe516da8dd96e1da73ce4d5f38

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

The safest method is probably to use format() for the day of the week:

select (case format(col, 'ddd', 'en-us')
           when 'Sat' then dateadd(day, -1, col)
           when 'Sun' then dateadd(day, -2, col)
           else col
        end)

Because format takes a culture argument, this works regardless of settings that might affect the date format or language returned by datename().

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81960

One option is to use Choose() in concert with DateAdd() Example

Example

Declare @YourTable table (DateCol date)
Insert Into @YourTable values
 ('2019-10-14')
,('2019-10-15')
,('2019-10-16')
,('2019-10-17')
,('2019-10-18')
,('2019-10-19')
,('2019-10-20')

Select DateCol
      ,ActDay  = datename(WEEKDAY,DateCol)
      ,AdjDay  = dateadd(DAY,choose(DatePart(WEEKDAY,DateCol),-2,0,0,0,0,0,-1),DateCol)
 From  @YourTable

Returns

DateCol     ActDay      AdjDay
2019-10-14  Monday      2019-10-14
2019-10-15  Tuesday     2019-10-15
2019-10-16  Wednesday   2019-10-16
2019-10-17  Thursday    2019-10-17
2019-10-18  Friday      2019-10-18
2019-10-19  Saturday    2019-10-18  << Friday's Date
2019-10-20  Sunday      2019-10-18  << Friday's Date

Upvotes: 1

Related Questions