-1

how to create two variables with a date for the procedure, regardless of when it starts: 1st variable - 8:00 of the last Monday 2nd variable - 8:00 of the last but one Monday (i.e. the difference between the variables is a week)

I have tried this, but it shows time 00:00

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 7)

Luna
  • 57
  • 5
  • 3
    Please show us what you have tried and where you got stuck. – Dale K Jun 16 '20 at 06:53
  • 3
    Do any of these answers help? [Get first day of week in SQL Server](https://stackoverflow.com/q/7168874/1048425), or [finding the start day (Monday) of the current week](https://stackoverflow.com/q/3936844/1048425) or [SQL Server : get next relative day of week. (Next Monday, Tuesday, Wed…)](https://stackoverflow.com/q/18920393/1048425) – GarethD Jun 16 '20 at 06:53
  • I have tried this, but it shows time 00:00, I need 8:00SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0) SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 7) – Luna Jun 16 '20 at 08:43

1 Answers1

1

Not sure this answer your question, but the below returns the date in european format with 8 AM as time. If you need the american format, you might need to change the dateadd() function.

A bit nested, but have a look if this helps

EDIT: Made it simpler and modified your initial code as well.

set DATEFIRST 1 --Monday

select

cast(concat(convert(varchar,dateadd(dd,1 - datepart(dw,getdate()),getdate()),101),' 8:00') as datetime)
,cast(concat(convert(varchar,dateadd(dd,1 - datepart(dw,getdate()),getdate()+7),101),' 8:00') as datetime)

See if this works.

Salz Mefan
  • 51
  • 4