I have this code in crystal reports that gives me last week date range based on the current date.
First day of the week:
If DayOfWeek(currentdate) = 2 Then
currentdate
Else If DayOfWeek(currentdate) = 3 Then
dateadd ("d",-1,currentdate)
Else If DayOfWeek(currentdate) = 4 Then
dateadd ("d",-2,currentdate)
Else If DayOfWeek(currentdate) = 5 Then
dateadd ("d",-3,currentdate)
Else If DayOfWeek(currentdate) = 6 Then
dateadd ("d",-4,currentdate)
Else If DayOfWeek(currentdate) = 7 Then
dateadd ("d",-5,currentdate)
Else If DayOfWeek(currentdate) = 1 Then
dateadd ("d",-6,currentdate)
Last day of week:
If DayOfWeek(currentdate) = 2 Then
dateadd ("d",+6,currentdate)
Else If DayOfWeek(currentdate) = 3 Then
dateadd ("d",+5,currentdate)
Else If DayOfWeek(currentdate) = 4 Then
dateadd ("d",+4,currentdate)
Else If DayOfWeek(currentdate) = 5 Then
dateadd ("d",+3,currentdate)
Else If DayOfWeek(currentdate) = 6 Then
dateadd ("d",+2,currentdate)
Else If DayOfWeek(currentdate) = 7 Then
dateadd ("d",+1,currentdate)
Else If DayOfWeek(currentdate) = 1 then currentdate
How can I do the same in SQL using 2 variables to storage Monday( startdate
) and Sunday( enddate
)?
I found this select datepart(dw,getdate()) --6
in this site, but I do not know how to use it.
6条答案
按热度按时间qojgxg4l1#
I generated some spaced out dates in the
parms
CTE thenSELECT
theCurrentDate
fromparms
, the Sunday of the week prior toCurrentDate
and the Saturday of the week prior toCurrentDate
. I'm assuming that you want the dtate range to be Sunday - Saturday.Sunday - Saturday Ranges
Monday to Sunday Ranges
If you just want the prior week's Monday to the prior week's Sunday from today rather than from a column of dates you can use this
5f0d552i2#
This solution is tested and works. I am getting the previous week's Monday and Sunday as upper and lower bounds.
Which can be used like this in a real world query:
Here are some tests:
1. Leap Year
Current Date:
2016-02-29 00:00:00.000
Results:
2. Last Week was in different year
Current Date:
2016-01-06 00:00:00.000
3. Lower limit in previous month and upper limit in current month
Current Date:
2016-05-04 00:00:00.000
4. Current Date is Sunday
Current Date:
2016-05-08 00:00:00.000
a64a0gku3#
Bill's code worked well, but I needed to make a slight edit if I wanted to get last Sunday to Saturday based on today's date.
Rgds - Matt
wn9m85ua4#
对于所有与日期和日期间隔相关的内容,我几乎总是推荐使用日历表。一个表中包含表示日期、周、月、年、星期几、hiliday、weekand等的列,根据需要预先填充,并对每一列进行索引。每年只有365行。
fdbelqdn5#
A shortened version concise syntax
last week, in my vision is the prev adjacent week, so if Im doing report during the week days, I want to receive dates of previous adjacent week Monday to Sunday.
select * from XXX where DateCol between DATE_ADD(CURDATE(), INTERVAL -(5 + dayofweek(CURDATE())) DAY) and DATE_ADD(CURDATE(), INTERVAL -(dayofweek(CURDATE()) - 1) DAY) order by DateCol
8tntrjer6#
I'm late to this party, but wanted to add a simpler way that works for me in SQL Server (when the other examples did not).
-- this is for the previous week:
-- alternatively, this is for a rolling 7 days: