473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Inserting datetime with milliseconds in SQL Server

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
Jul 20 '05 #1
5 58095


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


Jul 20 '05 #2
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
--
Jul 20 '05 #3
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

Jul 20 '05 #4
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

Jul 20 '05 #5


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



Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Eric | last post by:
Hi, I have a DateTime in one of my table in sql serveur. The format of the datetime is yyyy-mm-dd. I have an sqladapter in my application connected with this table and when the format of the...
3
by: Iwan Petrow | last post by:
Hi, I have a column of type datetime in a SQL Server database. I set and get this column with Web Services using dataset and send them to a Web App TextBox control. The collation of the database...
5
by: Kevin Yu | last post by:
hi all since the DateTime can't be assign null, the min value is set to 1901 or something, if in a application design, the date field can be null, so in between the UI and the DB, the business...
10
by: ryan.mclean | last post by:
Hi all, I am new to using sql server and parameterized sql. I am hoping to be returned the value of a column that has been inserted. Here is my statement strSqlInsetrtTrack = _ "INSERT INTO...
1
by: Ugur Ekinci | last post by:
Hi , I have two Sql Server 2000 on seperate machines , First one accepts datetime format like ("dd.MM.yyyy hh:mm:ss") And Second one accepts datetime format like ("MM.dd.yyyy hh:mm:ss") 1-...
0
by: hafeez | last post by:
Hi , My requirement is to read and write the data from remote SQL SERVER. Locally i have SQL SERVER and i want read and write the data from remote SQL SERVER. I am able to read the data from...
2
by: xeroxero | last post by:
What is the best way to display (C# 2.0) the date, time, current timezone, daylight savings offset of the web server to the ASP.NET client? Thanks.
4
by: Manikandan | last post by:
Hi, I'm inserting a datetime values into sql server 2000 from c# SQL server table details Table name:date_test columnname datatype No int date_t DateTime ...
3
by: buntyindia | last post by:
Hi, I have a time related issue. Our application servers are hosted in US Central. So when cookie created by the application it contains US Central Time with DST. When we use it in India time...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.