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

By: a guest on Jan 16th, 2013  |  syntax: None  |  size: 1.47 KB  |  views: 62  |  expires: Never
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. ,convertdates as
28. (
29.    select n, dt = dateadd(day,d-1,dateadd(month,m-1,dateadd(year,y-1900,0)))
30.    from pivotdates
31. )
32. ,datediffs as
33. (
34.    select n, dt, midx = datediff(day,'2012-12-21',dt)+144000*13
35.    from convertdates
36. )
37. ,convertmayan as
38. (
39.    select n,
40.           baktun = cast(midx/20/20/18/20      as varchar(9)),
41.           katun  = cast(midx/20/18/20    % 20 as varchar(9)),
42.           tun    = cast(midx/20/18       % 20 as varchar(9)),
43.           winal  = cast(midx/20          % 18 as varchar(9)),
44.           kin    = cast(midx             % 20 as varchar(9))
45.    from datediffs
46. )
47. select baktun+'.'+katun+'.'+tun+'.'+winal+'.'+kin
48. from convertmayan
49. order by n desc
clone this paste RAW Paste Data
Top