Converting to (and from) Julian Date (YYDDD) format in T-SQL

I often get asked how to convert a datetime into Julian Date format in T-SQL. People have differing opinions about what Julian means, but the one I got asked about most recently meant YYDDD, as often used by mainframe systems (I think this is Julian Date, as opposed to Julian Day which is the number of days since 4713BC). SQL Server doesn’t have a TO_JULIAN function, but we can make one easily enough.

So we’re wanting to express a date as YYDDD, where YY is the two-digit form of the year, and DDD is the number of days since Dec 31st of the previous year (ie, the DDDth day of the year).

Using the DATEPART function can get each part. YY for the year, and DY for the day of the year. I’m going to use @date as a variable here, of type datetime. Using the date type in SQL 2008 would work just the same.

SELECT DATEPART(yy, @date), DATEPART(dy, @date)

However, to make sure that we have the year in two-digits only, we should convert this to a string and get the rightmost two characters.

SELECT RIGHT(CAST(DATEPART(yy, @date) AS char(4)),2)

We also need to pad the DDD with zeroes – which I’ll do by putting three zeroes in front of the number and getting the three rightmost characters.

SELECT RIGHT('000' + CAST(DATEPART(dy, @date) AS varchar(3)),3)

Concatenating the YY and the DDD, we now have a TO_JULIAN function.

SELECT RIGHT(CAST(YEAR(@date) AS CHAR(4)),2) + RIGHT('000' + CAST(DATEPART(dy, @date) AS varchar(3)),3)

Converting back again isn’t too hard – it’s just a matter of pulling the numbers out of the 5-character string. I’m going to assume we have a char(5) called @julian.

We need to split the string up first.

SELECT LEFT(@julian,2), RIGHT(@julian,3)

The first bit becomes the year easily enough

SELECT CONVERT(datetime, LEFT(@julian,2) + '0101', 112)

The second half can be cast to a number, and then added back (subtracting one to get the maths right) using DATEADD.

SELECT DATEADD(day, CAST(RIGHT(@julian,3) AS int) – 1, CONVERT(datetime, LEFT(@julian,2) + '0101', 112))

So now we have a FROM_JULIAN function:

SELECT DATEADD(day, CAST(RIGHT(@julian,3) AS int) – 1, CONVERT(datetime, LEFT(@julian,2) + '0101', 112))

Easy stuff really, just a matter of thinking about what we mean by a particular format.

A review — Microsoft Windows PowerShell Step By Step (Ed Wilson)

Another book review, and another giveaway for the Adelaide SQL Server User Group. This time, it’s Microsoft Windows PowerShell Step By Step.

Last month I had picked up the Windows PowerShell Scripting Guide, wondering if it was going to be a good recommendation for people who were interesting in getting into PowerShell. Even though I thought the book was very good (particularly if you want to use PowerShell to access the innards of a Windows installation), it didn’t seem like the right book for recommending for PowerShell beginners.

This book is though, and I’ll definitely recommend it for people wanting to get into PowerShell.

It’s worth pointing out that it’s a thinner (and cheaper) book than the other one. It certainly doesn’t cover how to perform the variety of Windows Admin commands that the Scripting Guide did. But what it replaces that with is a guide on getting the most out of PowerShell. PowerShell is used in so many different products now, it’s no longer just the domain of Windows Administrators. Developers can use PowerShell for unit tests. DBAs can use PowerShell to perform routine maintenance. Just about everyone in IT could use PowerShell to make their job easier. My background is in development, not system administration, so I’m always keen to write code to automate tasks. I was never that keen on VBScript, but PowerShell gives me a much richer environment while also being much closer to the system itself. I can hook into subsystems of Windows and .Net objects easily, and into environment variables, certificates and more, piping the results into other functions and utilities to extend the scripts as much as my imagination can provide. I’m always happy to recommend PowerShell as an important skill for the future.

And this book can people get introduced to PowerShell, walking them (step by step, just like the title suggests) into the depths of PowerShell — leveraging functions and providers, and a good introduction to using PowerShell with WMI and Exchange. I’ve enjoyed reading it, and plan to re-read it a few times over the next months, looking for those techniques that I’m not using (yet) but that I’d like become more familiar with. PowerShell reminds me of my early days using the vi editor (which I still use as my preferred text editor in Windows). We were forced to use vi at university, and the learning curve felt very steep. It seemed as if I learned some new (and better) way of doing something every day, to the extent that these days I still find it by far the quickest way to edit many types of text files. This book feels like those fellow students of mine, pointing out features I didn’t know existed even after I felt proficient (although I’m pleased to mention, not as many as I thought there might have been).

The book also has a CD full of examples that I need to find time to go through (and tweak, and practise, and learn). It includes a bunch of utilities, and an electronic copy of the book as well. Having said that, the book isn’t too big to carry with me for a while, and I’m sure will be a regular read for those “no electronic device” periods of flights.

Things You Know Now

This blog meme is doing the rounds… I’ve been tagged at least twice now (Jason Strate and Greg Linwood), so I suppose subconsciously I’ve been thinking about this stuff for a few weeks already.

Since I do a lot of training, I tend to explain these things to my students anyway. I have a lot of opportunity to stand up in front of people and tell them important stuff – so this kind of thing definitely comes up now and then.

Things I wish I had known years ago (career-wise that I would teach new people in the SQL field)

The Importance of technical communities

