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 a

		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 new1

		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 = lua_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)

			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)

			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=to_int((yy-1900)/4);

    nonleapyears=to_int((yy-1900)/100)

    nonnonleapyears=to_int((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