DateTime的屬性如:Date、Year,方法如:AddDays、AddYears,都會轉成Sql,知道轉譯的方法,對操作Linq To Sql或Entity Framework會更方便上手,下面介紹幾個情況給大家參考,詳細內容請看MSDN。
1.Today或某天的處理如果要找出今天修改的資料,你會怎麼下,像下面這樣嗎?
1from pin Products
2where p.ModifiedDate >= DateTime.Today && p.ModifiedDate < DateTime.Today.AddDays(1)
3select p
1-- 轉譯的Sql
2SELECT *
3FROM [Product]
4WHERE ([ModifiedDate] >='2010-09-13 00:00:00.000' )AND ([ModifiedDate] <'2010-09-14 00:00:00.000')
其實還有更簡單的方式1from pin Products
2where p.ModifiedDate.Date == DateTime.Today
3select p
1-- 轉譯的Sql
2SELECT *
3FROM [Product]
4WHERE CONVERT(DATE,[ModifiedDate])= '2010-09-13 00:00:00.000'
是不是簡單許多呢,當然也可以跟某一天比較,如:1from pin Products
2where p.ModifiedDate.Date == new DateTime(2010,1,1)
3select p
Tip:DateTime.Date,是去掉時間只取日期,而DATE,是Sql Server 2008 的只有日期的資料型態。2.找出某年月的資料
1from pin Products
2where p.ModifiedDate.Year == DateTime.Today.Year && p.ModifiedDate.Month == DateTime.Today.Month
3select p
1-- 轉譯的Sql
2SELECT *
3FROM [Product]
4WHERE (DATEPART(Year,[ModifiedDate])= 2010)AND (DATEPART(Month,
[ModifiedDate])= 9)
3.加半年就過期的資料1from pin Products
2where p.ModifiedDate.AddMonths(6) >= DateTime.Today
3select p
1-- 轉譯的Sql
2SELECT *
3FROM [Product]
4WHERE DATEADD(MONTH,6, [PeriodDate])>= '2010-09-14 00:00:00.000'
Note:用AddHours,AddMinutes,AddSeconds,轉譯的T-Sql,非常的不易閱讀,不過還好平常沒事不用管轉譯結果,長怎麼樣。
如AddHours(6),會轉譯成
1DATEADD(ms, (CONVERT(BigInt,6* 3600000))% 86400000, DATEADD(day, (CONVERT(BigInt,6* 3600000))/ 86400000, [ModifiedDate]))
LINQ