Let's play "Gotcha!" - Round Two

Welcome to the second round of "Gotcha!", the new self-testing quiz game for developers who work with SQL Anywhere.

Let's get started right away with a warm-up question:

What does SELECT DATEDIFF ( DAY, '2011-09-28', '2011-09-29' ) return?

The answer is 1!

Here's proof using SQL Anywhere 12.0.1:

SELECT DATEDIFF ( DAY, '2011-09-28', '2011-09-29' );

DATEDIFF(day,'2011-09-28','2011-09-29')
1

The rules for Gotcha!

You will be asked three skill-testing questions involving SQL Anywhere, and you must answer all three correctly to move on to the next round.

Two out of three right answers don't count, you have to get them all.

No computers!

You have to answer all the questions without looking anything up, and without running any actual tests. This is a test of your SQL Anywhere knowledge, not your prowess at Googling the docs.

Here we go...

---

Question One

What does SELECT DATEDIFF ( SECOND, '2011-09-28 23:59:58', '2011-09-28 23:59:59' ) return?

Listen to this while working on your answer, then scroll down to see if you got it right...



Here's the answer, it's 1!

SELECT DATEDIFF ( SECOND, '2011-09-28 23:59:58', '2011-09-28 23:59:59' );

DATEDIFF(second,'2011-09-28 23:59:58','2011-09-28 23:59:59')
1

---

Question Two

What does SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:58', '7910-12-31 23:59:59' ) return?

It's not a trick question; according to the Help, "the useful range of TIMESTAMP date types is from 1600-02-28 23:59:59 to 7911-01-01 00:00:00."



Once again, the correct answer is 1!

SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:58', '7910-12-31 23:59:59' );

DATEDIFF(second,'7910-12-31 23:59:58','7910-12-31 23:59:59')
1

Congratulations to everyone who has two out of three right so far... only one more to go!

---

Question Three

What does SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:59', '7911-01-01 00:00:00' ) return?

Remember what the Help said, "the useful range of TIMESTAMP date types is from 1600-02-28 23:59:59 to 7911-01-01 00:00:00."



Did you say 1? Are you sure? Want a hint?

Let's ask Microsoft Excel 2010:





How about another hint; let's ask Microsoft SQL Server 2008:

1> SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:59', '7911-01-01 00:00:00' )
2> GO

 -----------
           1

But once again remember, this is a question about SQL Anywhere, not Excel or SQL Server...

...so don't rush, take your time.



Here's the answer...

Gotcha!

The answer, according to SQL Anywhere 12.0.1, is 176,422,921!

SELECT DATEDIFF ( SECOND, '7910-12-31 23:59:59', '7911-01-01 00:00:00' );

DATEDIFF(second,'7910-12-31 23:59:59','7911-01-01 00:00:00')
176422921

For you winners, see you all back here in two days for Round Three of "Gotcha!"

For the rest of you, don't let your SQL code stray outside the true useful range for the TIMESTAMP data type which is 1600-02-28 23:59:59 to 7910-12-31 23:59:59.

After that, it's all one big Y7.911K party!

---