question

asmasm avatar image
asmasm asked

sql query to get bi-weekly data

i want to know the start bi-week and end bi-week dates.

create table #temp(EmployeeID int,TEDate datetime,EmpFNamenvarchar(100),EmpLName nvarchar(100))

go

insert into #temp (EmployeeID ,EmpFName,EmpLName,TEDate) Select 2019,'roz','Ahmad','2019-04-23' union all Select 2019,'roz','Ahmad','2019-04-17' union all Select 2019,'roz','Ahmad','2019-04-29' select * from #temp

sql querysql server 2017sqlserver 2008r2
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
asmasm avatar image
asmasm answered

I used this approach but it is not giving the desired results

select *,DATEADD(WEEK, DATEPART(wk, TEDate), DATEADD(YEAR, year(TEDate) - 1900, 0)) - 4 - DATEPART(DW, DATEADD(WEEK, DATEPART(wk, TEDate), DATEADD(YEAR, year(TEDate) - 1900, 0)) - 4) + 1 AS [BiWEEK_START] ,DATEADD(WEEK, DATEPART(wk, TEDate), DATEADD(YEAR, year(TEDate) - 1900, 0)) - 4 - DATEPART(DW, DATEADD(WEEK, DATEPART(wk, TEDate), DATEADD(YEAR, year(TEDate) - 1900, 0)) - 4) + 14 AS [BiWEEK_END] from #temp

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

asmasm avatar image asmasm commented ·

23 and 17 comes under same biweekly but it is calculating its own biweekly date

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.