SQLSaturday #162: Cambridge, England
September 9th, 20124
SQLSaturday #162: Cambridge, England
September 9th, 20124

Yesterday I presented at SQLSaturday #162.

I also wanted to answer a question from an audience member after the session about how to generate YYYYMMDD strings to represent yesterday's date in order to append to a backup file name. In this case because we're probably not worried about performance (you're performing this calculation once), we can just use string conversion (see this blog post for a more detailed discussion about doing things like stripping time from datetime at high volume).

-- In SQL Server 2005, 2008 or 2008 R2, you can say:
SELECT [filename] = 'Backup' + CONVERT(CHAR(8), DATEADD(DAY, -1, GETDATE()), 112);
-- In SQL Server 2012, you can use a slightly more self-documenting method:
SELECT [filename] = 'Backup' + FORMAT(DATEADD(DAY, -1, GETDATE()), 'yyyyMMdd');

I was really impressed with how this event was run – it was super organized and I had a great time, so kudos to Mark and Lorraine Broadbent and everyone else who helped by speaking, volunteering and sponsoring.

Today I was able to take a walk around "The Backs" (the walkways along the river behind all the famous colleges here), then went to Castle Mound with Buck Woody & family, then Scott and I took a train to Ely to see the 4th largest cathedral in Europe. A good day. Pictures here:

    Cambridge | Ely

By: Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a husband, a father of two, a huge hockey and football fan, and my pronouns are he/him.

4 Responses

  1. AaronBertrand says:

    Thanks Zuzana!

  2. Zuzana says:

    Thank you. It was a really great presentation. Will go and play with dates now.

  3. AaronBertrand says:

    Steve, sure, if all your code is listed out within the job step. If you're using more complicated logic you're probably using stored procedures and – especially if you need to call them outside the context of Agent as well – the tokens don't help much. 🙂

  4. Steve Morris says:

    even easier use Job Tokens :-