FreeTDS Development Group

[jklowden AT schemamania.org]

Some notes on the new DATE type.  

http://msdn.microsoft.com/en-us/library/bb677301.aspx

Type            Token   Size
--------------- ------  --------
DATE            0x28     3 bytes
TIME            0x29     6 bytes
DATETIME2       0x2a     9 bytes
DATETIMEOFFSET  0x2b    11 bytes

Sample data:

1> select * from d
2> go
 name                 smdate
 -------------------- --------------------
 a                    1960-01-01
 a                    1960-02-01
 a                    1960-03-01
 a                    1960-04-01
 a                    1960-06-01
 a                    1960-10-01
 a                    1960-11-01
 a                    1960-12-01
 
1> select cast(smdate as varbinary) from d
2> go

 -----------------------------------------
 0xf5ea0a
 0x14eb0a
 0x31eb0a
 0x50eb0a
 0x8deb0a
 0x07ec0a
 0x26ec0a
 0x44ec0a

These values consistent with the windump+pcap capture below.  Notable: 

1.  tds.html is out of date versus our current knowledge. Cf. the size of
variable-length data in a D1 packet.  

2.  Is there a new 2-byte field after the flags in an 0x81 packet? 

3.  Size of DATE is always given as 3, apparently, even though the 0x81
provides no size, implying it's fixed-length.  

4.  Even with TDSVER=7.2, bsqlodbc receives DATE from the server as 
varchar(20).
 I don't know yet how to convince the server to send DATE tokens.  

I haven't decoded the values yet.  ISTR discussion, or maybe code, but neither
we nor jTDS documents the type afaik.  

Dump follows.  It's an extract of a windump capture of a BCP.EXE session
uploading 8 rows.  

--jkl


0x0000:  4500 00ab 7870 4000 8006 0000 0a51 259b  E...xp@......Q%.
0x0010:  0a52 5092 f5d7 09c4 793a a33a b071 8e11  .RP.....y:.:.q..
0x0020:  5018 3fe8 8b6d 0000 0701 0083 0000 0100  P.?..m..........
0x0030:  8102 0000 0000 000a 00a7 1400 0904 0001  ................
0x0040:  0004 6e00 6100 6d00 6500 0000 0000 0800  ..n.a.m.e.......
0x0050:  2806 7300 6d00 6400 6100 7400 6500 d101  (.s.m.d.a.t.e...
0x0060:  0061 03f5 ea0a d101 0061 0314 eb0a d101  .a.......a......
0x0070:  0061 0331 eb0a d101 0061 0350 eb0a d101  .a.1.....a.P....
0x0080:  0061 038d eb0a d101 0061 0307 ec0a d101  .a.......a......
0x0090:  0061 0326 ec0a d101 0061 0344 ec0a fd00  .a.&.....a.D....
0x00a0:  0000 0000 0000 0000 0000 00              ...........

Analysis:

0x0000:  4500 00ab 7870 4000 8006 0000 0a51 259b  E...xp@......Q%.
0x0010:  0a52 5092 f5d7 09c4 793a a33a b071 8e11  .RP.....y:.:.q..
0x0020:  5018 3fe8 8b6d 0000 0701 0083 0000 0100  P.?..m..........
0x0030:  

81      packet: tds result
02 00   2 columns

00 00   usertype
00 00   flags
0a 00   ???
a7      XSYBVARCHAR
14 00   size: 20 bytes
09 04 00 01 00  collation
04      namelen: 4 UCS-2 characters
6e00 6100 6d00 6500 "name"

00 00   usertype
00 00   flags
08 00   ???
28      new type: TDSMSDATE
        (no size)
        (no collation)
06      namelen: 6 UCS-2 characters
7300 6d00 6400 6100 7400 6500   "smdate"

d1      packet: data row
0100    size: 1 single-byte character
61      "a"
03      size: 3 bytes, N.B. NOT NULL! 
f5 ea 0a 

d1      packet: data row
01      size: 1 single-byte character
61      "a"
03      size: 3 bytes
14 eb 0a 

d1      packet: data row
0100    size: 1 single-byte character
61      "a"
03      size: 3 bytes
31 eb 0a 

d1      packet: data row
01      size: 1 single-byte character
61      "a"
03      size: 3 bytes
50 eb 0a 

d1      packet: data row
0100    size: 1 single-byte character
61      "a"
03      size: 3 bytes
8d eb 0a 

d1      packet: data row
01      size: 1 single-byte character
61      "a"
03      size: 3 bytes
07 ec 0a 

d1      packet: data row
0100    size: 1 single-byte character
61      "a"
03      size: 3 bytes
26 ec 0a 

d1      packet: data row
01      size: 1 single-byte character
61      "a"
03      size: 3 bytes
44 ec 0a 

fd      packet: Result Set Done
0000    flags
0000    officially unknown? 
00 00 00 00
00 00 00 00     row count: 0