I remember when Craig Bailey wrote about his ideal role. It wasn’t new stuff – I had heard it all before, but it certainly got me thinking about how people can influence where they are in that Venn diagram. For Craig, he wanted his ideal role to be a job that he was good at and that he enjoyed. Obviously to be a job, someone has to be prepared to pay him sufficiently too.

Being good at something you enjoy isn’t hard, and you can invest your own time (outside of the job that you don’t enjoy) developing your skills. For people in IT, I suggest they pick a particular area they find interesting, and start getting their skills up. If they can become expert-level in that area, then great.

The next problem though, is moving that skill into something that lets you can earn money. Community can help that. Community can help you develop your skills, because you’re spending time with other people in your field. But as you become an expert, presenting at community events, developing a profile, you find yourself being differentiated from the rest. If nothing else, people know you have presentation skills. Every presentation can become like a job interview – showing your skills and ability to communicate information to clients, colleagues, whoever.

Presenting isn’t easy, but there are plenty of other communities that can help develop those skills. You can get along to a ToastMasters group, or offer to do presentations in a group to which you already belong.

You might be the best in the world at what you do – but you need to get out there. I enjoy the technical communities, and run the Adelaide SQL Server User Group because I enjoy it. But I can’t deny that it’s been useful for my career. Now, I’m wishing that I had got involved many years ago.

Enjoy public speaking

According to the old saying, more people are afraid of public speaking than death (so at a funeral, they’d rather be in the coffin than giving the eulogy). But it’s a useful skill to have, so learn to enjoy it.

Keep in touch with old friends

This isn’t quite so career-related, but is actually very important for your career nonetheless.

There are people that I haven’t seen in years, who I have no idea how to contact. Facebook (and the internet in general) has proved very useful for that, but still there are many people that I wish I could find. Most of them are just people I would like to spend time with now and then, but some are people that I’d happily offer to do some work for. And perhaps some of them would contact me to do some consulting if they knew how to reach me (clue, there’s contact information to the side of my blog site!).

Far too many people fall out our lives, and it’s sad. I’m still not great at it, but I do think I should take the time to write people letters now and then (emails, Facebook comments, Instant Messages are all fine too – I’m just talking about touching base to keep the contact there).

Certifications aren’t worth studying for (but they are worth taking)

I used to study for exams. I first became a Microsoft Certified Professional back in 1998, passing an exam called “Architecture I”. Since then I’ve passed over 30 exams, and earned plenty of certifications. But a few years ago I worked out at that it’s just not worth studying for these things.

A MCP exam is not like high school or university. If you fail, you can just try again. Fails don’t appear on your transcript, only the passes do. It’s like your driving test – if you fail, you just try again. Once you pass, you get access to the roads like everyone else.

If you spend weeks studying for a MCP exam, you probably won’t even improve your chances of passing – you’ll just be spending precious family time trying to learn those things to get you past the line. You might even start losing sleep over it.

Nowadays, I tell my students (and myself) to care less. Plenty of people say “No, you don’t understand – I can’t fail at anything.”, and I understand that. I’m not particularly good with failure either. But I’ve learned to not care so much. I don’t want to waste time sitting an exam only find that I fail (or spend $180 on the privilege), but I also don’t want to waste time studying for an exam that I could probably pass anyway. With the Second Shot offer that is often around you’ve paid for two attempts, so go into the first one blind.

The amount of time you invest in getting a certification is largely the study time. So if you can reduce that, the certification becomes a lot cheaper – in which case, it’s probably worth taking the few hours to give it a try. If you do fail, you know you have a weak area, so you can improve that with study – just don’t bother studying before the first try.

[Edited: I should make it very clear that I definitely approve of learning new skills, and preparation for an exam is a great prompt for this learning. Better still is learning for the sake of getting those new skills, with the focus being an upcoming project or new role. My advice above is focussed on people who have the skills necessary to pass an exam.]

Reading execution plans, and understanding indexes

I’ve always been good at solving problems with T-SQL (or PL/SQL for that matter) – I just took to it naturally when I got into databases. But it took me several years to actual venture into understanding what the query is actually doing when it runs. Now, I look at the execution plan for every query I write, as default behaviour, and I consider the indexes that I want up front.

Perhaps it’s because I was a programmer first, but I had always trusted the compiler to do things the right way. I had looked a bit past my code when studying Prolog at university, but it took me a long time to make that my default behaviour.

So when I find people who are just getting into T-SQL, I encourage them to look at the execution plans, and start getting a feel for what’s going on behind the scenes. You can often improve a query without looking at the execution plan, but if you want to write really good T-SQL and have well-performing queries, you need to make the execution plan part of the process.

The significance of BI to businesses

I was involved in data warehouses in some of my first projects when I left university, I just didn’t realise at the time. I first got involved in SQL Server in version 6.0, and quite early on I migrated a system to 6.5, and created a data warehouse to allow for various reports. In hindsight, I was making a data warehouse. I had an ETL process, calculated aggregates, considered the dimensions and granularity, all that. But it wasn’t called a data warehouse, and I only realised a few years later that it really was one.

If I had’ve realised, then I’m sure I would’ve jumped into the BI space much earlier. Companies love BI – it’s one of the most empowering areas of database technology for any business.


I’ve picked a few things here – and I hope people somehow get some benefit from reading it. I have put it in my ‘must read’ list to find other people’s responses, because I’m sure there are things that I’m still to learn.

Tagging some other people: Simon Sabin, Jamie Thomson, Deepak Kapoor, Grant Paisley