OK, it's a feature, not a bug, but successfully converting between a TIMESTAMP and an INTEGER is [cough] unexpected, especially when the TIMESTAMP becomes a DATE.

So... where can I read about this behavior, and what else I should watch out for?

BEGIN
DECLARE @x INTEGER;
DECLARE @y TIMESTAMP;
SET @x = CURRENT TIMESTAMP;
SET @y = @x;
SELECT @@VERSION, @x, @y, ISDATE ( @x );
END;

@@VERSION,@x,@y,ISDATE(@x)
'12.0.1.3298',20120727,'2012-07-27 00:00:00.000',1

asked 27 Jul '12, 11:47

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

edited 27 Jul '12, 11:50


This is covered in Ivan's whitepaper on mixed-type comparisons, which can be found on my blog.

permanent link

answered 30 Jul '12, 08:48

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

Comment Text Removed
Comment Text Removed

This is the closest thing I can find readily: http://dcx.sybase.com/index.html#1201/en/dbreference/comparisons-date-wsqltype.html

"Exact numeric values of type SMALLINT, INTEGER, BIGINT, and NUMERIC can be converted to date values. The conversion is performed by treating the number as a string. For example, the integer value 20100401 represents the first day of April in 2010."

permanent link

answered 27 Jul '12, 22:04

John%20Smirnios's gravatar image

John Smirnios
8.9k377112
accept rate: 39%

Comment Text Removed
Comment Text Removed
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×102

question asked: 27 Jul '12, 11:47

question was seen: 749 times

last updated: 30 Jul '12, 10:14