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]

Re: Inserting datetime with milliseconds in SQL Server



Dave Pylatuk wrote:
[color=blue]
> 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.[/color]

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[color=blue]
>
> Any help would be appreciated.
> Thanks
>
>[/color]

[David Portas]

Re: Inserting datetime with milliseconds in SQL Server

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]

Re: Inserting datetime with milliseconds in SQL Server

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:[color=blue]
>
> 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[/color]

[Dave Pylatuk]

Re: Inserting datetime with milliseconds in SQL Server

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" <joeNOSPAM@bea. com> wrote in message
news:3FA94807.6 000006@bea.com. ..[color=blue]
>
>
> Dave Pylatuk wrote:
>[color=green]
> > 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.[/color]
>
> 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[color=green]
> >
> > Any help would be appreciated.
> > Thanks
> >
> >[/color]
>[/color]

[Joe Weinstein]

Re: Inserting datetime with milliseconds in SQL Server



Dave Pylatuk wrote:
[color=blue]
> Thanks Joe.
>
> I tried 4 then 3 then 2 then 1 digit after the seconds portion.
> All with no success.
>
> Any other ideas.
> Thanks.[/color]

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
[color=blue]
> "Joe Weinstein" <joeNOSPAM@bea. com> wrote in message
> news:3FA94807.6 000006@bea.com. ..
>[color=green]
>>
>>Dave Pylatuk wrote:
>>
>>[color=darkred]
>>>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.[/color]
>>
>>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
>>[color=darkred]
>>>Any help would be appreciated.
>>>Thanks
>>>
>>>[/color]
>>[/color]
>
>[/color]