The only difference i found here is the date function accepts the expressions having values in numeric form like i.e. 35648 whereas date# is only use to evaluate the date data expression.
date() takes a number that can be expressed as a date and expresses it in the default format unless otherwise specified.
e.g. Date(40379) may return a 20/07/2010 and date(40379,'MM-DD-YYYY') would return 07-20-2010
date#() will take in a date in an odd format and convert it to a date format. e.g. in my database we have a date in the format yyyymmdd
so date#('20100720','YYYYMMDD') would make this recognisable as a date. so to get that in the same format as above you would have to perform the date() function on it.
