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