SQL查询：
SELECT
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 7 DAY) as lastweekmonday,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 1 DAY) as lastweeksunday,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) DAY) as monday,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) as sunday,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 7 DAY) as nextweekmonday,
DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 13 DAY) as nextweeksunday;
# 查询
SELECT
t1.id,
t1.title,
t1.date,
t1.time,
t1.attendee,
t1.place,
WEEKDAY(t1.date) as weekday,
CASE WHEN TIME( t1.time ) BETWEEN '01:00:00'
AND '12:00:00' THEN 1
WHEN TIME( t1.time ) BETWEEN '12:00:00'
AND '17:00:00' THEN 2
WHEN TIME( t1.time ) BETWEEN '17:00:00'
AND '24:00:00' THEN 3
END as sd
FROM schedule t1
WHERE 1=1
AND DATE_FORMAT(t1.date,'%y%m%d') >= date_format(#{begindate},'%y%m%d')
AND DATE_FORMAT(t1.date,'%y%m%d') <= date_format(#{enddate},'%y%m%d')
ORDER BY weekday, sd
JS获取：
function ymd(day){
return `${day.getFullYear()}-${
day.getMonth() + 1 < 10
? '0' + (day.getMonth() + 1)
: day.getMonth() + 1
}-${
day.getDate() < 10
? '0' + day.getDate()
: day.getDate()
}`
}
function md(day){
return `${
day.getMonth() + 1 < 10
? '0' + (day.getMonth() + 1)
: day.getMonth() + 1
}-${
day.getDate() < 10
? '0' + day.getDate()
: day.getDate()
}`
}
function cnweek(index){
const ws = ['星期日','星期一','星期二','星期三','星期四','星期五','星期六'];
return ws[index];
}
function weeks(s){
const ws = [];
const w1 = new Date(s);
let i = 0;
while(i<7){
ws.push(`${cnweek(w1.getDay())} ${md(w1)}`);
w1.setDate(w1.getDate() + 1);
i++;
}
return ws;
}
function curWeek() {
//获取当前时间
const currentDate = new Date()
//返回date是一周中的某一天
const week = currentDate.getDay()
//一天的毫秒数
const millisecond = 1000 * 60 * 60 * 24
//减去的天数
const minusDay = week != 0 ? week - 1 : 6
//本周 周一
const monday = new Date(currentDate.getTime() - minusDay * millisecond)
//本周 周日
const sunday = new Date(monday.getTime() + 6 * millisecond)
let startDate = ymd(monday)
let endDate = ymd(sunday)
return [startDate, endDate]
}
function prevWeek(day) {
const Time = !!day?new Date(day):new Date()
let weekNum = Time.getDay()
weekNum = weekNum == 0 ? 7 : weekNum
let lastDate = new Date(Time.getTime() - weekNum * 24 * 60 * 60 * 1000)
let fitstDate = new Date(
Time.getTime() - (weekNum + 6) * 24 * 60 * 60 * 1000
)
let startDate = ymd(fitstDate)
let endDate = ymd(lastDate)
return [startDate, endDate]
}
function nextWeek(day) {
const Time = !!day?new Date(day):new Date()
let weekNum = Time.getDay()
weekNum = weekNum == 0 ? 7 : weekNum
let fitstDate = new Date(
Time.getTime() + (7 - weekNum + 1) * 24 * 60 * 60 * 1000
)
let lastDate = new Date(
Time.getTime() + (7 - weekNum + 7) * 24 * 60 * 60 * 1000
)
let startDate = ymd(fitstDate)
let endDate = ymd(lastDate)
return [startDate, endDate]
}
console.log(prevWeek());
console.log(curWeek());
console.log(nextWeek());
console.log("===============================================================")
console.log(prevWeek('2022-10-24'));
console.log(prevWeek('2022-10-17'));
console.log(nextWeek('2022-10-17'));
console.log(weeks('2022-10-31'));
JAVA获取：
package atest;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;
public class Test日历获取周起止日期 {
public static String curWeek() {
Calendar cal = Calendar.getInstance(Locale.CHINA);
// cal.set(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DAY_OF_MONTH), 0, 0, 0);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// 从星期日开始
cal.setFirstDayOfWeek(Calendar.MONDAY);
// int dayOfWeek = cal.get(Calendar.DAY_OF_WEEK);
// System.out.println(dayOfWeek);
// 获取本周一和周日的日期
// System.out.println(cal.getTime());
cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
String start = sdf.format(cal.getTime());
cal.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
String end = sdf.format(cal.getTime());
// 获取上周的周一和周日的日期
// cal.clear();// 1970-01-01
// System.out.println(sdf.format(cal.getTime()));
return String.format("{ start: %s, end: %s }", start, end);
}
public static Date parseDate(String strDate) {
Date dt = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
dt = sdf.parse(strDate);
} catch (Exception ex) {
System.out.println("Exception occur in parseDate(strDate,fmt)," + ex.getMessage());
}
return dt;
}
public static String prevWeek(String day) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance(Locale.CHINA);
Date date = parseDate(day);
cal.setTime(date);
cal.add(Calendar.WEEK_OF_YEAR, -1);
cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
String start = sdf.format(cal.getTime());
cal.add(Calendar.DAY_OF_WEEK, 6);
String end = sdf.format(cal.getTime());
return String.format("{ start: %s, end: %s }", start, end);
}
public static String nextWeek(String day) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Calendar cal = Calendar.getInstance(Locale.CHINA);
Date date = parseDate(day);
cal.setTime(date);
cal.add(Calendar.WEEK_OF_YEAR, 1);
cal.set(Calendar.DAY_OF_WEEK, Calendar.MONDAY);
String start = sdf.format(cal.getTime());
cal.add(Calendar.DAY_OF_WEEK, 6);
String end = sdf.format(cal.getTime());
return String.format("{ start: %s, end: %s }", start, end);
}
public static void main(String[] args) {
Long a = null;
String str = String.valueOf(a);
System.out.println(String.valueOf(a));
System.out.println("".length());
System.out.println("curweek ===> " + curWeek());
System.out.println("prevweek ===> " + prevWeek("2022-10-31"));
System.out.println("nextWeek ===> " + nextWeek("2022-11-02"));
}
}
未经允许不得转载：
红吉他 »
MySQL »
周起止日期为查询条件