Pastebin launched a little side project called VERYVIRAL.com, check it out ;-) Want more features on Pastebin? Sign Up, it's FREE!
Guest

aredna mayan date conversion

By: a guest on Jan 16th, 2013  |  syntax: None  |  size: 1.47 KB  |  views: 61  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
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