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

Advertisements

9 thoughts on “Convert Excel date value to SQL date

  1. Not working… I have a string like 27-12-1961 in h3 and in I3, where I run your function, I get 27-12-1961. Sorry, but this does not seem to be working…

    1. You should check your d-m-y settings in Windows. These differ per country. In the US setting, you need to enter 12-27-1961. Only then will the value be recognized as a date.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s