Sql Server - Linq , SQL时间相关 - SQL日期,时间比较

最新推荐文章于 2024-07-26 00:00:00 发布

原小明

最新推荐文章于 2024-07-26 00:00:00 发布

阅读量4.2k

点赞数

文章标签： sql server sql select 时间比较

本文链接：https://blog.csdn.net/LABLENET/article/details/51542112

版权

SQL Server 中时间比较

例子:

select count(*) from table where DATEDIFF ([second], ‘2004-09-18
00:00:18’, ‘2004-09-18 00:00:19’) > 0

说明

select DATEDIFF(day, time1 , time2) 对应示例语句如下

　　select DATEDIFF(day, ‘2010-07-23 0:41:18’, ‘2010-07-23 23:41:18’)

　　time1 > time2 为负数;

　　time1 < time2 为正数;

　　[day] :只会比较 2010-07-23 忽略 0:41:18’ 其他同理

以下分别:

年: SELECT DATEDIFF([year],time1 , time2) 返回值： -6 ，说明是后减前与 mysql

　　月: SELECT DATEDIFF([month], time1 , time2)

　　天: SELECT DATEDIFF([day], time1 , time2)

　　小时: SELECT DATEDIFF([hour], time1 , time2)

　　秒: SELECT DATEDIFF([second], time1 , time2)

Entity Framework（LINQ to Entities）使用日期判断条件Truncate日期函数,类似Convert函数，MYSQL数据库可以直接使用convert(varchar(10),a.cjrq,120)

LINQ to Entities使用日期字段查询，截取日期，不包括时间，测试环境SQL Server2005/2008，Entity Framework4.0

        public void TestMethod1()
        {
            using (var _context = new hotelEntities())
            {
                var rq = DateTime.Now.Date;
                var query = from q in _context.UV_RZJL_RZRY_Single
                             where EntityFunctions.TruncateTime(q.LDRQ) >=rq
                             select q;
                Assert.Inconclusive(query.Count().ToString());              
            }    
        }


//SqlFunctions.DateDiff的函数也可以实现

        public void TestMethod1()
        {
            using (var _context = new hotelEntities())
            {
                var rq = DateTime.Now.Date;
                var query = from q in _context.UV_RZJL_RZRY_Single
                             where SqlFunctions.DateDiff("day",rq,q.LDRQ)>0
                             select q;
                Assert.Inconclusive(query.Count().ToString());              

            }    
        }

***筛选本周数据***

            //获取当天的数据
            DrawRecordDA _recordDA = new DrawRecordDA();
            var query = _recordDA.GetQuery();

            //筛选 当天
            //query = query.Where(q=>SqlFunctions.DateDiff("day",q.AddTime,DateTime.Now)==0);

            //筛选 当天
            //  query = query.Where(q=>q.AddTime.Day==DateTime.Now.Day);
            //筛选 本周
            query = query.Where(q => SqlFunctions.DateDiff("week", q.AddTime, DateTime.Now) == 0);

            Console.WriteLine(query.Count());