Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

aredna mayan date conversion bonus 1

By: a guest on Jan 17th, 2013  |  syntax: None  |  size: 2.03 KB  |  hits: 26  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
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)
create a new version of this paste RAW Paste Data