Skip to Content.
Sympa Menu

freetds - [freetds] notes on new DATE type

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: jklowden AT schemamania.org
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: [freetds] notes on new DATE type
  • Date: Fri, 6 May 2011 18:33:11 -0400

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






Archive powered by MHonArc 2.6.24.

Top of Page