Check out the Pastebin Gadgets Shop. We have thousands of fun, geeky & affordable gadgets on sale :-)Want more features on Pastebin? Sign Up, it's FREE!
tweet

# aredna mayan date conversion bonus 1

By: a guest on Jan 17th, 2013  |  syntax: None  |  size: 2.03 KB  |  views: 28  |  expires: Never
This paste has a previous version, view the difference. Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
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. ,leapyears as
28. (
29.     select 1972 as yr
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)
clone this paste RAW Paste Data
Top