|
0
|
1 using System;
|
|
|
2 using System.Linq;
|
|
|
3
|
|
|
4 using NUnit.Framework;
|
|
|
5
|
|
|
6 using BLToolkit.Data.DataProvider;
|
|
|
7 using BLToolkit.Data.Linq;
|
|
|
8
|
|
|
9 namespace Data.Linq
|
|
|
10 {
|
|
|
11 [TestFixture]
|
|
|
12 public class DateTimeFunctions : TestBase
|
|
|
13 {
|
|
|
14 [Test]
|
|
|
15 public void GetDate()
|
|
|
16 {
|
|
|
17 ForEachProvider(db =>
|
|
|
18 {
|
|
|
19 var q = from p in db.Person where p.ID == 1 select new { Now = Sql.AsSql(Sql.GetDate()) };
|
|
|
20 Assert.AreEqual(DateTime.Now.Year, q.ToList().First().Now.Year);
|
|
|
21 });
|
|
|
22 }
|
|
|
23
|
|
|
24 [Test]
|
|
|
25 public void CurrentTimestamp()
|
|
|
26 {
|
|
|
27 ForEachProvider(db =>
|
|
|
28 {
|
|
|
29 var q = from p in db.Person where p.ID == 1 select new { Now = Sql.CurrentTimestamp };
|
|
|
30 Assert.AreEqual(DateTime.Now.Year, q.ToList().First().Now.Year);
|
|
|
31 });
|
|
|
32 }
|
|
|
33
|
|
|
34 [Test]
|
|
|
35 public void Now()
|
|
|
36 {
|
|
|
37 ForEachProvider(db =>
|
|
|
38 {
|
|
|
39 var q = from p in db.Person where p.ID == 1 select new { DateTime.Now };
|
|
|
40 Assert.AreEqual(DateTime.Now.Year, q.ToList().First().Now.Year);
|
|
|
41 });
|
|
|
42 }
|
|
|
43
|
|
|
44 [Test]
|
|
|
45 public void Parse1()
|
|
|
46 {
|
|
|
47 ForEachProvider(db => AreEqual(
|
|
|
48 from d in from t in Types select DateTime.Parse(Sql.ConvertTo<string>.From(t.DateTimeValue)) where d.Day > 0 select d.Date,
|
|
|
49 from d in from t in db.Types select DateTime.Parse(Sql.ConvertTo<string>.From(t.DateTimeValue)) where d.Day > 0 select d.Date));
|
|
|
50 }
|
|
|
51
|
|
|
52 [Test]
|
|
|
53 public void Parse2()
|
|
|
54 {
|
|
|
55 ForEachProvider(db => AreEqual(
|
|
|
56 from d in from t in Types select DateTime.Parse(t.DateTimeValue.Year + "-02-24 00:00:00") where d.Day > 0 select d,
|
|
|
57 from d in from t in db.Types select Sql.AsSql(DateTime.Parse(t.DateTimeValue.Year + "-02-24 00:00:00")) where d.Day > 0 select d));
|
|
|
58 }
|
|
|
59
|
|
|
60 #region DatePart
|
|
|
61
|
|
|
62 [Test]
|
|
|
63 public void DatePartYear()
|
|
|
64 {
|
|
|
65 ForEachProvider(db => AreEqual(
|
|
|
66 from t in Types select Sql.DatePart(Sql.DateParts.Year, t.DateTimeValue),
|
|
|
67 from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Year, t.DateTimeValue))));
|
|
|
68 }
|
|
|
69
|
|
|
70 [Test]
|
|
|
71 public void DatePartQuarter()
|
|
|
72 {
|
|
|
73 ForEachProvider(db => AreEqual(
|
|
|
74 from t in Types select Sql.DatePart(Sql.DateParts.Quarter, t.DateTimeValue),
|
|
|
75 from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Quarter, t.DateTimeValue))));
|
|
|
76 }
|
|
|
77
|
|
|
78 [Test]
|
|
|
79 public void DatePartMonth()
|
|
|
80 {
|
|
|
81 ForEachProvider(db => AreEqual(
|
|
|
82 from t in Types select Sql.DatePart(Sql.DateParts.Month, t.DateTimeValue),
|
|
|
83 from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Month, t.DateTimeValue))));
|
|
|
84 }
|
|
|
85
|
|
|
86 [Test]
|
|
|
87 public void DatePartDayOfYear()
|
|
|
88 {
|
|
|
89 ForEachProvider(db => AreEqual(
|
|
|
90 from t in Types select Sql.DatePart(Sql.DateParts.DayOfYear, t.DateTimeValue),
|
|
|
91 from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.DayOfYear, t.DateTimeValue))));
|
|
|
92 }
|
|
|
93
|
|
|
94 [Test]
|
|
|
95 public void DatePartDay()
|
|
|
96 {
|
|
|
97 ForEachProvider(db => AreEqual(
|
|
|
98 from t in Types select Sql.DatePart(Sql.DateParts.Day, t.DateTimeValue),
|
|
|
99 from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Day, t.DateTimeValue))));
|
|
|
100 }
|
|
|
101
|
|
|
102 [Test]
|
|
|
103 public void DatePartWeek()
|
|
|
104 {
|
|
|
105 ForEachProvider(db =>
|
|
|
106 (from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Week, t.DateTimeValue))).ToList());
|
|
|
107 }
|
|
|
108
|
|
|
109 [Test]
|
|
|
110 public void DatePartWeekDay()
|
|
|
111 {
|
|
|
112 ForEachProvider(db => AreEqual(
|
|
|
113 from t in Types select Sql.DatePart(Sql.DateParts.WeekDay, t.DateTimeValue),
|
|
|
114 from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.WeekDay, t.DateTimeValue))));
|
|
|
115 }
|
|
|
116
|
|
|
117 [Test]
|
|
|
118 public void DatePartHour()
|
|
|
119 {
|
|
|
120 ForEachProvider(db => AreEqual(
|
|
|
121 from t in Types select Sql.DatePart(Sql.DateParts.Hour, t.DateTimeValue),
|
|
|
122 from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Hour, t.DateTimeValue))));
|
|
|
123 }
|
|
|
124
|
|
|
125 [Test]
|
|
|
126 public void DatePartMinute()
|
|
|
127 {
|
|
|
128 ForEachProvider(db => AreEqual(
|
|
|
129 from t in Types select Sql.DatePart(Sql.DateParts.Minute, t.DateTimeValue),
|
|
|
130 from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Minute, t.DateTimeValue))));
|
|
|
131 }
|
|
|
132
|
|
|
133 [Test]
|
|
|
134 public void DatePartSecond()
|
|
|
135 {
|
|
|
136 ForEachProvider(db => AreEqual(
|
|
|
137 from t in Types select Sql.DatePart(Sql.DateParts.Second, t.DateTimeValue),
|
|
|
138 from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Second, t.DateTimeValue))));
|
|
|
139 }
|
|
|
140
|
|
|
141 [Test]
|
|
|
142 public void DatePartMillisecond()
|
|
|
143 {
|
|
|
144 ForEachProvider(new[] { ProviderName.Informix, ProviderName.MySql, ProviderName.Access }, db => AreEqual(
|
|
|
145 from t in Types select Sql.DatePart(Sql.DateParts.Millisecond, t.DateTimeValue),
|
|
|
146 from t in db.Types select Sql.AsSql(Sql.DatePart(Sql.DateParts.Millisecond, t.DateTimeValue))));
|
|
|
147 }
|
|
|
148
|
|
|
149 [Test]
|
|
|
150 public void Year()
|
|
|
151 {
|
|
|
152 ForEachProvider(db => AreEqual(
|
|
|
153 from t in Types select t.DateTimeValue.Year,
|
|
|
154 from t in db.Types select Sql.AsSql(t.DateTimeValue.Year)));
|
|
|
155 }
|
|
|
156
|
|
|
157 [Test]
|
|
|
158 public void Month()
|
|
|
159 {
|
|
|
160 ForEachProvider(db => AreEqual(
|
|
|
161 from t in Types select t.DateTimeValue.Month,
|
|
|
162 from t in db.Types select Sql.AsSql(t.DateTimeValue.Month)));
|
|
|
163 }
|
|
|
164
|
|
|
165 [Test]
|
|
|
166 public void DayOfYear()
|
|
|
167 {
|
|
|
168 ForEachProvider(db => AreEqual(
|
|
|
169 from t in Types select t.DateTimeValue.DayOfYear,
|
|
|
170 from t in db.Types select Sql.AsSql(t.DateTimeValue.DayOfYear)));
|
|
|
171 }
|
|
|
172
|
|
|
173 [Test]
|
|
|
174 public void Day()
|
|
|
175 {
|
|
|
176 ForEachProvider(db => AreEqual(
|
|
|
177 from t in Types select t.DateTimeValue.Day,
|
|
|
178 from t in db.Types select Sql.AsSql(t.DateTimeValue.Day)));
|
|
|
179 }
|
|
|
180
|
|
|
181 [Test]
|
|
|
182 public void DayOfWeek()
|
|
|
183 {
|
|
|
184 ForEachProvider(db => AreEqual(
|
|
|
185 from t in Types select t.DateTimeValue.DayOfWeek,
|
|
|
186 from t in db.Types select Sql.AsSql(t.DateTimeValue.DayOfWeek)));
|
|
|
187 }
|
|
|
188
|
|
|
189 [Test]
|
|
|
190 public void Hour()
|
|
|
191 {
|
|
|
192 ForEachProvider(db => AreEqual(
|
|
|
193 from t in Types select t.DateTimeValue.Hour,
|
|
|
194 from t in db.Types select Sql.AsSql(t.DateTimeValue.Hour)));
|
|
|
195 }
|
|
|
196
|
|
|
197 [Test]
|
|
|
198 public void Minute()
|
|
|
199 {
|
|
|
200 ForEachProvider(db => AreEqual(
|
|
|
201 from t in Types select t.DateTimeValue.Minute,
|
|
|
202 from t in db.Types select Sql.AsSql(t.DateTimeValue.Minute)));
|
|
|
203 }
|
|
|
204
|
|
|
205 [Test]
|
|
|
206 public void Second()
|
|
|
207 {
|
|
|
208 ForEachProvider(db => AreEqual(
|
|
|
209 from t in Types select t.DateTimeValue.Second,
|
|
|
210 from t in db.Types select Sql.AsSql(t.DateTimeValue.Second)));
|
|
|
211 }
|
|
|
212
|
|
|
213 [Test]
|
|
|
214 public void Millisecond()
|
|
|
215 {
|
|
|
216 ForEachProvider(new[] { ProviderName.Informix, ProviderName.MySql, ProviderName.Access }, db => AreEqual(
|
|
|
217 from t in Types select t.DateTimeValue.Millisecond,
|
|
|
218 from t in db.Types select Sql.AsSql(t.DateTimeValue.Millisecond)));
|
|
|
219 }
|
|
|
220
|
|
|
221 [Test]
|
|
|
222 public void Date()
|
|
|
223 {
|
|
|
224 ForEachProvider(db => AreEqual(
|
|
|
225 from t in Types select Sql.AsSql(t.DateTimeValue.Date),
|
|
|
226 from t in db.Types select Sql.AsSql(t.DateTimeValue.Date)));
|
|
|
227 }
|
|
|
228
|
|
|
229 static TimeSpan TruncMiliseconds(TimeSpan ts)
|
|
|
230 {
|
|
|
231 return new TimeSpan(ts.Hours, ts.Minutes, ts.Seconds);
|
|
|
232 }
|
|
|
233
|
|
|
234 [Test]
|
|
|
235 public void TimeOfDay([DataContexts] string context)
|
|
|
236 {
|
|
|
237 using (var db = GetDataContext(context))
|
|
|
238 {
|
|
|
239 AreEqual(
|
|
|
240 from t in Types select TruncMiliseconds(Sql.AsSql(t.DateTimeValue.TimeOfDay)),
|
|
|
241 from t in db.Types select TruncMiliseconds(Sql.AsSql(t.DateTimeValue.TimeOfDay)));
|
|
|
242 }
|
|
|
243 }
|
|
|
244
|
|
|
245 #endregion
|
|
|
246
|
|
|
247 #region DateAdd
|
|
|
248
|
|
|
249 [Test]
|
|
|
250 public void DateAddYear()
|
|
|
251 {
|
|
|
252 ForEachProvider(db => AreEqual(
|
|
|
253 from t in Types select Sql.DateAdd(Sql.DateParts.Year, 1, t.DateTimeValue). Value.Date,
|
|
|
254 from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Year, 1, t.DateTimeValue)).Value.Date));
|
|
|
255 }
|
|
|
256
|
|
|
257 [Test]
|
|
|
258 public void DateAddQuarter()
|
|
|
259 {
|
|
|
260 ForEachProvider(db => AreEqual(
|
|
|
261 from t in Types select Sql.DateAdd(Sql.DateParts.Quarter, -1, t.DateTimeValue). Value.Date,
|
|
|
262 from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Quarter, -1, t.DateTimeValue)).Value.Date));
|
|
|
263 }
|
|
|
264
|
|
|
265 [Test]
|
|
|
266 public void DateAddMonth()
|
|
|
267 {
|
|
|
268 ForEachProvider(db => AreEqual(
|
|
|
269 from t in Types select Sql.DateAdd(Sql.DateParts.Month, 2, t.DateTimeValue). Value.Date,
|
|
|
270 from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Month, 2, t.DateTimeValue)).Value.Date));
|
|
|
271 }
|
|
|
272
|
|
|
273 [Test]
|
|
|
274 public void DateAddDayOfYear()
|
|
|
275 {
|
|
|
276 ForEachProvider(db => AreEqual(
|
|
|
277 from t in Types select Sql.DateAdd(Sql.DateParts.DayOfYear, 3, t.DateTimeValue). Value.Date,
|
|
|
278 from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.DayOfYear, 3, t.DateTimeValue)).Value.Date));
|
|
|
279 }
|
|
|
280
|
|
|
281 [Test]
|
|
|
282 public void DateAddDay()
|
|
|
283 {
|
|
|
284 ForEachProvider(db => AreEqual(
|
|
|
285 from t in Types select Sql.DateAdd(Sql.DateParts.Day, 5, t.DateTimeValue). Value.Date,
|
|
|
286 from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Day, 5, t.DateTimeValue)).Value.Date));
|
|
|
287 }
|
|
|
288
|
|
|
289 [Test]
|
|
|
290 public void DateAddWeek()
|
|
|
291 {
|
|
|
292 ForEachProvider(db => AreEqual(
|
|
|
293 from t in Types select Sql.DateAdd(Sql.DateParts.Week, -1, t.DateTimeValue). Value.Date,
|
|
|
294 from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Week, -1, t.DateTimeValue)).Value.Date));
|
|
|
295 }
|
|
|
296
|
|
|
297 [Test]
|
|
|
298 public void DateAddWeekDay()
|
|
|
299 {
|
|
|
300 ForEachProvider(db => AreEqual(
|
|
|
301 from t in Types select Sql.DateAdd(Sql.DateParts.WeekDay, 1, t.DateTimeValue). Value.Date,
|
|
|
302 from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.WeekDay, 1, t.DateTimeValue)).Value.Date));
|
|
|
303 }
|
|
|
304
|
|
|
305 [Test]
|
|
|
306 public void DateAddHour()
|
|
|
307 {
|
|
|
308 ForEachProvider(db => AreEqual(
|
|
|
309 from t in Types select Sql.DateAdd(Sql.DateParts.Hour, 1, t.DateTimeValue). Value.Hour,
|
|
|
310 from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Hour, 1, t.DateTimeValue)).Value.Hour));
|
|
|
311 }
|
|
|
312
|
|
|
313 [Test]
|
|
|
314 public void DateAddMinute()
|
|
|
315 {
|
|
|
316 ForEachProvider(db => AreEqual(
|
|
|
317 from t in Types select Sql.DateAdd(Sql.DateParts.Minute, 5, t.DateTimeValue). Value.Minute,
|
|
|
318 from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Minute, 5, t.DateTimeValue)).Value.Minute));
|
|
|
319 }
|
|
|
320
|
|
|
321 [Test]
|
|
|
322 public void DateAddSecond()
|
|
|
323 {
|
|
|
324 ForEachProvider(db => AreEqual(
|
|
|
325 from t in Types select Sql.DateAdd(Sql.DateParts.Second, 41, t.DateTimeValue). Value.Second,
|
|
|
326 from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Second, 41, t.DateTimeValue)).Value.Second));
|
|
|
327 }
|
|
|
328
|
|
|
329 [Test]
|
|
|
330 public void DateAddMillisecond()
|
|
|
331 {
|
|
|
332 ForEachProvider(new[] { ProviderName.Informix, ProviderName.MySql, ProviderName.Access },
|
|
|
333 db => (from t in db.Types select Sql.AsSql(Sql.DateAdd(Sql.DateParts.Millisecond, 41, t.DateTimeValue))).ToList());
|
|
|
334 }
|
|
|
335
|
|
|
336 [Test]
|
|
|
337 public void AddYears()
|
|
|
338 {
|
|
|
339 ForEachProvider(db => AreEqual(
|
|
|
340 from t in Types select t.DateTimeValue.AddYears(1). Date,
|
|
|
341 from t in db.Types select Sql.AsSql(t.DateTimeValue.AddYears(1)).Date));
|
|
|
342 }
|
|
|
343
|
|
|
344 [Test]
|
|
|
345 public void AddMonths()
|
|
|
346 {
|
|
|
347 ForEachProvider(db => AreEqual(
|
|
|
348 from t in Types select t.DateTimeValue.AddMonths(-2). Date,
|
|
|
349 from t in db.Types select Sql.AsSql(t.DateTimeValue.AddMonths(-2)).Date));
|
|
|
350 }
|
|
|
351
|
|
|
352 [Test]
|
|
|
353 public void AddDays()
|
|
|
354 {
|
|
|
355 ForEachProvider(db => AreEqual(
|
|
|
356 from t in Types select t.DateTimeValue.AddDays(5). Date,
|
|
|
357 from t in db.Types select Sql.AsSql(t.DateTimeValue.AddDays(5)).Date));
|
|
|
358 }
|
|
|
359
|
|
|
360 [Test]
|
|
|
361 public void AddHours()
|
|
|
362 {
|
|
|
363 ForEachProvider(db => AreEqual(
|
|
|
364 from t in Types select t.DateTimeValue.AddHours(22). Hour,
|
|
|
365 from t in db.Types select Sql.AsSql(t.DateTimeValue.AddHours(22)).Hour));
|
|
|
366 }
|
|
|
367
|
|
|
368 [Test]
|
|
|
369 public void AddMinutes()
|
|
|
370 {
|
|
|
371 ForEachProvider(db => AreEqual(
|
|
|
372 from t in Types select t.DateTimeValue.AddMinutes(-8). Minute,
|
|
|
373 from t in db.Types select Sql.AsSql(t.DateTimeValue.AddMinutes(-8)).Minute));
|
|
|
374 }
|
|
|
375
|
|
|
376 [Test]
|
|
|
377 public void AddSeconds()
|
|
|
378 {
|
|
|
379 ForEachProvider(db => AreEqual(
|
|
|
380 from t in Types select t.DateTimeValue.AddSeconds(-35). Second,
|
|
|
381 from t in db.Types select Sql.AsSql(t.DateTimeValue.AddSeconds(-35)).Second));
|
|
|
382 }
|
|
|
383
|
|
|
384 [Test]
|
|
|
385 public void AddMilliseconds()
|
|
|
386 {
|
|
|
387 ForEachProvider(new[] { ProviderName.Informix, ProviderName.MySql, ProviderName.Access },
|
|
|
388 db => (from t in db.Types select Sql.AsSql(t.DateTimeValue.AddMilliseconds(221))).ToList());
|
|
|
389 }
|
|
|
390
|
|
|
391 #endregion
|
|
|
392
|
|
|
393 #region DateDiff
|
|
|
394
|
|
|
395 [Test]
|
|
|
396 public void SubDateDay()
|
|
|
397 {
|
|
|
398 ForEachProvider(
|
|
|
399 new[] { ProviderName.Informix, "Oracle", "DevartOracle", ProviderName.PostgreSQL, ProviderName.MySql, ProviderName.SQLite, ProviderName.Access },
|
|
|
400 db => AreEqual(
|
|
|
401 from t in Types select (int)(t.DateTimeValue.AddHours(100) - t.DateTimeValue).TotalDays,
|
|
|
402 from t in db.Types select (int)Sql.AsSql((t.DateTimeValue.AddHours(100) - t.DateTimeValue).TotalDays)));
|
|
|
403 }
|
|
|
404
|
|
|
405 [Test]
|
|
|
406 public void DateDiffDay()
|
|
|
407 {
|
|
|
408 ForEachProvider(
|
|
|
409 new[] { ProviderName.Informix, "Oracle", "DevartOracle", ProviderName.PostgreSQL, ProviderName.MySql, ProviderName.SQLite, ProviderName.Access },
|
|
|
410 db => AreEqual(
|
|
|
411 from t in Types select Sql.DateDiff(Sql.DateParts.Day, t.DateTimeValue, t.DateTimeValue.AddHours(100)),
|
|
|
412 from t in db.Types select Sql.AsSql(Sql.DateDiff(Sql.DateParts.Day, t.DateTimeValue, t.DateTimeValue.AddHours(100)))));
|
|
|
413 }
|
|
|
414
|
|
|
415 [Test]
|
|
|
416 public void SubDateHour()
|
|
|
417 {
|
|
|
418 ForEachProvider(
|
|
|
419 new[] { ProviderName.Informix, "Oracle", "DevartOracle", ProviderName.PostgreSQL, ProviderName.MySql, ProviderName.SQLite, ProviderName.Access },
|
|
|
420 db => AreEqual(
|
|
|
421 from t in Types select (int)(t.DateTimeValue.AddHours(100) - t.DateTimeValue).TotalHours,
|
|
|
422 from t in db.Types select (int)Sql.AsSql((t.DateTimeValue.AddHours(100) - t.DateTimeValue).TotalHours)));
|
|
|
423 }
|
|
|
424
|
|
|
425 [Test]
|
|
|
426 public void DateDiffHour()
|
|
|
427 {
|
|
|
428 ForEachProvider(
|
|
|
429 new[] { ProviderName.Informix, "Oracle", "DevartOracle", ProviderName.PostgreSQL, ProviderName.MySql, ProviderName.SQLite, ProviderName.Access },
|
|
|
430 db => AreEqual(
|
|
|
431 from t in Types select Sql.DateDiff(Sql.DateParts.Hour, t.DateTimeValue, t.DateTimeValue.AddHours(100)),
|
|
|
432 from t in db.Types select Sql.AsSql(Sql.DateDiff(Sql.DateParts.Hour, t.DateTimeValue, t.DateTimeValue.AddHours(100)))));
|
|
|
433 }
|
|
|
434
|
|
|
435 [Test]
|
|
|
436 public void SubDateMinute()
|
|
|
437 {
|
|
|
438 ForEachProvider(
|
|
|
439 new[] { ProviderName.Informix, "Oracle", "DevartOracle", ProviderName.PostgreSQL, ProviderName.MySql, ProviderName.SQLite, ProviderName.Access },
|
|
|
440 db => AreEqual(
|
|
|
441 from t in Types select (int)(t.DateTimeValue.AddMinutes(100) - t.DateTimeValue).TotalMinutes,
|
|
|
442 from t in db.Types select (int)Sql.AsSql((t.DateTimeValue.AddMinutes(100) - t.DateTimeValue).TotalMinutes)));
|
|
|
443 }
|
|
|
444
|
|
|
445 [Test]
|
|
|
446 public void DateDiffMinute()
|
|
|
447 {
|
|
|
448 ForEachProvider(
|
|
|
449 new[] { ProviderName.Informix, "Oracle", "DevartOracle", ProviderName.PostgreSQL, ProviderName.MySql, ProviderName.SQLite, ProviderName.Access },
|
|
|
450 db => AreEqual(
|
|
|
451 from t in Types select Sql.DateDiff(Sql.DateParts.Minute, t.DateTimeValue, t.DateTimeValue.AddMinutes(100)),
|
|
|
452 from t in db.Types select Sql.AsSql(Sql.DateDiff(Sql.DateParts.Minute, t.DateTimeValue, t.DateTimeValue.AddMinutes(100)))));
|
|
|
453 }
|
|
|
454
|
|
|
455 [Test]
|
|
|
456 public void SubDateSecond()
|
|
|
457 {
|
|
|
458 ForEachProvider(
|
|
|
459 new[] { ProviderName.Informix, "Oracle", "DevartOracle", ProviderName.PostgreSQL, ProviderName.MySql, ProviderName.SQLite, ProviderName.Access },
|
|
|
460 db => AreEqual(
|
|
|
461 from t in Types select (int)(t.DateTimeValue.AddMinutes(100) - t.DateTimeValue).TotalSeconds,
|
|
|
462 from t in db.Types select (int)Sql.AsSql((t.DateTimeValue.AddMinutes(100) - t.DateTimeValue).TotalSeconds)));
|
|
|
463 }
|
|
|
464
|
|
|
465 [Test]
|
|
|
466 public void DateDiffSecond()
|
|
|
467 {
|
|
|
468 ForEachProvider(
|
|
|
469 new[] { ProviderName.Informix, "Oracle", "DevartOracle", ProviderName.PostgreSQL, ProviderName.MySql, ProviderName.SQLite, ProviderName.Access },
|
|
|
470 db => AreEqual(
|
|
|
471 from t in Types select Sql.DateDiff(Sql.DateParts.Second, t.DateTimeValue, t.DateTimeValue.AddMinutes(100)),
|
|
|
472 from t in db.Types select Sql.AsSql(Sql.DateDiff(Sql.DateParts.Second, t.DateTimeValue, t.DateTimeValue.AddMinutes(100)))));
|
|
|
473 }
|
|
|
474
|
|
|
475 [Test]
|
|
|
476 public void SubDateMillisecond()
|
|
|
477 {
|
|
|
478 ForEachProvider(
|
|
|
479 new[] { ProviderName.Informix, "Oracle", "DevartOracle", ProviderName.PostgreSQL, ProviderName.MySql, ProviderName.SQLite, ProviderName.Access },
|
|
|
480 db => AreEqual(
|
|
|
481 from t in Types select (int)(t.DateTimeValue.AddSeconds(1) - t.DateTimeValue).TotalMilliseconds,
|
|
|
482 from t in db.Types select (int)Sql.AsSql((t.DateTimeValue.AddSeconds(1) - t.DateTimeValue).TotalMilliseconds)));
|
|
|
483 }
|
|
|
484
|
|
|
485 [Test]
|
|
|
486 public void DateDiffMillisecond()
|
|
|
487 {
|
|
|
488 ForEachProvider(
|
|
|
489 new[] { ProviderName.Informix, "Oracle", "DevartOracle", ProviderName.PostgreSQL, ProviderName.MySql, ProviderName.SQLite, ProviderName.Access },
|
|
|
490 db => AreEqual(
|
|
|
491 from t in Types select Sql.DateDiff(Sql.DateParts.Millisecond, t.DateTimeValue, t.DateTimeValue.AddSeconds(1)),
|
|
|
492 from t in db.Types select Sql.AsSql(Sql.DateDiff(Sql.DateParts.Millisecond, t.DateTimeValue, t.DateTimeValue.AddSeconds(1)))));
|
|
|
493 }
|
|
|
494
|
|
|
495 #endregion
|
|
|
496
|
|
|
497 #region MakeDateTime
|
|
|
498
|
|
|
499 [Test]
|
|
|
500 public void MakeDateTime()
|
|
|
501 {
|
|
|
502 ForEachProvider(db => AreEqual(
|
|
|
503 from t in from p in Types select Sql.MakeDateTime(2010, p.ID, 1) where t.Value.Year == 2010 select t,
|
|
|
504 from t in from p in db.Types select Sql.MakeDateTime(2010, p.ID, 1) where t.Value.Year == 2010 select t));
|
|
|
505 }
|
|
|
506
|
|
|
507 [Test]
|
|
|
508 public void NewDateTime1()
|
|
|
509 {
|
|
|
510 ForEachProvider(db => AreEqual(
|
|
|
511 from t in from p in Types select new DateTime(p.DateTimeValue.Year, 10, 1) where t.Month == 10 select t,
|
|
|
512 from t in from p in db.Types select new DateTime(p.DateTimeValue.Year, 10, 1) where t.Month == 10 select t));
|
|
|
513 }
|
|
|
514
|
|
|
515 [Test]
|
|
|
516 public void NewDateTime2()
|
|
|
517 {
|
|
|
518 ForEachProvider(db => AreEqual(
|
|
|
519 from p in Types select new DateTime(p.DateTimeValue.Year, 10, 1),
|
|
|
520 from p in db.Types select new DateTime(p.DateTimeValue.Year, 10, 1)));
|
|
|
521 }
|
|
|
522
|
|
|
523 [Test]
|
|
|
524 public void MakeDateTime2()
|
|
|
525 {
|
|
|
526 ForEachProvider(db => AreEqual(
|
|
|
527 from t in from p in Types select Sql.MakeDateTime(2010, p.ID, 1, 20, 35, 44) where t.Value.Year == 2010 select t,
|
|
|
528 from t in from p in db.Types select Sql.MakeDateTime(2010, p.ID, 1, 20, 35, 44) where t.Value.Year == 2010 select t));
|
|
|
529 }
|
|
|
530
|
|
|
531 [Test]
|
|
|
532 public void NewDateTime3()
|
|
|
533 {
|
|
|
534 ForEachProvider(db => AreEqual(
|
|
|
535 from t in from p in Types select new DateTime(p.DateTimeValue.Year, 10, 1, 20, 35, 44) where t.Month == 10 select t,
|
|
|
536 from t in from p in db.Types select new DateTime(p.DateTimeValue.Year, 10, 1, 20, 35, 44) where t.Month == 10 select t));
|
|
|
537 }
|
|
|
538
|
|
|
539 [Test]
|
|
|
540 public void NewDateTime4()
|
|
|
541 {
|
|
|
542 ForEachProvider(db => AreEqual(
|
|
|
543 from p in Types select new DateTime(p.DateTimeValue.Year, 10, 1, 20, 35, 44),
|
|
|
544 from p in db.Types select new DateTime(p.DateTimeValue.Year, 10, 1, 20, 35, 44)));
|
|
|
545 }
|
|
|
546
|
|
|
547 [Test]
|
|
|
548 public void NewDateTime5()
|
|
|
549 {
|
|
|
550 ForEachProvider(db => AreEqual(
|
|
|
551 from t in from p in Types select new DateTime(p.DateTimeValue.Year + 1, 10, 1) where t.Month == 10 select t,
|
|
|
552 from t in from p in db.Types select new DateTime(p.DateTimeValue.Year + 1, 10, 1) where t.Month == 10 select t));
|
|
|
553 }
|
|
|
554
|
|
|
555 #endregion
|
|
|
556
|
|
|
557 [Test]
|
|
|
558 public void GetDateTest1()
|
|
|
559 {
|
|
|
560 ForEachProvider(
|
|
|
561 new[] { ProviderName.PostgreSQL },
|
|
|
562 db =>
|
|
|
563 {
|
|
|
564 var dates =
|
|
|
565 from v in db.Parent
|
|
|
566 join s in db.Child on v.ParentID equals s.ParentID
|
|
|
567 where v.Value1 > 0
|
|
|
568 select Sql.GetDate().Date;
|
|
|
569
|
|
|
570 var countByDates =
|
|
|
571 from v in dates
|
|
|
572 group v by v into g
|
|
|
573 select new { g.Key, Count = g.Count() };
|
|
|
574
|
|
|
575 countByDates.Take(5).ToList();
|
|
|
576 });
|
|
|
577 }
|
|
|
578
|
|
|
579 [Test]
|
|
|
580 public void GetDateTest2()
|
|
|
581 {
|
|
|
582 ForEachProvider(
|
|
|
583 //new[] { ProviderName.PostgreSQL },
|
|
|
584 db =>
|
|
|
585 {
|
|
|
586 var dates =
|
|
|
587 from v in db.Parent
|
|
|
588 join s in db.Child on v.ParentID equals s.ParentID
|
|
|
589 where v.Value1 > 0
|
|
|
590 select Sql.CurrentTimestamp.Date;
|
|
|
591
|
|
|
592 var countByDates =
|
|
|
593 from v in dates
|
|
|
594 group v by v into g
|
|
|
595 select new { g.Key, Count = g.Count() };
|
|
|
596
|
|
|
597 countByDates.Take(5).ToList();
|
|
|
598 });
|
|
|
599 }
|
|
|
600 }
|
|
|
601 }
|
