
aredna mayan date conversion
By: a guest on
Jan 16th, 2013 | syntax:
None | size: 1.47 KB | hits: 59 | expires: Never
declare @i varchar(999) = '3 1 1 1970 20 7 1988 12 12 2012';
;with parseinput as
(
select n = cast(substring(@i,1,charindex(' ',@i)-1) as int)*3,
i = cast(substring(@i,charindex(' ',@i)+1,999)+' ' as varchar(100)),
x = 0,
t = 'x'
union all
select n = n-1,
i = cast(substring(i,charindex(' ',i)+1,999) as varchar(100)),
x = cast(substring(i,1,charindex(' ',i)-1) as int),
t = case n%3 when 0 then 'd' when 2 then 'm' else 'y' end
from parseinput
where n > 0
)
,pivotdates as
(
select n = n/3,
d = max(case when t = 'd' then x else 0 end),
m = max(case when t = 'm' then x else 0 end),
y = max(case when t = 'y' then x else 0 end)
from parseinput
where t <> 'x'
group by n/3
)
,convertdates as
(
select n, dt = dateadd(day,d-1,dateadd(month,m-1,dateadd(year,y-1900,0)))
from pivotdates
)
,datediffs as
(
select n, dt, midx = datediff(day,'2012-12-21',dt)+144000*13
from convertdates
)
,convertmayan as
(
select n,
baktun = cast(midx/20/20/18/20 as varchar(9)),
katun = cast(midx/20/18/20 % 20 as varchar(9)),
tun = cast(midx/20/18 % 20 as varchar(9)),
winal = cast(midx/20 % 18 as varchar(9)),
kin = cast(midx % 20 as varchar(9))
from datediffs
)
select baktun+'.'+katun+'.'+tun+'.'+winal+'.'+kin
from convertmayan
order by n desc