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

How to remove duplicates from a list

There are tons of ways to remove duplicates from a list of items, most of which are way too complicated and technical for a noob (if you don’t know what this is, then that’s you) to perform.

Let’s look at some of them, and let me know in the comments if you think these were useful. Here they come, in order of increasing difficulty (geekness)

TextPad

removing duplicates with textpad
textpad sort & remove

TextPad is a free (well, nagware) text editor with so many built in tools I cannot talk about it without crying…

  • open the file in TextPad
  • select Tools > Sort
  • check the box at ‘remove duplicate lines’
  • click OK

Excel

Another program abused by millions (billions?) to do stuff that could be done with a 10 year old cellphone. What the bozos at Microsoft tell you is the dumbest way to do it, because you’re overwriting your original list. Here’s the smart way

  • Excel Pivot
    Excel Pivot

    Select the data

  • Click Data > PivotTable… (Office 2003) or Insert > PivotTable (Office 2007)
  • Click ‘Finish’ to create a new sheet with an empty pivot table
  • Drag the column for which you need to remove duplicates into the left part of the pivottable
  • adding flavor: you can now sort, filter, group, analyze, you name it.

SQL

If your data is in a database, and you have access to SQL, perform the following query:

  • SELECT DISTINCT(column) FROM table
  • in place of ‘column’ you type what you need to be unique
  • in place of ‘table’ you type the table name
  • tip: you can combine more columns by typing (column1, column2, …)

Linux

If you have the file on a linux or unix system, from the terminal (command line) type

  • sort -u file > output
  • where ‘file’ is the name of your file and ‘output’ is the name of the output file.

I’d like to know if you can come up with new and maybe even faster ways!

How to ruin your website in 10 steps

I have worked for almost 10 years in web development now, and here’s what I have learned from the clients: you can ruin your website too!

Here’s how:
1. Get the cheapest designer you can get. If your nephew says he can do it, let him do it. It’s not that hard.
2. That being said, you can also do it yourself. Why pay for some fancy designer when you can buy or get a template. And HTML+CSS is easy, right?
3. Even cheaper, copy someone else’s site and just change the text.
4. Don’t start a forum or community. There are a lot of people out there ready to say nasty things about you.
5. You should get a CMS if possible. Then you can control everything yourself real easy, and choose any font and color. Did you know with a CMS you can just copy straight from MS Word? You can make sure people notice your links by using a big red font.
6. Don’t listen to any advice from your designer. They’re just after your money, not to help you.
7. If it looks good in Internet Explorer, then you’re done. There are so many browsers there’s no keeping up with it, and most people use IE anyway.
8. Almost everyone has broadband, so keep your images in high quality. Browsers resize them anyway when they display them.
9. Tables are cool. With tables you can get layouts done that are really hard without them.
10. Get as much information from your visitors as possible. When they sign up, ask for name, gender, location, email, interests. You can even sell that information later and make a good profit.

And all of the above will help you to get the lowest possible position on Google. But there are companies that can help you to become #1, just wait for the ‘offers’ to come in.