473,854 Members | 1,823 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 58241


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
1647
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 date is not yyyy-mm-dd (on the local computer where my application is running), I have some problem with the insert/upgrade. My question is: How can I change the value in the sqldataadapter just before the update? (because all I display is the hour...
3
2478
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 is SQL_Latin1_General_CP1_CI_AS (if it necessary I could change it). I didn't change options in Web service and Web app (web_config). The date is shown in this format: "mm/dd/yyyy hh:mm:ss AM/PM". I would like the format to be "dd/mm/yyyy...
5
2057
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 logic has to take care of the conversion?? e.g when inserting DateTime.minvalue into db, (especially when using store precedure, it seems that pass null parameter to a store precedure cause problem.), the store procedure must translate the minvalue...
10
1899
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 TRACK (CASE_NUM, CERT_NUM, CLMT_NUM, BROKER_NAME, " + _ "CONTRACT_SEQ, TRACK_COMMENTS, DATE_OVER_SPEC, LAST_TOUCHED) " + _ "VALUES ('" + strCase + "','" + strCert + "'," + _ "'" + strClmt + "',@BrokerName,'" + strContractSeq + "',@TrackComments," +...
1
2883
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- Date formats are different because of SQL Collation? 2- Do regional Settings affect Sql date format? 3- (Important) When inserting a datetime into first server there is no problem (15.12.2000 12:12:12) , but when I insert into second server (if...
0
2167
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 remote SQL SERVER but i am getting the following error while inserting. I created one linked server in my machine and mentioned the remote server name as data source. The query is :
2
1122
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
5603
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 C# coding
3
1753
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 comparison logic fails as milliseconds are different. In US central logic of comparison current datetime milliseconds with cookie date time milliseconds work gr8. Please tell me any way so that It work in India too. Thanks
0
9901
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11031
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10763
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10371
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7918
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7082
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5750
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5942
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4162
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.