查询本周的记录

最新推荐文章于 2024-09-26 01:42:12 发布

huaxue1987

最新推荐文章于 2024-09-26 01:42:12 发布

阅读量324

点赞数

分类专栏：数据库文章标签： date events javascript object 数据库

本文链接：https://blog.csdn.net/huaxue1987/article/details/6596331

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

项目中有个功能需要查询本周（从周日开始到周六）的事件记录。这看似是一件挺容易的事情，但说和做毕竟是两回事。我没有想出太好的办法，因此求助于大家。

问题是这样的：数据库中有一些事件的记录，事件表有关于时间的有年、月和日三个字段，通过javascript Date object提供的函数可以得到当天得年、月、日和星期几，然后要查询这一周得数据。

需要考虑到得情况有以下几种：

1. 本周都在本月中

2. 本周有几天在上个月，这里又有可能上个月是上一年12月

3. 本周有几天在下个月，这里有可能下个月是下一年1月

我想到得只有最直接得方法，多少繁复了一些：我将月份分成大小月份，在情况2时，要判断上个月是几天从而确定本周是从上个月哪一天开始到本周哪一天结束，这时要考虑上个月有几天（可能是31，30，28，也有可能是闰年29），情况3则需考虑本周从本月哪一天开始，到下月哪一天结束，要考虑本月的天数。如各位大牛们有更好的方法，请不吝赐教，万分感谢。

代码附下：

            var strSql;
            
            if (date-day <= 0)
            {
                // this week starts from previous month but in the same year
                var preMonth = month-1;
                var preMonthDays;
                
                if (preMonth != 0)
                {
                    if (preMonth==1 || preMonth==3 || preMonth==5 || preMonth==7 || preMonth==8 || preMonth==10)
                    {
                        // previous month has 31 days
                        preMonthDays=31;
                    }
                    else if (preMonth==4 || preMonth==6 || preMonth==9 || preMonth==11)
                    {
                        // previous month has 30 days
                        preMonthDays=30;
                    }
                    else
                    {
                        // month==2
                        if (year%4!=0 || year%100==0&&year%400!=0)
                        {
                            // a non-leap year, has 28 days
                            preMonthDays=28;
                        }
                        else
                        {
                            // a leap year, has 29 days
                            preMonthDays=29; 
                        }
                    }
                    
                    strSql = 'SELECT * FROM EVENTS WHERE year=' + year + ' AND ( month='+ preMonth + 'AND day>=' + (preMonthDays+date-day) + ' OR month=' + month + ' AND day<=' + (date+6-day);

                }
                else
                {
                    // premonth==0, previos month is the December in previous year
                    strSql = 'SELECT * FROM EVENTS WHERE year=' + (year-1) + ' AND month=12 AND day>=' + (31+date-day) +' OR year=' + year + ' AND month=1 AND day<=' + (date+6-day);
                }
            }
            else
            {
                var thisMonthDays;
                if (month==1 || month==3 || month==5 || month==7 || month==8 || month==10 || month==12)
                {
                    thisMonthDays=31;
                }
                else if (month==4 || month==6 || month==9 || month==11)
                {
                    thisMonthDays=30;
                }
                else
                {
                    if (year%4!=0 || year%100==0&&year%400!=0)
                    {
                        // a non-leap year, has 28 days
                        thisMonthDays=28;
                    }
                    else
                    {
                        // a leap year, has 29 days
                        thisMonthDays=29; 
                    }
                }
                
                if (date+6-day-thisMonthDays>0)
                {
                    // some days of this week are in next month
                    if (month==12)
                    {
                        // some days of this week are in next year January
                        strSql='SELECT * FROM EVENTS WHERE year=' + year + ' AND month=12 AND date>=' + (date-day) + ' OR year=' + (year+1) + ' AND month=1 AND date<=' + (date+6-day-31);
                    }
                    else
                    {
                        strSql='SELECT * FROM EVENTS WHERE year=' + year + ' AND month=12 AND date>=' + (date-day) + ' OR year=' + (year+1) + ' AND month=1 AND date<=' + (date+6-day-thisMonthDays);
                    }
                }
                else
                {
                    strSql='SELECT * FROM EVENTS WHERE year=' + year + ' AND month=' + month + ' AND date>=' + (date-day) + ' And date<=' + (date+6-day);
                }
            }