Inserting datetime with milliseconds in SQL Server

[Dave Pylatuk]

Hello all.

I am attempting to insert a row into a table with a datetime column:

When the insert statement contains a value for the millisecond portion
of the data time column: ie. {ts '2003-11-05 12:02:43:2960'}

I get 'Syntax error converting datetime from string'

When I insert a value like: {ts '2003-11-05 12:02:43'}
with no millisecond value it succeeds.

Any help would be appreciated.
Thanks

[Joe Weinstein]

Dave Pylatuk wrote:

Hello all.

I am attempting to insert a row into a table with a datetime column:

When the insert statement contains a value for the millisecond portion
of the data time column: ie. {ts '2003-11-05 12:02:43:2960'}

I get 'Syntax error converting datetime from string'

When I insert a value like: {ts '2003-11-05 12:02:43'}
with no millisecond value it succeeds.
The MS datetime column is documented to have an accuracy
of only about .3 seconds anyway, so I suppose your insert
may work if you truncate the value to no more than 3 digits
after the seconds, but the actual value stored will mostly
be only approximately what you had to begin with...

Joe Weinstein at BEA
Any help would be appreciated.
Thanks

[David Portas]

Use a decimal point to indicate the millisenconds. Only three digits decimal
precision allowed.

Either of these will work:

INSERT INTO foo VALUES ({ts '2003-11-05 12:02:43.296'})
INSERT INTO foo VALUES ('2003-11-05T12:02:43.296')

SQLServer's DATETIME data type rounds to the nearest 3 milliseconds so the
above values are inserted as:

2003-11-05 12:02:43.297

--
David Portas
------------
Please reply only to the newsgroup
--

[Gert-Jan Strik]

The datetime format you are using is unknown to SQL-Server. For example,

select cast('2003-11-05 12:02:43:2960' as datetime)

will throw an error. If you leave out the last digit, it might work,
because

select cast('2003-11-05 12:02:43:296' as datetime)

returns

---------------------------
2003-11-05 12:02:43.297

(1 row(s) affected)

HTH,
Gert-Jan
Dave Pylatuk wrote:

Hello all.

I am attempting to insert a row into a table with a datetime column:

When the insert statement contains a value for the millisecond portion
of the data time column: ie. {ts '2003-11-05 12:02:43:2960'}

I get 'Syntax error converting datetime from string'

When I insert a value like: {ts '2003-11-05 12:02:43'}
with no millisecond value it succeeds.

Any help would be appreciated.
Thanks

[Dave Pylatuk]

Thanks Joe.

I tried 4 then 3 then 2 then 1 digit after the seconds portion.
All with no success.

Any other ideas.
Thanks.
"Joe Weinstein" <jo*******@bea.com> wrote in message
news:3F**************@bea.com...

Dave Pylatuk wrote:

Hello all.

I am attempting to insert a row into a table with a datetime column:

When the insert statement contains a value for the millisecond portion
of the data time column: ie. {ts '2003-11-05 12:02:43:2960'}

I get 'Syntax error converting datetime from string'

When I insert a value like: {ts '2003-11-05 12:02:43'}
with no millisecond value it succeeds.

The MS datetime column is documented to have an accuracy
of only about .3 seconds anyway, so I suppose your insert
may work if you truncate the value to no more than 3 digits
after the seconds, but the actual value stored will mostly
be only approximately what you had to begin with...

Joe Weinstein at BEA

Any help would be appreciated.
Thanks

[Joe Weinstein]

Dave Pylatuk wrote:

Thanks Joe.

I tried 4 then 3 then 2 then 1 digit after the seconds portion.
All with no success.

Any other ideas.
Thanks.
Wait! I see a syntax error in your JDBC. The separator
between secs and subseconds is supposed to be a '.'.
Try this:

{ts '2003-11-05 12:02.43'}

Joe Weinstein at BEA
"Joe Weinstein" <jo*******@bea.com> wrote in message
news:3F**************@bea.com...

Dave Pylatuk wrote:

Hello all.

I am attempting to insert a row into a table with a datetime column:

When the insert statement contains a value for the millisecond portion
of the data time column: ie. {ts '2003-11-05 12:02:43:2960'}

I get 'Syntax error converting datetime from string'

When I insert a value like: {ts '2003-11-05 12:02:43'}
with no millisecond value it succeeds.

The MS datetime column is documented to have an accuracy
of only about .3 seconds anyway, so I suppose your insert
may work if you truncate the value to no more than 3 digits
after the seconds, but the actual value stored will mostly
be only approximately what you had to begin with...

Joe Weinstein at BEA

Any help would be appreciated.
Thanks