· 8 years ago · Jan 08, 2018, 08:54 AM
1select ... as days where `date` is between '2010-01-20' and '2010-01-24'
2
3insert into table select ... as days date between '' and ''
4
5select a.Date
6from (
7 select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
8 from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
9 cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
10 cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
11) a
12where a.Date between '2010-01-20' and '2010-01-24'
13
14Date
15----------
162010-01-24
172010-01-23
182010-01-22
192010-01-21
202010-01-20
21
22CREATE VIEW digits AS
23 SELECT 0 AS digit UNION ALL
24 SELECT 1 UNION ALL
25 SELECT 2 UNION ALL
26 SELECT 3 UNION ALL
27 SELECT 4 UNION ALL
28 SELECT 5 UNION ALL
29 SELECT 6 UNION ALL
30 SELECT 7 UNION ALL
31 SELECT 8 UNION ALL
32 SELECT 9;
33
34CREATE VIEW numbers AS
35 SELECT
36 ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number
37 FROM
38 digits as ones,
39 digits as tens,
40 digits as hundreds,
41 digits as thousands;
42
43CREATE VIEW dates AS
44 SELECT
45 SUBDATE(CURRENT_DATE(), number) AS date
46 FROM
47 numbers;
48
49SELECT
50 date
51FROM
52 dates
53WHERE
54 date BETWEEN '2010-01-20' AND '2010-01-24'
55ORDER BY
56 date
57
58CREATE VIEW dates AS
59 SELECT
60 SUBDATE(CURRENT_DATE(), number) AS date
61 FROM
62 numbers
63 UNION ALL
64 SELECT
65 ADDDATE(CURRENT_DATE(), number + 1) AS date
66 FROM
67 numbers;
68
69SELECT day::date
70FROM generate_series('2010-01-20', '2010-01-24', INTERVAL '1 day') day;
71
72 day
73------------
74 2010-01-20
75 2010-01-21
76 2010-01-22
77 2010-01-23
78 2010-01-24
79(5 rows)
80
81with [dates] as (
82 select convert(datetime, '1753-01-01') as [date] --start
83 union all
84 select dateadd(day, 1, [date])
85 from [dates]
86 where [date] < '9999-12-31' --end
87)
88select [date]
89from [dates]
90where [date] between '2013-01-01' and '2013-12-31'
91option (maxrecursion 0)
92
93select datetable.Date
94from (
95 select DATEADD(day,-(a.a + (10 * b.a) + (100 * c.a)),getdate()) AS Date
96 from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
97 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
98
99 cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
100 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
101
102 cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4
103 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
104) datetable
105where datetable.Date between '2014-01-20' and '2014-01-24'
106order by datetable.Date DESC
107
108Date
109-----
1102014-01-23 12:35:25.250
1112014-01-22 12:35:25.250
1122014-01-21 12:35:25.250
1132014-01-20 12:35:25.250
114
115CREATE TABLE `example`.`numbers` (
116 `id` int(10) unsigned NOT NULL auto_increment,
117 PRIMARY KEY (`id`)
118) ENGINE=InnoDB DEFAULT CHARSET=latin1;
119
120INSERT INTO NUMBERS (id) VALUES (NULL);
121
122SELECT x.start_date + INTERVAL n.id-1 DAY
123 FROM NUMBERS n
124 JOIN (SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d') AS start_date
125 FROM DUAL) x
126 WHERE x.start_date + INTERVAL n.id-1 DAY <= '2010-01-24'
127
128SELECT STR_TO_DATE('2010-01-20', '%Y-%m-%d')
129 FROM DUAL
130UNION ALL
131SELECT STR_TO_DATE('2010-01-21', '%Y-%m-%d')
132 FROM DUAL
133UNION ALL
134SELECT STR_TO_DATE('2010-01-22', '%Y-%m-%d')
135 FROM DUAL
136UNION ALL
137SELECT STR_TO_DATE('2010-01-23', '%Y-%m-%d')
138 FROM DUAL
139UNION ALL
140SELECT STR_TO_DATE('2010-01-24', '%Y-%m-%d')
141 FROM DUAL
142
143SELECT 0 AS a
144FROM dual
145UNION ALL
146SELECT 1
147FROM dual
148UNION ALL
149SELECT 2
150FROM dual
151UNION ALL
152SELECT 3
153FROM dual
154UNION ALL
155SELECT 4
156FROM dual
157UNION ALL
158SELECT 5
159FROM dual
160UNION ALL
161SELECT 6
162FROM dual
163UNION ALL
164SELECT 7
165FROM dual
166UNION ALL
167SELECT 8
168FROM dual
169UNION ALL
170SELECT 9
171FROM dual;
172
173SELECT date() - (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
174FROM
175 (SELECT *
176 FROM ZeroThru9Q) AS a,
177
178 (SELECT *
179 FROM ZeroThru9Q) AS b,
180
181 (SELECT *
182 FROM ZeroThru9Q) AS c
183
184SELECT date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
185FROM
186 (SELECT *
187 FROM ZeroThru9Q) AS a,
188
189 (SELECT *
190 FROM ZeroThru9Q) AS b,
191
192 (SELECT *
193 FROM ZeroThru9Q) AS c;
194
195SELECT MyDate
196FROM TodayMinus1KQ
197UNION
198SELECT MyDate
199FROM TodayPlus1KQ;
200
201SELECT MyDate
202FROM TodayPlusMinus1KQ
203WHERE MyDate BETWEEN #05/01/2014# and #05/30/2014#
204
205SELECT date_value
206FROM (SELECT a.espr1+(10*b.espr1)+(100*c.espr1) AS integer_value,
207dateadd("d",integer_value,dateserial([start_year], [start_month], [start_day])) as date_value
208FROM (select * from
209 (
210 select top 1 "0" as espr1 from MSysObjects
211 union all
212 select top 1 "1" as espr2 from MSysObjects
213 union all
214 select top 1 "2" as espr3 from MSysObjects
215 union all
216 select top 1 "3" as espr4 from MSysObjects
217 union all
218 select top 1 "4" as espr5 from MSysObjects
219 union all
220 select top 1 "5" as espr6 from MSysObjects
221 union all
222 select top 1 "6" as espr7 from MSysObjects
223 union all
224 select top 1 "7" as espr8 from MSysObjects
225 union all
226 select top 1 "8" as espr9 from MSysObjects
227 union all
228 select top 1 "9" as espr9 from MSysObjects
229 ) as a,
230 (
231 select top 1 "0" as espr1 from MSysObjects
232 union all
233 select top 1 "1" as espr2 from MSysObjects
234 union all
235 select top 1 "2" as espr3 from MSysObjects
236 union all
237 select top 1 "3" as espr4 from MSysObjects
238 union all
239 select top 1 "4" as espr5 from MSysObjects
240 union all
241 select top 1 "5" as espr6 from MSysObjects
242 union all
243 select top 1 "6" as espr7 from MSysObjects
244 union all
245 select top 1 "7" as espr8 from MSysObjects
246 union all
247 select top 1 "8" as espr9 from MSysObjects
248 union all
249 select top 1 "9" as espr9 from MSysObjects
250 ) as b,
251 (
252 select top 1 "0" as espr1 from MSysObjects
253 union all
254 select top 1 "1" as espr2 from MSysObjects
255 union all
256 select top 1 "2" as espr3 from MSysObjects
257 union all
258 select top 1 "3" as espr4 from MSysObjects
259 union all
260 select top 1 "4" as espr5 from MSysObjects
261 union all
262 select top 1 "5" as espr6 from MSysObjects
263 union all
264 select top 1 "6" as espr7 from MSysObjects
265 union all
266 select top 1 "7" as espr8 from MSysObjects
267 union all
268 select top 1 "8" as espr9 from MSysObjects
269 union all
270 select top 1 "9" as espr9 from MSysObjects
271 ) as c
272) as d)
273WHERE date_value
274between dateserial([start_year], [start_month], [start_day])
275and dateserial([end_year], [end_month], [end_day]);
276
277SELECT TO_DATE('20160210','yyyymmdd') - 1 + LEVEL AS start_day
278from DUAL
279connect by level <= (TO_DATE('20160228','yyyymmdd') + 1) - TO_DATE('20160210','yyyymmdd') ;
280
281select from days.day, count(mytable.field) as fields from days left join mytable on day=date where date between x and y;
282
283DELIMITER $$
284
285CREATE DEFINER=`root`@`localhost` PROCEDURE `days`(IN dateStart DATE, IN dateEnd DATE)
286BEGIN
287
288 CREATE TEMPORARY TABLE IF NOT EXISTS date_range (day DATE);
289
290 WHILE dateStart <= dateEnd DO
291 INSERT INTO date_range VALUES (dateStart);
292 SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
293 END WHILE;
294
295 SELECT * FROM date_range;
296 DROP TEMPORARY TABLE IF EXISTS date_range;
297
298END
299
300SELECT DATEADD(d, N, '0001-01-22')
301FROM Numbers -- A table containing the numbers 0 through N
302WHERE N <= 5;
303
304DECLARE @date1 DATE = '9001-11-21';
305DECLARE @date2 DATE = '9001-11-23';
306
307SELECT D = DATEADD(d, N, @date1)
308FROM (
309 SELECT N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
310 FROM (SELECT 'A' AS S UNION ALL SELECT 'A' UNION ALL SELECT 'A') S
311) Numbers
312WHERE N <= ABS(DATEDIFF(d, @date1, @date2));
313
314CREATE FUNCTION dbo.GenerateRangeDate (
315 @date1 DATE,
316 @date2 DATE
317)
318RETURNS TABLE
319WITH SCHEMABINDING
320AS
321RETURN (
322 SELECT D = DATEADD(d, N + 32768, CASE WHEN @date1 <= @date2 THEN @date1 ELSE @date2 END)
323 FROM dbo.GenerateRangeSmallInt(-32768, ABS(DATEDIFF(d, @date1, @date2)) - 32768)
324);
325
326GO
327
328CREATE FUNCTION dbo.GenerateRangeSmallInt (
329 @num1 SMALLINT = -32768
330 , @num2 SMALLINT = 32767
331)
332RETURNS TABLE
333WITH SCHEMABINDING
334AS
335RETURN (
336 WITH Numbers(N) AS (
337 SELECT N FROM(VALUES
338 (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
339 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
340 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
341 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
342 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
343 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
344 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
345 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
346 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
347 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
348 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
349 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
350 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
351 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
352 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
353 , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
354 ) V (N)
355 )
356 SELECT TOP(ABS(CAST(@num1 AS INT) - CAST(@num2 AS INT)) + 1)
357 N = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) + CASE WHEN @num1 <= @num2 THEN @num1 ELSE @num2 END - 1
358 FROM Numbers A
359 , Numbers B
360);
361
362(SELECT TRIM('2016-01-05' + INTERVAL a + b DAY) date
363FROM
364(SELECT 0 a UNION SELECT 1 a UNION SELECT 2 UNION SELECT 3
365UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
366UNION SELECT 8 UNION SELECT 9 ) d,
367(SELECT 0 b UNION SELECT 10 UNION SELECT 20
368UNION SELECT 30 UNION SELECT 40) m
369WHERE '2016-01-05' + INTERVAL a + b DAY <= '2016-01-21')
370
371select trunc(sysdate-dayincrement, 'DD')
372 from dual, (select level as dayincrement
373 from dual connect by level <= 30)
374
375create table #dates ([date] smalldatetime)
376while @since < @to
377begin
378 insert into #dates(dateadd(day,1,@since))
379 set @since = dateadd(day,1,@since)
380end
381select [date] from #dates
382
383STARTDATE ENDDATE
38410/24/2012 10/24/2012
38510/27/2012 10/29/2012
38610/30/2012 10/30/2012
387
388STARTDATE
38910/24/2012
39010/27/2012
39110/28/2012
39210/29/2012
39310/30/2012
394
395WITH CTE AS
396 (SELECT DISTINCT convert(varchar(10),StartTime, 101) AS StartTime,
397 datediff(dd,StartTime, endTime) AS diff
398 FROM dbo.testdate
399 UNION ALL SELECT StartTime,
400 diff - 1 AS diff
401 FROM CTE
402 WHERE diff<> 0)
403SELECT DISTINCT DateAdd(dd,diff, StartTime) AS StartTime
404FROM CTE
405
406STARTDATE DIFF
40710/24/2012 0
40810/27/2012 0
40910/27/2012 1
41010/27/2012 2
41110/30/2012 0
412
413STARTDATE Specification
41410/24/2012 --> From Record 1
41510/27/2012 --> From Record 2
41610/27/2012 --> From Record 2
41710/27/2012 --> From Record 2
41810/30/2012 --> From Record 3
419
420STARTDATE
42110/24/2012
42210/27/2012
42310/28/2012
42410/29/2012
42510/30/2012
426
427set language 'SPANISH'
428DECLARE @table table(fechaDesde datetime , fechaHasta datetime )
429INSERT @table VALUES('20151231' , '20161231');
430WITH x AS
431 (
432 SELECT DATEADD( m , 1 ,fechaDesde ) as fecha FROM @table
433 UNION ALL
434 SELECT DATEADD( m , 1 ,fecha )
435 FROM @table t INNER JOIN x ON DATEADD( m , 1 ,x.fecha ) <= t.fechaHasta
436 )
437SELECT LEFT( CONVERT( VARCHAR, fecha , 112 ) , 6 ) as Periodo_Id
438,DATEPART ( dd, DATEADD(dd,-(DAY(fecha)-1),fecha)) Num_Dia_Inicio
439,DATEADD(dd,-(DAY(fecha)-1),fecha) Fecha_Inicio
440,DATEPART ( mm , fecha ) Mes_Id
441,DATEPART ( yy , fecha ) Anio
442,DATEPART ( dd, DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha))) Num_Dia_Fin
443,DATEADD(dd,-(DAY(DATEADD(mm,1,fecha))),DATEADD(mm,1,fecha)) ultimoDia
444,datename(MONTH, fecha) mes
445,'Q' + convert(varchar(10), DATEPART(QUARTER, fecha)) Trimestre_Name
446FROM x
447OPTION(MAXRECURSION 0)
448
449DELIMITER $$
450CREATE PROCEDURE GenerateRangeDates(IN dateStart DATE, IN dateEnd DATE)
451BEGIN
452
453 CREATE TEMPORARY TABLE IF NOT EXISTS dates (day DATE);
454
455 loopDate: LOOP
456 INSERT INTO dates(day) VALUES (dateStart);
457 SET dateStart = DATE_ADD(dateStart, INTERVAL 1 DAY);
458
459 IF dateStart <= dateEnd
460 THEN ITERATE loopDate;
461 ELSE LEAVE loopDate;
462 END IF;
463 END LOOP loopDate;
464
465 SELECT day FROM dates;
466 DROP TEMPORARY TABLE IF EXISTS dates;
467
468END
469$$
470
471-- Call procedure
472call GenerateRangeDates(
473 now() - INTERVAL 40 DAY,
474 now()
475 );
476
477select d.Date
478from (
479 select
480 date(julianday('2010-01-20') + (a.a + (10 * b.a) + (100 * c.a))) as Date
481 from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
482 cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
483 cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
484) d
485where
486d.Date between '2010-01-20' and '2010-01-24'
487order by d.Date
488
489with [dates] as (
490 select convert(datetime, '2016-01-01') as [date] --start
491 union all
492 select dateadd(day, 1, [date])
493 from [dates]
494 where [date] < '2018-01-01' --end
495)
496select [date]
497, DATEPART (dw,[date]) as Wochentag
498, (select holidayname from holidaytable
499where holidaytable.hdate = [date])
500as Feiertag
501from [dates]
502where [date] between '2016-01-01' and '2016-31-12'
503option (maxrecursion 0)
504
505create view zero_to_nine as
506 select 0 as n union all
507 select 1 union all
508 select 2 union all
509 select 3 union all
510 select 4 union all
511 select 5 union all
512 select 6 union all
513 select 7 union all
514 select 8 union all
515 select 9;
516
517create view date_range as
518 select curdate() - INTERVAL (a.n + (10 * b.n) + (100 * c.n)) DAY as date
519 from zero_to_nine as a
520 cross join zero_to_nine as b
521 cross join zero_to_nine as c;
522
523select * from date_range
524
525date
526---
5272017-06-06
5282017-06-05
5292017-06-04
5302017-06-03
5312017-06-02
532...