Postgresql: convert a string to a date or NULL

We’re working with some user-submitted text that we need to convert into dates. Most of the data looks correct, but some of it looks glitchy:


See that line with “damaged” in there? That will cause to_date to throw an error:

select to_date('damaged', 'YY/MM');
ERROR: invalid value "da" for "YY"
DETAIL: Value must be an integer.

So I wrote this function:

create or replace function dt_or_null (s text, fmt text)

returns date

return to_date(s, fmt);

when others then return null;


$$ language plpgsql;

And this is how it works:

select 'damaged', dt_or_null('damaged', 'YY/MM');
| ?column? | dt_or_null |
| damaged | |
(1 row)

My advice to new programmers looking to start their career

Your resume is probably pretty good, but you need to show you can build stuff beyond school assignments. You don’t need a job to do that though! Here’s my advice:

  1. Prove that you can build and maintain something without being supervised. Build some kind of web project in your free time and host it online on AWS or rackspace or my favorite, Linode. That link has my referral code in it, by the way 🙂

    Start with something as easy as possible. Don’t worry though — you will discover a ton of difficulties as you work through it. Your project can be anything:

    • a really simple recipe database
    • the most popular mens socks on Amazon
    • weather forecast for nearby cities

    At the bottom of every screen in that project, add a link to your github profile and your linkedin page, and put your email in there and say something like “I’m looking for work!”

    Once you’re done, pick a new project. Maybe rewrite the same thing in a different language. The point here is to make real things that regular people can interact with.

    Silly projects are likely to get more attention. For example, the KJV Programming tumblr site is hugely popular and doesn’t really do anything useful for anyone.

  2. Get involved with some volunteer programming work. In Cleveland, there are several groups of programmers that volunteer their time. Look at Cleveland Givecamp, for example, or Open Cleveland.

    Where ever you are, I bet there’s a group like this already. If not, start one!

    Or, just find an organization like a church or a club or a business that you like and offer to work with them to do something like set up a better website, automate some financial reports, or even just help them manage their facebook / instagram / twitter accounts.

    You will learn how to work with non-technical people this way. That is an important skill!

  3. Start a blog.

    Write tutorials for little things you figure out while building your projects. Write tutorials for stuff that you are learning in school, like recursion, or operator overloading in C++, or why you hate or love one language vs another.

    Write about the nonprofits or clubs or small businesses you’re working with.

    Practice writing clearly and succinctly.

    Read William Strunk’s The Elements of Style at least three times. It’s nearly a hundred years old and still the best writing guide out there.

    Publish what you do on twitter and reddit and hacker news and other places so you get more attention. Don’t waste a minute arguing with the haters though. Nobody cares about them.

    Add google analytics to your blog and study what posts attract the most attention.

  4. Go to as many technical meetups as you can and introduce yourself to people and tell them you are looking for work. Talk about what you are working on. Ask them where they work and if they like it and if they know of openings.

    If you’re anywhere near Columbus, Ohio, show up at PyOhio on July 30th and 31st and introduce yourself to as many people as you can. Maybe even do a 5-minute lightning talk on one of your projects — the sillier the project is, the better.

  5. Cold-call recruiters at companies like Robert Half, Oxford, Randstad, etc and tell them you’re looking for work. Ask them what skills are the most sought after.

    Learn those skills, and build projects with them, and then write out about it.

The point with all this stuff is to make yourself a programming celebrity. You don’t want to go looking for jobs — you want jobs to come to you.

Good luck on your quest!

Consider that you are lucky to live at a time where a few of us have vastly more upward economic mobility than ever before. It just takes effort.

Are you an animal or a human?