Let's play "Gotcha!" - Round Three

Welcome to the third and final 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 DATEADD ( DAY, 1, '2011-09-30' ) return?

The answer is 2011-10-01!

Here's proof using SQL Anywhere 12.0.1:

SELECT DATEADD ( DAY, 1, '2011-09-30' );

DATEADD(day,1,'2011-09-30')
'2011-10-01 00:00:00.000'

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 this query return?

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

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



Here's the answer, it's 2011-09-28 23:59:59, the same as the second date in the nested DATEDIFF call!

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

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

---

Question Two

What does this query return?

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

It's not a trick question; remember back to what we learned from Round Two of Gotcha!: the true useful range for the TIMESTAMP data type is 1600-02-28 23:59:59 to 7910-12-31 23:59:59.



Once again, the answer is the same as the second date in the nested DATEDIFF call: 7910-12-31 23:59:59!

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

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

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

---

Question Three

What does this query return?

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

Remember what Round Two of Gotcha! said: the true useful range for the TIMESTAMP data type is 1600-02-28 23:59:59 to 7910-12-31 23:59:59.



Did you say 7910-12-31 23:59:59? Are you sure? Want a hint?

Let's ask Microsoft Excel 2010:





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

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



Here's the answer...

Gotcha!

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

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Value 31556908801 out of range for destination
SQLCODE=-158, ODBC 3 State="22003"

Congratulations to all you winners out there!

For the rest of you, don't let the fact that DATEDIFF has been enhanced to return BIGINT values...

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

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

...lead you to assume that DATEADD will accept a BIGINT... it won't; you're stuck using INTEGER values.

But don't feel bad, Microsoft SQL Server 2008 doesn't even get that far, it chokes on the DATEDIFF:

1> SELECT DATEDIFF ( SECOND, '6910-12-31 23:59:58', '7910-12-31 23:59:59' )
2> GO
Msg 535, Level 16, State 1, Server ENVY, Line 1
The datediff function resulted in an overflow. The number of dateparts
separating two date/time instances is too large. Try to use datediff with a
less precise datepart.

---