How to calculate the 7 weeks pay period prior to the recent week ending pay

Transact-SQL
1

How to calculate the 7 weeks pay period prior to the recent WRK_END_DATE date. Please help. Thanks in advance.
Here's my sample of query and it returns the recent date 2020-02-23. I want to get 7 weeks from this date.
SELECT MAX(WRK_END_DATE) AS Last_Week_End_Date
FROM PRTIME
where WRK_END_DATE < Getdate() - WHAT?

2

The q is very vague, but something like this maybe:

where WRK_END_DATE < DATE_ADD(CURDATE(), INTERVAL -49 DAYS)

3

Tried it and got an error Msg 'CURDATE' is not a recognized built-in function name
SELECT MAX(WRK_END_DATE) as endate
FROM PRTIME
WHERE EMPLOYEE = 1812
and max(WRK_END_DATE) < DATE_ADD(CURDATE(), INTERVAL -49 DAYS)

4

Sorry, that was the best guess I had for MySQL. I'm a SQL Server person.

5

hi

please see below link .. hope it helps !!! :slight_smile:

6

I am still getting the error "'CURDATE' is not a recognized built-in function name"
This is my query. Please help
select EMPLOYEE, SUM(WAGE_AMOUNT) as Pay_Amount, WRK_END_DATE, datepart(WK,max(WRK_END_DATE)) [week]
from PRTIME
WHERE EMPLOYEE = 1812
and MAX(WRK_END_DATE) <DATE_ADD(CURDATE(), INTERVAL -49 DAY)
group by WRK_END_DATE, EMPLOYEE, WAGE_AMOUNT

7

what that means is !!!

CURDATE is not available in MYSql
or
it may be a spelling mistake
or
you will have to find equivalent function in MYSql

Hope this helps !! :slight_smile:

1 Like
8

What is the function in mysql to get the current date? Now()? Or what? Replace it with that. This is not a mysql forum but people are trying to help you.