Oracle SQL date values

Found some really useful information on SQL date values at the excellent SQL FAQ. I've added a couple of 'real world' examples at the bottom that I used in some recent development work:

The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date.

Look at these examples:

SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;
SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13

The following format is frequently used with Oracle Replication:

select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;
NOW                  NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53

Here are a couple of examples:

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,MONDAY' ' ),NEXT_DAY(sysdate,WEDNESDAY), NEXT_DAY(sysdate,FRIDAY ))) + (9/24)

Real world examples:

(i) This first one sets the date to be the 1st of the month (by using the last_day function to get the end of the current month, then add 1 day) and the time to be 7am (7/24):

update mc_jobs set scheduledtime=TRUNC(LAST_DAY(SYSDATE ) + 1) + 7/24 where jobid=3596

(ii) And this one uses next_day to set the date to be the next Monday after today, at 6am (6/24):

update mc_jobs set scheduledtime=NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 6/24 where jobid=3536

Best. Offseason. Ever.

I've only been following the Yankees closely since '96 so my frame of reference is pretty small, but the news that Mark Teixeira has agreed to an 8 year $180m deal catapults this offseason into a class all of it's own.
The Yankees have now mopped up 3 of the top 4 free agents available, after previously agreeing to deals for CC Sabathia and AJ Burnett to address the urgent need for quality pitching at the top of their rotation.
Now, by signing Teixeira, they have added a quality bat (combined .308 with 33 homers and 121 RBI for the Atlanta Braves and Los Angeles Angels last season), ensured there will be protection for A-Rod in the middle of the lineup, and instantly upgraded the defence.
All of this has been achieved without dismantling the farm system that Brian Cashman has worked so hard to rebuild over the last few seasons.
Personally I'm also hoping that this means Brett Gardner will get a chance to start the season as the centre fielder, as there isn't such a pressing need for offensive output from that position now.
I'm in awe of Cashman, the financial might of the Yankees....and I can't wait for Opening Day to come round!

CC and AJ

The New York Yankees officially announced the signings of free agent pitchers CC Sabathia and AJ Burnett today.
CC in particular is an exciting signing, although he hasn't come cheaply at $161m over 7 years (and an opt-out clause after 3 years) - the richest deal for a pitcher in MLB history. The American League CY Young winner in 2007, he gives the Yankees a true ace at the top of their rotation.
AJ is a different proposition though. On his day he has incredible stuff, but his track record with injuries is worrying to say the least (particularly given that the contract runs for 5 years, meaning he'll be a Yankee until age 37). He was dominant against the Yankees last year though, going 3-1 with a 1.64 ERA in five starts. He's also 5-0 lifetime against the Red Sox, which is always welcome. :-)
Check out CC's site for more info, and the great coverage over at River Ave. Blues.

FM2009 - Genie arrives!

The rather excellent FM Genie Scout 2009 has been released for download, either from the Genie site (which is down as I write this) or from the equally excellent FM Scout site.

The interface has been completely re-designed, and bears more than a few similarities with the latest version of Microsoft Office. Not necessarily a bad thing, and it seems to work fine against the saved game I have to hand. I have noticed one or two glitches and it disappointingly doesn't seem to provide the option of viewing or searching on a player's current/potential rating, instead focusing on the calculated effectiveness percentage. In theory this is a more accurate reflection of ability, but I must admit I prefer using the rating allocated within the game as a starting point.

But if you're after the most effective way of scouting new players you should definitely give it a try.

Fallout 3 extended?

One of my friends pointed me in the direction of an interview on IGN with the lead designer of Fallout 3, Jeff Gardiner. The key thing for me was at the end:

"IGN: Not to spoil the end of Fallout 3, but extending the story in the Broken Steel download might be a bit tricky. Will the main character still continue through for that storyline? Will he or she be usable in the other downloads?

Gardiner: Based on a lot of feedback, we're going to allow the player to continue on after the main quest ends in the Broken Steel DLC. While a lot of details still have to be sorted, this will allow the player to continue on and play in the Wasteland enjoying the side and freeform quests, along with any new downloadable content we have planned in the future."

Not only is it great news that there is going to be more content for Fallout 3, but opening up the end of the game is awesome. Obviously a lot of people felt the same way I did about how the main quest plays out, and it's really nice to see that Bethesda have listened.

The Friends Problem

I love xkcd, but the latest strip is one of the best. Also poignant as I've been in the same situation, and it worked out just as well! If only I'd had xkcd back then....

RTFM!

You know how you get used to something that always works the same way? It feels comfortable, safe almost. You understand what's going on and that brings with it a sense of order.

Of course, when that certain something suddenly stops working it brings with it a sense of extreme frustration and key bashing anger!

Which is what I've been experiencing with FM09 and the tactics editor.

Editing tactics comprises creating a basic positional picture of the formation, providing overall team instructions, and then modifying individual settings to suit particular players. To a certain extent the positional and individual elements have been independent; the former used to indicate where on the field your players will start and move when your team has possession or not, and the latter to tweak how certain players may deviate from the norm for your team tactics.

With FM09 these two are now linked a lot more closely, a change I was unaware of and didn't pick up from my cursory 3 second scan of the manual.

In previous FM versions you could add a forward or backward arrow (farrow or barrow) to a player to indicate that they may run forward when you have possession of the ball or drop back when you don't. In this version that arrow is directly linked to the "Forward Runs" setting on the individual instructions screen. Which probably makes more sense.

So don't get annoyed or frustrated when your carefully crafted tactics seem to alter themselves while you're still creating them. Like I did. Grrr.

Oh, and RTFM. :-)