EXPRTYPE, DATEDIFF, MICROSECOND, BIGINT and Volker Barth

Some of the minor differences between Volker Barth and me are apparent in this photo (I'm the one on the right):



However, there is a much bigger, hidden difference between Volker and me: when Volker reads something, he remembers what he's read and is able to use it in his daily life.

Unlike me, apparently.

You see, Volker's read the "What's New" sections in the SQL Anywhere Version 12 Help. I know that because of his comment on this blog post about how DATEDIFF is now returning BIGINT as well as supporting the new MICROSECOND date part.

I also know that I also read all the What's New stuff in Version 12... I know that because, well, I had to, didn't I? In order to write 10 Cool New Features In SQL Anywhere 12?

Didn't I?

I'm sure I did :)

OK, That's 12, This Is Now

For those of you working with earlier versions of SQL Anywhere, and even those lucky enough to be running Version 12, the question sometimes arises, "What data type am I getting?"

Older Help files often don't tell you what data type is returned by a builtin function. For example, the Version 9.0.2 Help file doesn't tell you that DATEDIFF returns INTEGER.

Plus, you may have coded an expression and you need to know what the resulting data type is so that, for example, you're not getting an integer when you want three decimal places.

The EXPRTYPE function was introducted in Version 9.0.0 8.0.3 for just this purpose. You pass it a 'SELECT-statement-inside-single-quotes', plus a number 1, 2, 3 telling EXPRTYPE which SELECT list item you are interested in, and it returns a string containing the data type for that item.

exprtype() works with 8.0.3, though it is not documented there. FWIW, the 8.0.3 maintenance release did not have its own doc set, just a "Readme file" with the news and changes - and unfortunately, that made it newer than the official docs, and therefore even the v9-V11 docs don't list the changes from 8.0.3. – Volker Barth

Here's what EXPRTYPE says about DATEDIFF in 9.0.2 8.0.3:

SELECT '  Version:' AS "Date Part", @@Version AS "Data Type"
UNION
SELECT ' 1. YEAR',        EXPRTYPE ( 'SELECT DATEDIFF ( YEAR,        CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 2. QUARTER',     EXPRTYPE ( 'SELECT DATEDIFF ( QUARTER,     CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 3. MONTH',       EXPRTYPE ( 'SELECT DATEDIFF ( MONTH,       CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 4. WEEK',        EXPRTYPE ( 'SELECT DATEDIFF ( WEEK,        CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 5. DAY',         EXPRTYPE ( 'SELECT DATEDIFF ( DAY,         CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 6. HOUR',        EXPRTYPE ( 'SELECT DATEDIFF ( HOUR,        CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 7. MINUTE',      EXPRTYPE ( 'SELECT DATEDIFF ( MINUTE,      CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 8. SECOND',      EXPRTYPE ( 'SELECT DATEDIFF ( SECOND,      CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 9. MILLISECOND', EXPRTYPE ( 'SELECT DATEDIFF ( MILLISECOND, CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
ORDER BY 1;

Date Part,Data Type
'  Version:','8.0.3.5379'
' 1. YEAR','int'
' 2. QUARTER','int'
' 3. MONTH','int'
' 4. WEEK','int'
' 5. DAY','int'
' 6. HOUR','int'
' 7. MINUTE','int'
' 8. SECOND','int'
' 9. MILLISECOND','int'

Here's what it says about DATEDIFF in Version 12:

SELECT '  Version:' AS "Date Part", @@Version AS "Data Type"
UNION
SELECT ' 1. YEAR',        EXPRTYPE ( 'SELECT DATEDIFF ( YEAR,        CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 2. QUARTER',     EXPRTYPE ( 'SELECT DATEDIFF ( QUARTER,     CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 3. MONTH',       EXPRTYPE ( 'SELECT DATEDIFF ( MONTH,       CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 4. WEEK',        EXPRTYPE ( 'SELECT DATEDIFF ( WEEK,        CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 5. DAY',         EXPRTYPE ( 'SELECT DATEDIFF ( DAY,         CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 6. HOUR',        EXPRTYPE ( 'SELECT DATEDIFF ( HOUR,        CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 7. MINUTE',      EXPRTYPE ( 'SELECT DATEDIFF ( MINUTE,      CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 8. SECOND',      EXPRTYPE ( 'SELECT DATEDIFF ( SECOND,      CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT ' 9. MILLISECOND', EXPRTYPE ( 'SELECT DATEDIFF ( MILLISECOND, CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
UNION
SELECT '10. MICROSECOND', EXPRTYPE ( 'SELECT DATEDIFF ( MICROSECOND, CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 )
ORDER BY 1;

Date Part,Data Type
'  Version:',12.0.0.2589
' 1. YEAR',int
' 2. QUARTER',int
' 3. MONTH',int
' 4. WEEK',int
' 5. DAY',int
' 6. HOUR',bigint
' 7. MINUTE',bigint
' 8. SECOND',bigint
' 9. MILLISECOND',bigint
'10. MICROSECOND',bigint

If you like EXPRTYPE, you'll love sa_describe_query().

Only in 12, though.