Archive for the ‘date’ Tag

Convert Excel date value to SQL date

When creating SQL statements you’ll often need a date in the ISO 8601 standard format (e.g. 2010-03-26 12:34).

Of course you can change the format in Excel to show it as such, but that doesn’t give you the string you need, e.g. in an insert or update statement.

Here’s an Excel function to make an SQL date value, presuming the date value is in cell A1:

=TEXT(A1,”yyyy-mm-dd hh:MM:ss”)

This circumvents the use of complicated IF and date/time functions. Append a “Z” if you need to indicate the timezone as UTC (i.e. GMT) time.

Here’s a short VBA function to create this type of date

Function SQLDate(d)
SQLDate = WorksheetFunction.Text(d, "yyyy-mm-dd hh:MM:ss")
End Function

Put this code in a new module in your workbook to instantly start using the function in Excel like this: “=SQLDate(A1)”

SQL date in Excel

SQL date in Excel

More information:

http://en.wikipedia.org/wiki/ISO_8601

The javascript Date object and how to add days to a date variable

JavaScript is not an object oriented language, it’s a prototype based language. This means that you can extend any existing object by just writing a new function for it on-the-fly. Here’s an example:

// add n number of days
Date.prototype.goto = function(n) {
	this.setDate(this.getDate()+n*86400000);
	return this;
};



Note: Thanks to commenter Joe I use setDate instead of setTime.
Yes I know… GOTO is considered harmful. But so is Edsger Dijkstra. Anyway, this nifty little function will add, or subtract(!), any number of days with an easy to use syntax:

// add n number of days
d = new Date; // today
d.goto(7); // move it to next week
d.goto(-365); // move to last year (well if it isn't leap)
};

Try it yourself, it’s fun. Now to make things more interesting, you can make a generic function (erm.. method?!) that will calculate the number of days since the Epoch

Date.prototype.days = function() { return parseInt(this.getTime() / (1000*24*60*60)); };

This makes stuff like comparing dates and calculating differences way easier. E.g. check out this one for calculating the number of working days between two dates:

Date.prototype.workdays = function(d2) {
	var start = new Date(this);
	var end = d2;
	var ret = 0;
	var diff = end.days() - start.days();
	if (diff > 0)
		// up to 1 year
		while (start.days() < end.days() && ret < 3650)
		{
			start.goto(1); // move to next day
			if (start.getDay() != 0 && start.getDay() != 6)
				ret++;
		}
	return ret;
};