Date Formatting Functions

wiki

Ideal for formatting dates in reports, or converting dates between different formats when importing data.

..from Sam Lie

--=====================================================
-- the reason this routine is needed is because lua does not 
-- have a sort indexed table function
function table_sort(a, sortfield)
local new1 = {}
local new2 = {}
    for k,v in pairs(a) do
        table.insert(new1, { key=k, val=v } )       
    end
    table.sort(new1, function (a,b) return (a.val[sortfield] < b.val[sortfield]) end)  
    for k,v in pairs(new1) do
        table.insert(new2, v.val)
    end
    return new2
end

---============================================================
function padzero(s, count)
    return string.rep("0", count-string.len(s)) .. s 
end

--========================================================================
-- get date parts for a given ISO 8601 date format (http://richard.warburton.it )
function get_date_parts(date_str)
  _,_,y,m,d=string.find(date_str, "(%d+)-(%d+)-(%d+)")
  return tonumber(y),tonumber(m),tonumber(d)
end

--====================================================
function getmonth(month)
    local months = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec" }
    return months[tonumber(month)]
end

--====================================================
function getday_posfix(day)
local idd = math.mod(day,10)
       return   (idd==1 and day~=11 and "st")  or (idd==2 and day~=12 and "nd") or (idd==3 and day~=13 and "rd") or "th"
end


--========================================================================
--  Given a date of certain date_format, returns the date parts yy,mm,dd
--  eg. "12 May 2007" with date_format "dd mmm yyyy"
--   or   "12.05.07" with date_format "dd.mm.yy"
--  will return 2007, 5, 12
--
function get_formatted_date_parts(date_str, date_format)
    local d,m,y, arr, x, yy, mm, dd, use_month_names
    local months = { jan=1, feb=2, mar=3, apr=4, may=5, jun=6, jul=7, aug=8, sep=9, oct=10, nov=11, dec=12 }

    if (date_format) then
    
        if string.find(date_format, "mmm") then
            use_month_names = true
        else
            use_month_names = false
        end

        d = string.find(date_format, "dd")
        m = string.find(date_format, "mm")
        y = string.find(date_format, "yy")
        
        arr = { { pos=y, b="yy" }, { pos=m, b="mm" } , { pos=d, b="dd" }  }
        arr = table_sort(arr, "pos")

        date_format = string.gsub(date_format,"yyyy","(%%d+)")
        date_format = string.gsub(date_format,"mmm","(%%a+)")
        date_format = string.gsub(date_format,"yy","(%%d+)")
        date_format = string.gsub(date_format,"mm","(%%d+)")
        date_format = string.gsub(date_format,"dd","(%%d+)")
        date_format = string.gsub(date_format," ","%%s")
    else
        date_format = "(%d+)-(%d+)-(%d+)"
        arr = { { pos=1, b="yy" }, { pos=2, b="mm" } , { pos=3, b="dd" }  }
    end

    if (date_str and date_str~="") then     
        _, _, arr[1].c, arr[2].c, arr[3].c = string.find(string.lower(date_str), date_format)
    else
        return nil, nil, nil
    end


    arr = table_sort(arr, "b")
    yy = arr[3].c
    mm = arr[2].c
    dd = arr[1].c

    if (use_month_names) then       
    
        mm = months[lower(string.sub(mm,1,3))]
        if (not mm) then
            error("Invalid month name.")
        end
    end

    -- for naughty people who still use two digit years.

    if (string.len(yy)==2) then
        if (tonumber(yy)>40) then
            yy = "19"..yy
        else
            yy = "20"..yy
        end
    end

    return tonumber(yy),tonumber(mm),tonumber(dd)
end



--========================================================================
-- Note : date_str has to be  ISO 8601 date format  ie. yyyy-mm-dd
--
function format_date(date_str, dateformat)
local iyy, imm, idd 

    if (date_str and date_str~="") then
        iyy, imm, idd =  get_date_parts(date_str)
        dateformat = string.gsub(dateformat, "DDD",  idd..string.upper(getday_posfix(idd)))
        dateformat = string.gsub(dateformat, "ddd",  idd..getday_posfix(idd) )
        dateformat = string.gsub(dateformat, "dd", padzero(idd,2))
        dateformat = string.gsub(dateformat, "MMM", string.upper(getmonth(imm)))
        dateformat = string.gsub(dateformat, "mmm", getmonth(imm))
        dateformat = string.gsub(dateformat, "mm", padzero(imm,2))
        dateformat = string.gsub(dateformat, "yyyy", padzero(iyy,4))
        dateformat = string.gsub(dateformat, "yy", string.sub(padzero(iyy,4),3,4))
    else
        dateformat = ""
    end

    return(dateformat)
end

--===============================================
-- convert date to excel serial day number
--
function date_to_excel_date(dd, mm, yy) 
local days, monthdays, leapyears, nonleapyears, nonnonleapyears

    monthdays= { 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31 }

    leapyears=tonumber(math.floor((yy-1900)/4));
    nonleapyears=tonumber(math.floor((yy-1900)/100))
    nonnonleapyears=tonumber(math.floor((yy-1600)/400))

    if ((math.mod(yy,4)==0) and mm<3) then
      leapyears = leapyears - 1
    end

    days= 365 * (yy-1900) + leapyears - nonleapyears + nonnonleapyears

    c=1
    while (c<mm) do
      days = days + monthdays[c]
    c=c+1
    end

    days=days+dd+1

    return days
end
	

Here is a test suite.

adate = os.date()
print("Todays date according to your OS format :", adate)

-- for example "mm/dd/yyyy" could be your OS locale date format.
yy,mm,dd =  get_formatted_date_parts(adate, "mm/dd/yyyy")

ISO_date = yy .. "-" .. mm .. "-" .. dd

print("Date formated in ISO 8601", ISO_date)

print(format_date(ISO_date, "dd MMM yyyy") )
print(format_date(ISO_date, "ddd mmm yyyy") )
print(format_date(ISO_date, "dd/mm/yy") )
print(format_date(ISO_date, "yyyy.mm.dd") )
print(format_date(ISO_date, "Today is the ddd of mmm, yyyy.") )

print( date_to_excel_date(22, 2, 2009)  )   -- outputs 39866