5 // Copyright (c) 2007-2008 Jiri Moudry, Pascal Craponne
\r
7 // Permission is hereby granted, free of charge, to any person obtaining a copy
\r
8 // of this software and associated documentation files (the "Software"), to deal
\r
9 // in the Software without restriction, including without limitation the rights
\r
10 // to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
\r
11 // copies of the Software, and to permit persons to whom the Software is
\r
12 // furnished to do so, subject to the following conditions:
\r
14 // The above copyright notice and this permission notice shall be included in
\r
15 // all copies or substantial portions of the Software.
\r
17 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
\r
18 // IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
\r
19 // FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
\r
20 // AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
\r
21 // LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
\r
22 // OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
\r
27 using System.Globalization;
\r
28 using System.Collections.Generic;
\r
31 using System.Linq.Expressions;
\r
32 using NUnit.Framework;
\r
38 using System.Data.Linq;
\r
40 using DbLinq.Data.Linq;
\r
45 namespace Test_NUnit_MySql
\r
47 namespace Test_NUnit_OracleODP
\r
49 namespace Test_NUnit_Oracle
\r
51 namespace Test_NUnit_PostgreSql
\r
53 namespace Test_NUnit_Sqlite
\r
55 namespace Test_NUnit_Ingres
\r
56 #elif MSSQL && L2SQL
\r
57 namespace Test_NUnit_MsSql_Strict
\r
59 namespace Test_NUnit_MsSql
\r
61 namespace Test_NUnit_Firebird
\r
65 public class ReadTests_DateTimeFunctions : TestBase
\r
67 #if !DEBUG && SQLITE
\r
71 public void GetYear()
\r
73 Northwind db = CreateDB();
\r
75 var q = from o in db.Orders
\r
76 where o.OrderDate.Value.Year == 1996
\r
79 var list = q.ToList();
\r
80 Assert.IsTrue(list.Count > 0);
\r
83 #if !DEBUG && SQLITE
\r
87 public void GetMonth()
\r
89 Northwind db = CreateDB();
\r
91 var q = from o in db.Orders
\r
92 where o.OrderDate.Value.Month == 10
\r
95 var list = q.ToList();
\r
96 Assert.IsTrue(list.Count > 0);
\r
99 #if !DEBUG && SQLITE
\r
103 public void GetDay()
\r
105 Northwind db = CreateDB();
\r
107 var q = from o in db.Orders
\r
108 where o.OrderDate.Value.Day == 16
\r
111 var list = q.ToList();
\r
112 Assert.IsTrue(list.Count > 0);
\r
115 #if !DEBUG && SQLITE
\r
119 public void GetHours()
\r
121 Northwind db = CreateDB();
\r
123 var q = (from o in db.Orders
\r
124 where o.OrderDate.Value.Hour == 0
\r
125 select o).ToList();
\r
130 #if !DEBUG && SQLITE
\r
134 public void GetMinutes()
\r
136 Northwind db = CreateDB();
\r
138 var q = (from o in db.Orders
\r
139 where o.OrderDate.Value.Minute == 0
\r
140 select o).ToList();
\r
145 #if !DEBUG && SQLITE
\r
149 public void GetSeconds()
\r
151 Northwind db = CreateDB();
\r
153 var q = (from o in db.Orders
\r
154 where o.OrderDate.Value.Second == 16
\r
155 select o).ToList();
\r
159 #if !DEBUG && SQLITE
\r
163 public void GetMilliSeconds()
\r
165 Northwind db = CreateDB();
\r
167 var q = (from o in db.Orders
\r
168 where o.OrderDate.Value.Millisecond == 0
\r
169 select o).ToList();
\r
173 #if !DEBUG && SQLITE
\r
177 public void GetCurrentDateTime()
\r
179 Northwind db = CreateDB();
\r
180 var query = from e in db.Employees
\r
181 where e.BirthDate.HasValue && e.BirthDate.Value == DateTime.Now
\r
184 var list = query.ToList();
\r
187 #if !DEBUG && SQLITE
\r
191 public void Parse01()
\r
193 Northwind db = CreateDB();
\r
194 var query = from e in db.Employees
\r
195 where e.BirthDate.Value == DateTime.Parse("1984/05/02")
\r
198 var list = query.ToList();
\r
202 [ExpectedException(typeof(NotSupportedException))]
\r
203 public void Parse02()
\r
205 Northwind db = CreateDB();
\r
206 var query = from e in db.Employees
\r
207 where e.BirthDate.Value == DateTime.Parse(e.BirthDate.ToString())
\r
210 var list = query.ToList();
\r
214 public void Parse03()
\r
216 Northwind db = CreateDB();
\r
217 var query = from e in db.Employees
\r
218 where e.BirthDate.HasValue
\r
219 select e.BirthDate.Value == DateTime.Parse("1984/05/02");
\r
222 var list = query.ToList();
\r
226 [ExpectedException(typeof(InvalidOperationException))]
\r
227 public void Parse04()
\r
229 Northwind db = CreateDB();
\r
230 var query = from e in db.Employees
\r
231 select e.BirthDate.Value == DateTime.Parse(e.BirthDate.ToString());
\r
234 var list = query.ToList();
\r
237 #if !DEBUG && SQLITE
\r
241 public void DateTimeDiffTotalHours()
\r
243 Northwind db = CreateDB();
\r
244 var query = from e in db.Employees
\r
245 where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalHours > 0
\r
249 var list = query.ToList();
\r
252 #if !DEBUG && SQLITE
\r
256 public void DateTimeDiffHours()
\r
258 Northwind db = CreateDB();
\r
260 DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddHours(2);
\r
262 var query = from e in db.Employees
\r
263 where (e.BirthDate.Value - parameterDateTime).Hours > -2
\r
267 var list = query.ToList();
\r
268 Assert.Greater(list.Count, 0);
\r
271 #if !DEBUG && SQLITE
\r
275 public void DateTimeDiffTotalMinutes()
\r
277 Northwind db = CreateDB();
\r
278 var query = from e in db.Employees
\r
279 where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalMinutes > 0
\r
283 var list = query.ToList();
\r
286 #if !DEBUG && SQLITE
\r
290 public void DateTimeDiffMinutes()
\r
292 Northwind db = CreateDB();
\r
294 DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddMinutes(2);
\r
296 var query = from e in db.Employees
\r
297 where (e.BirthDate.Value - parameterDateTime).Minutes == -2
\r
301 var list = query.ToList();
\r
302 Assert.Greater(list.Count, 0);
\r
306 #if !DEBUG && SQLITE
\r
310 public void DateTimeDiffTotalSeconds()
\r
312 Northwind db = CreateDB();
\r
313 var query = from e in db.Employees
\r
314 where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalSeconds > 0
\r
318 var list = query.ToList();
\r
321 #if !DEBUG && SQLITE
\r
325 public void DateTimeDiffSeconds()
\r
327 Northwind db = CreateDB();
\r
329 DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddSeconds(2);
\r
331 var query = from e in db.Employees
\r
332 where (e.BirthDate.Value - parameterDateTime).Seconds == -2
\r
336 var list = query.ToList();
\r
337 Assert.Greater(list.Count, 0);
\r
340 #if !DEBUG && (SQLITE || MSSQL)
\r
341 // L2SQL: SQL Server doesnt' seem to support millisecond precision.
\r
345 public void DateTimeDiffMilliseconds()
\r
347 Northwind db = CreateDB();
\r
349 DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddMilliseconds(2);
\r
351 var query = from e in db.Employees
\r
352 where (e.BirthDate.Value - parameterDateTime).Milliseconds == -2
\r
356 var list = query.ToList();
\r
357 Assert.Greater(list.Count, 0);
\r
360 #if !DEBUG && SQLITE
\r
364 public void DateTimeDiffTotalMilliseconds()
\r
366 Northwind db = CreateDB();
\r
367 var query = from e in db.Employees
\r
368 where (e.BirthDate.Value - DateTime.Parse("1984/05/02")).TotalMinutes > 0
\r
372 var list = query.ToList();
\r
375 #if !DEBUG && SQLITE
\r
379 public void DateTimeDiffDays()
\r
381 Northwind db = CreateDB();
\r
383 DateTime parameterDateTime = db.Employees.First().BirthDate.Value.AddDays(2);
\r
385 var query = from e in db.Employees
\r
386 where (e.BirthDate.Value - parameterDateTime).Days == -2
\r
390 var list = query.ToList();
\r
391 Assert.Greater(list.Count, 0);
\r
394 #if !DEBUG && SQLITE
\r
398 public void DateTimeDiffTotalDays()
\r
400 Northwind db = CreateDB();
\r
401 DateTime firstDate = db.Employees.First().BirthDate.Value;
\r
403 DateTime parameterDate = firstDate.Date.AddDays(2);
\r
404 parameterDate = parameterDate.Date.AddHours(12);
\r
407 var query = from e in db.Employees
\r
408 where (e.BirthDate.Value -parameterDate).TotalDays == -2.5
\r
411 var list = query.ToList();
\r
413 Assert.Greater(list.Count, 0);
\r
416 #if !DEBUG && POSTGRES
\r
420 [ExpectedException(typeof(InvalidOperationException))]
\r
421 public void DateTimeDiffTotalDaysSelectWithNulls01()
\r
424 Northwind db = CreateDB();
\r
426 var employee = new Employee
\r
428 FirstName = "Test First",
\r
429 LastName = "Test Last",
\r
431 db.Employees.InsertOnSubmit(employee);
\r
432 db.SubmitChanges();
\r
434 DateTime firstDate = db.Employees.First().BirthDate.Value;
\r
435 firstDate.Date.AddDays(2);
\r
436 DateTime parameterDate = firstDate.Date.AddHours(12);
\r
440 //this test should throw an invalid operation exception since one BirthDate is null so select clausle should crash
\r
441 var query = from e in db.Employees
\r
442 select (e.BirthDate.Value - parameterDate).TotalDays;
\r
444 var list = query.ToList();
\r
446 Assert.Greater(list.Count, 0);
\r
450 db.Employees.DeleteOnSubmit(employee);
\r
451 db.SubmitChanges();
\r
455 #if !DEBUG && POSTGRES
\r
459 public void DateTimeDiffTotalDaysSelectWithNulls02()
\r
461 Northwind db = CreateDB();
\r
463 var employee = new Employee
\r
465 FirstName = "Test First",
\r
466 LastName = "Test Last",
\r
468 db.Employees.InsertOnSubmit(employee);
\r
469 db.SubmitChanges();
\r
471 DateTime firstDate = db.Employees.First().BirthDate.Value;
\r
473 DateTime parameterDate = firstDate.Date.AddDays(2);
\r
474 parameterDate = parameterDate.Date.AddHours(12);
\r
478 var query = from e in db.Employees
\r
479 where e.BirthDate.HasValue
\r
480 select (e.BirthDate.Value - parameterDate).TotalDays;
\r
482 var list = query.ToList();
\r
484 Assert.Greater(list.Count, 0);
\r
488 db.Employees.DeleteOnSubmit(employee);
\r
489 db.SubmitChanges();
\r
494 #if !DEBUG && (SQLITE || (MSSQL && L2SQL))
\r
495 // L2SQL: System.Data.SqlClient.SqlException : The datepart minute is not supported by date function datepart for data type date.
\r
499 public void DateGetDate()
\r
501 Northwind db = CreateDB();
\r
502 var query = from e in db.Employees
\r
503 where (e.BirthDate.Value.Date).Minute == 0
\r
507 var list = query.ToList();
\r