SHOW:
|
|
- or go back to the newest paste.
| 1 | declare @i varchar(999) = '3 1 1 1970 20 7 1988 12 12 2012'; | |
| 2 | ||
| 3 | ;with parseinput as | |
| 4 | ( | |
| 5 | select n = cast(substring(@i,1,charindex(' ',@i)-1) as int)*3,
| |
| 6 | i = cast(substring(@i,charindex(' ',@i)+1,999)+' ' as varchar(100)),
| |
| 7 | x = 0, | |
| 8 | t = 'x' | |
| 9 | union all | |
| 10 | select n = n-1, | |
| 11 | i = cast(substring(i,charindex(' ',i)+1,999) as varchar(100)),
| |
| 12 | x = cast(substring(i,1,charindex(' ',i)-1) as int),
| |
| 13 | t = case n%3 when 0 then 'd' when 2 then 'm' else 'y' end | |
| 14 | from parseinput | |
| 15 | where n > 0 | |
| 16 | ) | |
| 17 | ,pivotdates as | |
| 18 | ( | |
| 19 | select n = n/3, | |
| 20 | d = max(case when t = 'd' then x else 0 end), | |
| 21 | m = max(case when t = 'm' then x else 0 end), | |
| 22 | y = max(case when t = 'y' then x else 0 end) | |
| 23 | from parseinput | |
| 24 | where t <> 'x' | |
| 25 | group by n/3 | |
| 26 | ) | |
| 27 | - | ,convertdates as |
| 27 | + | |
| 28 | ( | |
| 29 | - | select n, d, m, y |
| 29 | + | |
| 30 | union all | |
| 31 | select yr+case when (yr+4) % 100 = 0 and (yr+4) % 400 <> 0 then 8 else 4 end | |
| 32 | from leapyears | |
| 33 | where yr < 100000 | |
| 34 | ) | |
| 35 | ,datediffs as | |
| 36 | ( | |
| 37 | select n, | |
| 38 | midx = 1856304 | |
| 39 | + (y-1970)*365 | |
| 40 | + case m when 1 then 0 when 5 then 120 when 9 then 243 | |
| 41 | when 2 then 31 when 6 then 151 when 10 then 273 | |
| 42 | when 3 then 59 when 7 then 181 when 11 then 304 | |
| 43 | when 4 then 90 when 8 then 212 when 12 then 334 | |
| 44 | end | |
| 45 | + (d) | |
| 46 | + ( select count(*) from leapyears where yr<y or (yr=y and m>2) ) | |
| 47 | from pivotdates | |
| 48 | ) | |
| 49 | ,convertmayan as | |
| 50 | ( | |
| 51 | select n, | |
| 52 | baktun = cast(midx/20/20/18/20 as varchar(9)), | |
| 53 | katun = cast(midx/20/18/20 % 20 as varchar(9)), | |
| 54 | tun = cast(midx/20/18 % 20 as varchar(9)), | |
| 55 | winal = cast(midx/20 % 18 as varchar(9)), | |
| 56 | kin = cast(midx % 20 as varchar(9)) | |
| 57 | from datediffs | |
| 58 | ) | |
| 59 | select baktun+'.'+katun+'.'+tun+'.'+winal+'.'+kin | |
| 60 | from convertmayan | |
| 61 | order by n desc | |
| 62 | option (maxrecursion 32000) |
