Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement