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
I'm running into a constant issue of SQL Server modifying themillisecond part of a timestamp insert from another application. Theapplication inserts timestamp which includes a millisecond portion as astring (varchar). But when an SQL Server moves this data to anothertable (for reporting), the string is inserted in a datetime field, themillisecond field invariably changes by 1-2 milliseconds for everysingle data point inserted. Given the time critical nature of this data(to a millisecond), its almost impossible to avoid this other than toleave the data as string type. But this drives the analytical reportingfolks wild as report queries based on time criteria are getting messedup. Any ideas how to force SQL Server not to mess around with themillisecond value? Does this problem exist with SQL Server 2005 as well?
View 2 Replies View RelatedHi,
I am trying to access a date column up to millisecond precession. So I cast date to as follows:
Code BlockCONVERT(varchar(23),CREATE_DATE,121)
I get millisecond part as a result of query but it€™s €œ000€?.
When I try to test the format by using getDate instead of DateTime column I get right milliseconds.
CONVERT(varchar(23),GetDate(),121) --Gives right milliseconds in return
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example:
set MyUniqueNumber = 20071101190708733
ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following:
1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2.
select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
hi ppls..
we have sql server 2000 EM. we received daily xml files and we insert into our database.there is one column Date_T having data type datetime.till date we recieved this records from xml as '03/23/2004 12:23:34:956' but due to some duplicate isssue we now want to modified this column to recieve as milliseconds like '03/23/2004 12:23:34:956232' now my point is wheather sql server handle this kind of milliseconds..please help me out as early as possible..
T.I.A
Papillon
Hi,
I am trying to access a date column up to millisecond precession. So I cast date to as follows:
Code BlockCONVERT(varchar(23),CREATE_DATE,121)
I get millisecond part as a result of query but it€™s €œ000€?.
Hi,
I'm inserting a datetime values into sql server 2000 from c#
SQL server table details
Table nameate_test
columnname datatype
No int
date_t DateTime
C# coding
SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI");
connectionToDatabase.Open();
DataTable dt1 = new DataTable();
dt1.Columns.Add("no",typeof(System.Int16));
dt1.Columns.Add("date_t", typeof(System.DateTime));
DataRow dr = dt1.NewRow();
dr["no"] = 1;
dr["date_t"] = DateTime.Now;
dt1.Rows.Add(dr);
for(int i=0;i<dt1.Rows.Count;i++)
{
string str=dt1.Rows["no"].ToString();
DateTime dt=(DateTime)dt1.Rows["date_t"];
string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')";
SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase);
cmd.ExecuteNonQuery();
MessageBox.Show("saved");
}
When I run the above code, data is inserted into the table
The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column.
Is there any conversion needed for millisecond values?
thanks,
Mani
Hi,
I tried entering this value "8/24/2006 1:35:00.127 PM" with 127 as the milliseconds in a datetime field, but encountered error saying inconsistent datatype ...
Anyone knows how to store datetime value with milliseconds in the SQL database?
Thanks
I'm trying to save a datetime value from vb.net to a sql server.I'm using this code: Dim dt As DateTime = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss")
After I save this value to Database.But on sql server management studio , I see that the field's value still has the milliseconds .
I want to execute a paramterized ADO insert command into a SQL Server DATETIME column without losing the milliseconds. I can accomplish this without parameters, but that isn't what I want. Any suggestions?
It is OK that DATETIME only has a resolution of 3.33 milliseconds.
See the attached code:
Code Snippet
#include <string>
#include <iostream>
#import "C:Program FilesCommon FilesSystemadomsado15.dll" rename( "EOF", "ADOEOF" )
int main( int argc, char* argv[] )
{
::CoInitialize( NULL );
try
{
ADODB::_ConnectionPtr connection;
connection.CreateInstance(__uuidof( ADODB::Connection ) );
std::string connectionString;
connectionString.append( "Provider=SQLOLEDB;" );
connectionString.append( "Data Source=HPSERV1;" ); // Choose your server/instance.
connectionString.append( "Initial Catalog=tempdb;" );
connectionString.append( "Integrated Security=SSPI;" );
connection->ConnectionTimeout = 10;
connection->Open(
_bstr_t( connectionString.c_str() ),
_bstr_t( "" ),
_bstr_t( "" ),
ADODB::adOpenUnspecified );
std::string sqlStatement;
sqlStatement = "DROP TABLE TestTable1";
try
{
connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );
}
catch( const _com_error& )
{
// Ignore errors as table probably doesn't exist.
}
sqlStatement = "CREATE TABLE TestTable1 ( ColInt INT NOT NULL PRIMARY KEY, ColDate DATETIME )";
connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );
// ====================================================================
// Works (datetime resolution is 3.33 milliseconds so rounds to .347)
sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 1, '2007-12-28 20:05:16.345' )";
connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );
// ====================================================================
// Works (NULL is inserted)
sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 2, NULL )";
connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );
// ====================================================================
// Works (datetime resolution is 3.33 milliseconds so rounds to .347)
sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 3, CONVERT( DATETIME, '2007-12-28 20:05:16.345' ) )";
connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );
// ====================================================================
unsigned int colInt = 3;
sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( ?, ? )";
ADODB::_CommandPtr command;
// ====================================================================
// Fails (Operand type clash: ntext is incompatible with datetime)
colInt++;
command.CreateInstance( __uuidof( ADODB::Command ) );
command->ActiveConnection = connection;
command->CommandType = ADODB::adCmdText;
command->CommandText = _bstr_t( sqlStatement.c_str() );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adInteger,
ADODB::adParamInput,
4,
_variant_t( colInt ) ) );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adBSTR,
ADODB::adParamInput,
-1,
_bstr_t( "2005-10-25 09:10:11.012" ) ) );
try
{
command->Execute( NULL, NULL, ADODB::adCmdText );
}
catch( const _com_error& e )
{
std::cout << "Error at colInt=" << colInt << " (ADODB::adBSTR and _bstr_t):"
<< " HRESULT = " << e.Error() << ": " << e.Description() << ""
<< " SQL statement: " << command->CommandText << std::endl;
}
// ====================================================================
// Fails (Operand type clash: text is incompatible with datetime)
colInt++;
command.CreateInstance( __uuidof( ADODB::Command ) );
command->ActiveConnection = connection;
command->CommandType = ADODB::adCmdText;
command->CommandText = _bstr_t( sqlStatement.c_str() );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adInteger,
ADODB::adParamInput,
4,
_variant_t( colInt ) ) );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adChar,
ADODB::adParamInput,
-1,
_bstr_t( "2005-10-25 09:10:11.012" ) ) );
try
{
command->Execute( NULL, NULL, ADODB::adCmdText );
}
catch( const _com_error& e )
{
std::cout << "Error at colInt=" << colInt << " (ADODB::adChar and _bstr_t):"
<< " HRESULT = " << e.Error() << ": " << e.Description() << ""
<< " SQL statement: " << command->CommandText << std::endl;
}
// ====================================================================
// Fails (A datetime is inserted to table but milliseconds are zeroed)
colInt++;
::SYSTEMTIME sysNow;
::GetSystemTime( &sysNow );
double myTime;
::SystemTimeToVariantTime( &sysNow, &myTime );
// SystemTimeToVariantTime strips milliseconds, so we'll add some more
// back in as we're testing insert of milliseconds.
double desiredMilliseconds = 456;
myTime += desiredMilliseconds / 24.0 / 3600.0 / 1000.0;
command.CreateInstance( __uuidof( ADODB::Command ) );
command->ActiveConnection = connection;
command->CommandType = ADODB::adCmdText;
command->CommandText = _bstr_t( sqlStatement.c_str() );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adInteger,
ADODB::adParamInput,
4,
_variant_t( colInt ) ) );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adDate,
ADODB::adParamInput,
-1,
_variant_t( myTime, VT_DATE ) ) );
try
{
command->Execute( NULL, NULL, ADODB::adCmdText );
}
catch( const _com_error& e )
{
std::cout << "Error at colInt=" << colInt << " (ADODB::adDate and _variant_t VT_DATE):"
<< " HRESULT = " << e.Error() << ": " << e.Description() << ""
<< " SQL statement: " << command->CommandText << std::endl;
}
}
catch ( _com_error& e )
{
std::cout << "Unexpected error: "
<< e.Description() << std::endl;
}
return 0;
}
I'm getting error:
String was not recognized as a valid DateTime.
my insert parameter:
<asp:Parameter Name="LastModified" Type="DateTime" DefaultValue= "<%=DateTime.Now.ToString() %>"
my insert command:
InsertCommand="INSERT INTO [Product] ([Enabled], [ProductCode], [ProductName], [ProductAlias], [CarrierId], [DfltPlanId], [DoubleRating], [DoubleRateProductId], [ConnCharges], [StartDate], [EndDate], [Contracted], [BaseProductId], [LastModified], [LastUser]) VALUES (@Enabled, @ProductCode, @ProductName, @ProductAlias, @CarrierId, @DfltPlanId, @DoubleRating, @DoubleRateProductId, @ConnCharges, @StartDate, @EndDate, @Contracted, @BaseProductId, @LastModified, @LastUser)"
LastModified is a datetime field.
Running sql2005
i use this statement to insert into sql tablecmdInsert = New SqlCommand("insert into empbill ([deptcode],[personNo,[entrydate]] ) values(" & Val(eno.Text) & "," & Val(TextBox5.Text) & ", " & dd.Text & "')", db)
cmdInsert.ExecuteNonQuery()
dd.Text value is 22/5/2008 it come from datetime calender
the entrydate field datatype is datetime
i found data always 1/1/1900 00:00:00
how i enter my date
DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
hey everyone,
Im trying run a stored procedure, but I am getting the following message when trying to execute it.
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
which seems strange, as there is no integer going to a datetime field. Also, it does not return the value that is incorrect, nor the parameter that is failing. If there is anyway to view this in VS 2005 Team suite, please let me know.
It should also be noted that when I execute this sproc from within sql management studio, it executes fine.
This is the stored procedure im trying to execute...
CREATE PROCEDURE [dbo].[sp_UpdateRequest]
@request_id INT,
@request_client_id INT,
@request_description VARCHAR(150), @request_date DATETIME,
@request_edit_date DATETIME, @request_status VARCHAR(25), @request_approve_date DATETIME,
@request_activity_code VARCHAR(7), @request_department_code VARCHAR(3), @request_participants INT,
@request_activity_date DATETIME, @request_activity_due_date DATETIME,
@request_company_id INT, @request_company_code INT,
@request_notes VARCHAR(2000)
AS
BEGIN
UPDATE invoice_requests
SET request_client_id = @request_client_id,request_description = @request_description,
request_date = @request_date, request_edit_date = @request_edit_date,
request_status = @request_status, request_approve_date = @request_approve_date,
request_activity_code = @request_activity_code,
request_department_code = @request_department_code,
request_participants = @request_participants,
request_activity_date = @request_activity_date,
request_activity_due_date = @request_activity_due_date,
request_company_code = @request_company_code, request_company_id = @request_company_id,
request_notes = @request_notes
WHERE request_id = @request_id
END
GO
in my .net app, all the datetime objects are valid dates, so I could not see why this would be generating the error that it is, so I ran the SQL Profiler, and this was the output. Again, i cannot see where im going wrong...
please note, I have seperated the output from the single slab of text, making sure I didnt remove anything...
exec sp_executesql
N'EXECUTE sp_UpdateRequest
@request_id,
@request_client_id,
@request_description,
@request_date,
@request_edit_date,
@request_status,
@request_approve_date,
@request_activity_code,
@request_participants,
@request_activity_date,
@request_activity_due_date,
@request_company_id,
@request_company_code,
@request_notes',
N'@request_id int,
@request_client_id int,
@request_description varchar(8000),
@request_date datetime,
@request_edit_date datetime,
@request_status varchar(5),
@request_approve_date datetime,
@request_activity_code varchar(8000),
@request_department_code varchar(8000),
@request_participants int,
@request_activity_date datetime,
@request_activity_due_date datetime,
@request_company_id int,
@request_company_code int,
@request_notes varchar(8000)',
@request_id=5,
@request_client_id=1,
@request_description='',
@request_date=''2007-11-22 16:34:32:997'',
@request_edit_date=''2007-11-22 16:34:32:997'',
@request_status='Draft',
@request_approve_date=''1970-01-01 00:00:00:000'',
@request_activity_code='',
@request_department_code='',
@request_participants=0,
@request_activity_date=''2007-11-29 00:00:00:000'',
@request_activity_due_date=''2007-11-23 00:00:00:000'',
@request_company_id=0,
@request_company_code=1,
@request_notes=''
any help on this would be greatly appreciated.
Cheers
I keep getting the following error message but I don't see what's wrong with my code
Server Error in '/Admin' Application.
Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.Source Error:
Line 147:cmdSql.Parameters.Add("@event_active","1")
Line 148:
Line 149:cmdSql.ExecuteNonQuery()
Line 150:
Line 151:pnlForm.Visible = FalseSource File: c:hostingwebhost4lifememberNYACOAadmincalendar.aspx Line: 149 Stack Trace:
[SqlException (0x80131904): Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735078
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
ASP.calendar_aspx.SubmitForm(Object s, EventArgs E) in c:hostingwebhost4lifememberNYACOAadmincalendar.aspx:149
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
1 <%@ Register TagPrefix="cc1" Namespace="ManyMonkeys.Web.HtmlEditorLibrary.Controls" Assembly="HtmlEditorLibrary" %>
2 <%@ Page Language="VB" masterpagefile="nyacoaadmin.master" debug="true" %>
3 <%@ mastertype virtualpath="nyacoaadmin.master" %>
4 <%@ import Namespace="System" %>
5 <%@ import Namespace="System.Data" %>
6 <%@ import Namespace="System.Data.SqlClient" %>
7 <script runat="server">
8
9 Dim Connection As SqlConnection
10 Dim Sql As String
11 Dim cmdSql As SqlCommand
12 Dim dtr As SqlDataReader
13
14 Sub Page_Load
15
16 Connection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
17 Connection.Open()
18
19 If Not isPostback then
20
21 Sql = "SELECT chapter_id, chapter_name FROM chapter ORDER BY chapter_id"
22 cmdSql = New SqlCommand (Sql, Connection)
23 dtr = cmdSql.ExecuteReader()
24
25 ddlChapter.DataSource = dtr
26 ddlChapter.DataTextField = "chapter_name"
27 ddlChapter.DataValueField = "chapter_name"
28 ddlChapter.DataBind()
29
30 dtr.Close
31
32 ddlChapter.Items.Insert(0, New ListItem(" ", ""))
33
34 Call ShowList()
35
36 End If
37
38 End Sub
39
40 Sub Page_Unload
41
42 'Connection.Close()
43
44 End Sub
45
46 Sub ShowList()
47
48 Sql = "SELECT event_id, event_name, event_date, chapter_name FROM event WHERE event_active = '1' ORDER BY event_id"
49 cmdSql = New SqlCommand(sql, Connection)
50 dtr = cmdSql.ExecuteReader()
51
52 dgrdList.DataSource = dtr
53 dgrdList.DataBind()
54 dtr.Close()
55
56 pnlList.Visible = True
57
58 End Sub
59
60 Sub dgrdList_ItemCommand(s As Object, E As DataGridCommandEventArgs)
61
62 If e.CommandName = "edit" then
63 Call EditForm(dgrdList.DataKeys(e.Item.ItemIndex))
64 Else If e.CommandName = "delete" then
65 Call ConfirmDelete(dgrdList.DataKeys(e.Item.ItemIndex))
66 End If
67
68 End Sub
69
70 Sub ConfirmDelete(idVal As Integer)
71
72 pnlList.Visible = False
73 pnlConfirmDelete.Visible = True
74
75 sql = "SELECT event_name FROM event WHERE event_id = " & idVal
76 cmdSql = New SqlCommand(sql, connection)
77 litDelete.Text = cmdSql.ExecuteScalar()
78
79 ViewState("idVal") = idVal
80
81 End Sub
82
83 Sub DeleteForm (s As Object, E As Eventargs)
84
85 sql = "UPDATE event SET event_active = '0' WHERE event_id = " & ViewState("idVal")
86 cmdSql = New SqlCommand(sql, connection)
87 cmdSql.ExecuteNonQuery()
88
89 pnlList.Visible = True
90 pnlConfirmDelete.Visible = False
91
92 Call ShowList()
93
94 End Sub
95
96 Sub ShowCalendar(s As Object, E As ImageClickEventargs)
97
98 pnlEventDate.Visible = True
99
100 End Sub
101
102 Sub UpdateDate (s As Object, E As Eventargs)
103
104 pnlEventDate.Visible = False
105 txtEventDate.text=eventCalendar.SelectedDate.ToShortDateString()
106
107 End Sub
108
109 Sub SubmitForm (s As Object, E As Eventargs)
110
111 If ViewState("submitType") = "edit" then
112 sql = "UPDATE event SET event_name=@event_name, event_date=@event_date, chapter_name=@chapter_name, event_description=@event_description, event_last_updated_by=@event_last_updated_by, event_active=@event_active WHERE event_id = " & ViewState("idVal")
113 Else
114 sql = "INSERT INTO event (event_name, event_date, chapter_name, event_description, event_added_by, event_last_updated_by, event_active) VALUES (@event_name, @event_date, @chapter_name, @event_description, @event_added_by, @event_last_updated_by, @event_active)"
115 End If
116
117 cmdSql = New SqlCommand(sql, Connection)
118
119 If Len(txtEventName.Text) = 0 then
120 cmdSql.Parameters.Add("@event_name",DbNull.Value)
121 Else
122 cmdSql.Parameters.Add("@event_name",txtEventName.Text)
123 End If
124
125 If Len(txtEventDate.Text) = 0 then
126 cmdSql.Parameters.Add("@event_date",DbNull.Value)
127 Else
128 cmdSql.Parameters.Add("@event_date",txtEventDate.Text & " " & ddlEventHour.SelectedValue & ":" & ddlEventMinute.SelectedValue & " " & ddlEventAMPM.SelectedValue)
129 End If
130
131 If Len(ddlChapter.SelectedValue) = 0 then
132 cmdSql.Parameters.Add("@chapter_name",DbNull.Value)
133 Else
134 cmdSql.Parameters.Add("@chapter_name",ddlChapter.SelectedValue)
135 End If
136
137 If Len(txtEventDescription.Text) = 0 then
138 cmdSql.Parameters.Add("@event_description",DbNull.Value)
139 Else
140 cmdSql.Parameters.Add("@event_description",txtEventDescription.Text)
141 End If
142
143 cmdSql.Parameters.Add("@event_added_by",session("admin_username"))
144
145 cmdSql.Parameters.Add("@event_last_updated_by",session("admin_username"))
146
147 cmdSql.Parameters.Add("@event_active","1")
148
149 cmdSql.ExecuteNonQuery()
150
151 pnlForm.Visible = False
152 pnlList.Visible = True
153
154 Call ShowList()
155
156 End Sub
157
158 Sub AddForm(s As Object, E As Eventargs)
159
160 pnlForm.Visible = True
161 pnlList.Visible = False
162
163 txtEventName.Text = ""
164 txtEventDate.Text = ""
165 ddlChapter.ClearSelection()
166 txtEventDescription.Text = ""
167
168 ViewState("submitType") = "add"
169
170 End Sub
171
172 Sub EditForm (idVal As Integer)
173
174 pnlList.Visible = False
175 pnlForm.Visible = True
176
177 sql = "SELECT * FROM event WHERE event_id = " & idVal
178 cmdSql = New SqlCommand(sql, connection)
179 dtr = cmdSql.ExecuteReader()
180
181 If dtr.Read() then
182
183 txtEventName.Text = dtr("event_name") & ""
184 txtEventDate.Text = dtr("event_date") & ""
185 txtEventDescription.Text = dtr("event_description") & ""
186 ddlChapter.ClearSelection()
187 Try
188 ddlChapter.Items.FindByValue(dtr("chapter_name")).Selected = True
189 Catch Ex As Exception
190 End Try
191
192 End If
193
194 dtr.Close()
195
196 ViewState("submitType") = "edit"
197 ViewState("idVal") = idVal
198
199 End Sub
200
201 Sub DeleteCancel (s As Object, E As Eventargs)
202
203 pnlConfirmDelete.Visible = False
204 pnlList.Visible = True
205
206 End Sub
207
208 </script>
209
210 <asp:Content contentplaceholderid="cplhMainContent" runat="server">
211
212 <asp:Panel runat="server" id="pnlList" Visible="False">
213
214 <asp:DataGrid runat="server" id="dgrdList" AutoGenerateColumns="False" CellPadding="3" CellSpacing="0" width="550" Border="0" DataKeyField="event_id" onItemCommand="dgrdList_ItemCommand">
215 <HeaderStyle BackColor="#CDAD00" Font-Names="Tahoma,Arial,Helvetica,sans-serif" Font-Bold="true" Font-Size="10pt" HorizontalAlign="center" ForeColor="white" />
216 <ItemStyle BackColor="white" Font-Names="Tahoma,Arial,Helvetica,sans-serif" Font-Size="10pt" ForeColor="black" VerticalAlign="top" />
217 <AlternatingItemStyle BackColor="#dcdcdc" Font-Names="Tahoma,Arial,Helvetica,sans-serif" Font-Size="10pt" ForeColor="black" VerticalAlign="top" />
218 <Columns>
219 <asp:ButtonColumn Text="Edit" ButtonType="PushButton" CommandName="edit" />
220 <asp:ButtonColumn Text="Delete" ButtonType="PushButton" CommandName="delete" />
221 <asp:BoundColumn DataField="event_name" HeaderText="Event Name" />
222 <asp:BoundColumn DataField="event_date" HeaderText="Date" />
223 <asp:BoundColumn DataField="chapter_name" HeaderText="Sponsoring Chapter" />
224 </Columns>
225 </asp:DataGrid>
226
227 <asp:Button runat="server" Text="Add New Event" onClick="AddForm"></asp:Button>
228
229 </asp:Panel>
230
231 <asp:Panel runat="server" id="pnlForm" Visible="False">
232
233 Name of Event: <asp:TextBox id="txtEventName" runat="server"></asp:TextBox>
234 <br />Chapter: <asp:DropDownList id="ddlChapter" runat="server" AutoPostBack="False" />
235 <br />Date: <asp:TextBox id="txtEventDate" runat="server" ReadOnly="true" BackColor="Gray"></asp:TextBox> <asp:ImageButton id="CalButton" runat="server" OnClick="ShowCalendar" ImageUrl="images/cal.gif" />
236 <br /><asp:Panel runat="server" id="pnlEventDate" Visible="False">
237 <asp:Calendar id="eventCalendar" runat="server" FirstDayOfWeek="Sunday" Font-Names="Tahoma" Font-Size="8pt" OnSelectionChanged="UpdateDate">
238 <SelectedDayStyle BackColor="#EC9900" Font-Bold="True" />
239 <TodayDayStyle BackColor="#FFC0C0" />
240 <SelectorStyle Font-Names="Tahoma" Font-Size="8pt" />
241 <DayStyle Font-Names="Tahoma" Font-Size="8pt" />
242 <WeekendDayStyle BackColor="#E0E0E0" />
243 <OtherMonthDayStyle ForeColor="Silver" />
244 <DayHeaderStyle BackColor="#334B79" Font-Names="Tahoma" Font-Size="8pt" ForeColor="White" />
245 <TitleStyle BackColor="#CCCCFF" Font-Bold="True" Font-Names="Tahoma" Font-Size="10pt" />
246 </asp:Calendar>
247 <br />
248 </asp:Panel>
249 Time: <asp:DropDownList ID="ddlEventHour" runat="server" >
250 <asp:ListItem Text="12" Value="12" Selected="true"/>
251 <asp:ListItem Text="1" Value="1" />
252 <asp:ListItem Text="2" Value="2" />
253 <asp:ListItem Text="3" Value="3" />
254 <asp:ListItem Text="4" Value="4" />
255 <asp:ListItem Text="5" Value="5" />
256 <asp:ListItem Text="6" Value="6" />
257 <asp:ListItem Text="7" Value="7" />
258 <asp:ListItem Text="8" Value="8" />
259 <asp:ListItem Text="9" Value="9" />
260 <asp:ListItem Text="10" Value="10" />
261 <asp:ListItem Text="11" Value="11" />
262 </asp:DropDownList>
263 :
264 <asp:DropDownList ID="ddlEventMinute" runat="server">
265 <asp:ListItem Text="00" Value="00" Selected="true" />
266 <asp:ListItem Text="15" Value="15" />
267 <asp:ListItem Text="30" Value="30" />
268 <asp:ListItem Text="45" Value="45" />
269 </asp:DropDownList>
270 <asp:DropDownList ID="ddlEventAMPM" runat="server">
271 <asp:ListItem Text="AM" Value="AM" />
272 <asp:ListItem Text="PM" Value="PM" Selected="true" />
273 </asp:DropDownList>
274 <br /><br />
275 Content:
276 <br /><cc1:HtmlEditor id="txtEventDescription" runat="server" Height="300px" Width="575px"></cc1:HtmlEditor>
277 <br /><br />
278 <asp:Button onclick="SubmitForm" runat="server" Text="Submit"></asp:Button>
279
280 </asp:Panel>
281
282 <asp:Panel runat="server" id="pnlConfirmDelete" Visible="False">
283
284 Are you sure you wish to delete <asp:Literal runat="server" id="litDelete"></asp:Literal>?
285 <br /><br />
286 <asp:Button runat="server" Text="Yes - DELETE" onClick="DeleteForm"></asp:Button> <asp:Button runat="server" Text="No - GO BACK" onClick="DeleteCancel"></asp:Button>
287
288 </asp:Panel>
289
290 </asp:Content>
hi there, i have a calendar that i put into a string ilke this
string str = Calendar1.SelectedDate.ToShortDateString();
the result is dd/mm/yyyy date which is great, but for inserting into my DB (MSSQL) it needs to be a datetime field, however when i convert it
Datetime dtDate = Convert.ToDateTime(str);
it takes my date and adds 00:00:00 onto the end and this is not what i want! i just want the dd/mm/yyy how do i do this, it has to be simple but i have been searching for hours and cant find anything, i am using ASP.NET 2 and C#
Thanks
I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand.
The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value�), so I don’t know how I can insert this value when the field is blank?
Can anyone shed some light please? Thanks
Hi,
I am working with "sql server enterprise manager".
How can I insert datetime(i.e. date) value in the datetime column in my database using an sql command?
Eg.
If I have a table named Table1 in my database, and this table is composed of one primary key column and one datetime column (named dateofreceipt), how should the syntax look like?
Thank you
Vuchko
HiI am trying to insert value retrieved from Now() into a datetime field in my MSDE database, but I am getting the following error, and I have no idea what is going wrong.Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.Here is the code I am using:Dim user As String = MyContext.User.Identity.Name.ToString
Dim TimeDate As DateTime = Now()
Dim status As String = "Pending"
With SqlOrders.InsertParameters
.Item("UserName").DefaultValue = user
.Item("OrderDate").DefaultValue = TimeDate
.Item("Status").DefaultValue = status
End With
SqlOrders.Insert()The date is being returned in this format 23/03/2006 02:01:52, which is the same format as it should appear in the database. could anyone please tell me where I am going wrong?
Hi,
I am using SQL 2k5
I have a table with the follwing schema
Table BulkTest
(
ID nvarchar(100),
SubDate Datetime
)
And i have data in a file as follows ( column delimeter is | and row deleimeter is new line character ( windows ) )
4000000|10/25/2007 6:07:32 AM
4000001|10/25/2007 6:07:32 AM
4000002|10/25/2007 6:07:32 AM
4000003|10/25/2007 6:07:32 AM
4000004|10/25/2007 6:07:32 AM
BULK INSERT dbo.BulkTest FROM 'c:\insert.dat' WITH (FIELDTERMINATOR = '|',ROWTERMINATOR='
')
When i execute this statement its failing with the follwing message.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (SubDate ).
Can any one please let me know whats wrong in this and what should be done to make it work
Quick response will be appreciated
Thanks much
~Mohan
Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer.
That's my problem:
if the server is in english version, I have to insert date with this code:
DateTime.Today.ToString("MM/dd/yyyy")
instead if the server is in italian version, I have to insert date with this code:
DateTime.Today.ToString("dd/MM/yyyy")
Is there a way to insert a date in standard way, without knowing the server version?
bye and thanks in advance
Using Server Explorer in VB 2005, I am manually entering data in a table in a SQL Server 2005 Express database that includes a DateTime field. I have tried every conceivable format, but no matter what I try get one of these 2 errors:
1. String was not recognized as valid DateTime
2. Operand type class; text incompatible with DateTime
I have Googled this to death, but no example which involves trying to enter the data manually, say from Server Explorer.
Formats tried include all datetime formats (mmddyy, yymmdd, using dashes or slashes, enclosing in single quotes or pound signs).
I would appreciate if someone could please give me an example that I can literally insert without error.
Hi all,I am getting this error when insert values from one table to another inthe first table the values are varchar (10). In the second they aredatetime. The format of the data is mm/dd/yyyy to be easily convertedto dates. The conversion in this case is implicit as indicated in SQLServer documentation. Here is my query:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionUNIONSELECT program_id + 100000, program_name, start_date, end_date,campaign_id AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.programsUNIONSELECT execution_id + 200000, execution_name, start_date,end_date, program_id + 100000 AS ParentID, NULL AS ListIDFROM mysqlfactiva.dbo.executionsUNIONSELECT wave_id + 300000, wave_name, start_date, end_date,mysqlfactiva.dbo.waves.execution_id + 200000 AS ParentID, NULL ASListIDFROM mysqlfactiva.dbo.waves, mysqlfactiva.dbo.executionsWHERE mysqlfactiva.dbo.waves.execution_id =mysqlfactiva.dbo.executions.execution_idI am referencing programs table two times. If I just select this all Iget all data I need. When doing insert I get a message:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value. The statement has been terminated.If I execute just first part of the query before first union, I insertdata fine:INSERT INTO Campaign (CampaignID, Name, DateStart, DateEnd, ParentID,ListID)SELECT mysqlfactiva.dbo.campaigns.campaign_id AS CampaignID,mysqlfactiva.dbo.campaigns.campaign_name AS Name,MIN(mysqlfactiva.dbo.programs.start_date) AS DateStart,MIN(mysqlfactiva.dbo.programs.end_date) AS DateEnd,NULL AS ParentID,NULL AS ListIDFROM mysqlfactiva.dbo.campaigns, mysqlfactiva.dbo.programsWHERE mysqlfactiva.dbo.campaigns.campaign_id =mysqlfactiva.dbo.programs.campaign_idGROUP BY mysqlfactiva.dbo.campaigns.campaign_id,mysqlfactiva.dbo.campaigns.campaign_name,mysqlfactiva.dbo.campaigns.descriptionAs soon as I use union I get the above error. This is very strangesince even when I execute the query using first union where the datescome from the same table 'programs' I get the error. Why I can insertfrom programs first time and can's second time?Any help will be appreciated.Thanks,Stan
View 1 Replies View RelatedHi, Assume I have a table name "myTime". This table is simply only have 1 (one) DATETIME field "MyTestTime" (also serve as a primary number).Table MyTime- MyTestTime : SQLTYPE DATETIMETo insert a new row into this field, I simply wrote :SqlCommand sqlCommand = new SqlCommand("insert into MyTime values('2006-01-09')", sqlConnection);
I got the value of "2006-01-09" from a textbox or other relevan control.I realize when I try to use "SELECT * FROM MyTime" statement, MSSQL server 2000 automatically convert my date value from "2006-01-09" to "01/09/2006" (from YYYY-MM-DD to MM/DD/YYYY). I don't know why this one must be converted to MM/DD/YYYY automatically (I believe this behavior is depend on some "setting option" in my MSSQL server - but I don't know which one).The challenge is :In my country, the actual date format is like German Date format (DD-MM-YYY). Well I know this is only "Customization" problem. But how insert datetime value given from sql query to a datetime variable?// Connect to database, make a query, get the datareader result, and bla bla blaDateTime aDateTime = new DateTime;aDateTime = Convert.ToDateTime(myDataReader["PostDate"].ToString());// close connectionMy question isHow can I make sure that aDateTime's day is 09 not 01. How my program know that 09 is day not month. I can't use string.split() method because it's possible that my database setting will change from "mm-dd-yyyy" to "dd-mm-yyyy"thanks
Hi
Does anybody know while loading data from text file into sql server, how
can we ignore milliseconds.
regards
JK
I have got data like this below in effective date column
2008-08-05 19:18:13.000
2008-08-05 19:17:10.000
Expected output:
2008-08-05 19:17:10
I need to truncate the milliseconds and insert the same into datetime column.
I tried as below
select convert(datetime,substring(convert(varchar,getdate(),20),1,20))
Thanks in advance
Hi,
i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query
SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1
However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.
Thanks in advance.
Hi,
i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query
Code:
SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1
However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.
Thanks in advance.
why the results I receiving for Seconds is different? I get the same MS results.
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
--Returns 1 second
SELECT RunTime_SEC = DATEDIFF(SECOND, '2015-02-09 13:34:13.977', '2015-02-09 13:34:14.230')
,RunTime_MS = DATEDIFF(ms, '2015-02-09 13:34:13.977', '2015-02-09 13:34:14.230')
--Returns 0 second
SELECT RunTime_SEC = DATEDIFF(SECOND, '2015-02-09 13:30:30.147', '2015-02-09 13:30:30.400')
,RunTime_MS = DATEDIFF(ms, '2015-02-09 13:30:30.147', '2015-02-09 13:30:30.400')
Hi!
I have the difference between two dates in milliseconds. I want to convert this difference to the format hh:mm:ss.mmm, without the date. So, if the difference is bigger then one day, I would like to show it like this, for example: 36:25:14.047
How can I do this?
Thank you!
I've been working on a project to ensure that accross our entire data warehouse everything is at the same accuracy level as far as time - migrating everything to use the full hh:mis.mmm. Some places were using hh:mis:mmm (colon instead of decimal point) and many places not using milliseconds.
The SQL server portion went essentially without issue - however SSIS is not cooperating. For example I have data I am importing from a file that is in format: hh:mis (no milliseconds) that I need to compare to data from SQL (now containing full milliseconds) - matching on time ranges. Previously this was done by converting both to the "database time" datatype in SSIS and comparing. Now when converting the cTime to database time SSIS fails and complains "The value could not be converted because of a potential loss of data".
I don't want to lop off the milliseconds becuase that could create rounding errors.
I don't like it but the only option I can think of is keeping everything in string and comparing the strings... then I run into the issue of making sure to convert all sources of data into the exact same hh:mis:mmm format in text.
Is there an easier way? I know some people use "milliseconds since midnight" and so forth but that would require major reworking of the data warehouse and many packages.
why does select @@lock_timeout return -1. Shouldn't this return lock timeout in milliseconds?
View 2 Replies View RelatedI am writing a report that Queries a SQL DB using 'SQL Server Business Intelligence Development Studio'. I have a field in the DB called duration and it is in milliseconds. I am trying to find an easy way to convert the format from Milliseconds to HH:MM:SS.Nearest I can get is the following for the field:
=Int( ((Fields! DURATION.Value/1000) / 60) / 60) & ":" & Int(((((Fields!DURATION.Value/1000) / 60) / 60) - Int(((Fields!DURATION.Value/1000) / 60) / 60)) * 60)
The output is in HH:MM. One issue with this is if the MM is say :03, it prints as :3. I lose the leading 0 so 9:03 (9hrs and 3 minutes) prints as 9:3. Where as 9:30 (9 hrs and 30 minutes) prints as 9:30 as it should.