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

7 comments so far

  1. Imran on

    This was very useful! Thanks.

  2. Pbak on

    Thank you!

  3. Mandy on

    Amazing!! I have been trying for months to find a solution to this so thank you so much!

  4. Jim on

    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…

    • michiel on

      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.

  5. Hashim on

    Really awesome…

  6. kshitij on

    Thank you so much!! tis was much helpful


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

%d bloggers like this: