DateTime From Database Entry
Hi, I know this is probably very simple but I am pretty new to this and have tried looking but cant seem to get the search criteria right.
I have a database with a storeDate field which is of shortdatetime type. I am connecting to the database (MSSQL) via a stored procedure and returning all the records. I then use the code
foreach (DataRow dr in ds.Tables[0].Rows)
{
DateTime dtTo = DateTime.Now;
DateTime dtFrom = DateTime.Parse(dr["storeDate"].ToString());
TimeSpan diff = dtTo.Subtract(dtFrom);
}
I am basically trying to find out the age of the database entry by subtracting it from the current DateTime so i can delete records over a certain age. The problem (at least one of them!) is retrieving the "storeDate" object from the database and storing it in the dtFrom object. I have tried just assigning it directly as dtFrom = dr["storeDate"] and various other methods but I just don't know enough to assign it! Can anyone help me with this or spot any other mistakes in this process of removing old files automatically.
Greatly Appreciated,Sean.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Data Entry In A Datetime Field
Hi, I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message - "Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change." Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message. How do I input data into the field?
View Replies !
View Related
Data Entry In A Datetime Field.
My other thread containg this same topic seems to have some error. It doesn't show up in the main 'SQL Server Database Engine' group at all. So I had to start a new thread instead. Here's the link to the original - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1136124&SiteID=1 Here are the contents of the same - Post#1 Orginally posted by me - Hi, I am using SQL Server Management Studio Express for creating my database. I also use the GUI tools for data entry instead of using T-SQL. Whenever I try to enter a value in a field that has a smalldatetime data type, it gives me an error message - "Invalid Value for cell (row 1, column 2). The changed value in this cell was not recognised as valid. .Net Framework Datatype: Datetime Error Message: Index was outside the bounds of the array. Type a value appropriate for this data type or press ESC to cancel the change." Now I have tried entering all different combinations in which one can enter a date or a time or both, including in the format I have specified in the windows regional settings, but I always get the same error message. How do I input data into the field? Post#2 Originally posted by Jens K. Suessmeyer - Hi, you are using a format SQL Serve ri snot expecting. Which User language did you configured for the accessing user and which date are you trying to insert ? HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Post#3 Originally posted by me - Hi, I use only English as the default language for all programs, OS included. The date in question is not just one particular date, it is any date or time value, and I've been facing this issue ever since I started using SQL Server Express a year ago. Only I haven't used it much since, and need to seriously develop something now, that I bothered to ask the question here. Some examples of the values I tried entering yesterday, and none of them worked. (I got the same error message for each value) - 1-1-2007 01-01-2007 01/01/2007 01,01,2007 01:01:2007 Jan 01, 2007 (this is the format I have specified for short date in the Win regional settings - MMM dd, yyyy) I even tried entering time alongside with all those above figures in the format - 11:35 PM 11:35 AM 23:35 The only time value(s) I didn't try was/were - hh:mms tt - which is the time format specified in my Win regional settings, neither standalone, nor with the date values. I also tried entering a time value standalone, without the date. Still no go. (Also, as I have gleaned from previous posts, if all goes well and SQL Server accepts your data, then if you enter just a date value in a datetime field, then the field automatically gets populated with the value of the system time at that moment, and if you enter just a standalone time value, then the date part will be filled by the system date value on the date of the entry. Am I correct so far?) Now I'm in a real fix because of the inability to enter date/time values. Could you please help me out? Post#6 Originally posted by me - I don't understand. I can enter the datetime values in all the above mentioned combinations if I use an Insert or Update statement using T-SQL, but I can't seem to enter data in the datetime/smalldatetime fields at all if I try to use the GUI of Management Studio Express to enter data. (right click the table, select show table, and the grid view pops up). Ditto for using GUI tools of Visual Studio Express. Is this a bug in the Visual Studio (Express) software? Also, my other concerns are, once I develop a front-end for data entry using VB, will I face the same issues with data entry in the datetime fields using the GUI of the front end? I can't test that right now, because I'm still learning VB, and cannot create the front end just yet. Post#7 Originally posted by me this afternoon, but it never showed up on the page - Alright, I have done a complete reinstall of Win XP, and have also reinstalled SQL Server Express. Prior to this, I had SQLExpress SP1, but now I have directly installed SP2. Now I continue to face the same issue. SQL Server will not accept any value for any datetime field, unless it is entered as an SQL insert statement. It won't accept the value entered in the exact same format as the insert statement from the GUI mode of Management Studio Express, nor will it take any values from any GUI developed using VBExpress and the fill dataset method. It throws an error everytime. I don't know any other way of databinding and updating/inserting using VB at this time. I am at my wits' end because of this. I ask again, is this a bug in SQL Server Express? If so how do I report it to MS and get my issue resolved? If not, even then how can I solve this problem? Could someone please help me out? I am stuck and cannot use the product at all, without the datetime field.
View Replies !
View Related
Find The Earliest Datetime Entry & Then Update
Finding the earliest datetime entry, and then updating the database on these reults. I need to query a table of data that has multiple datetime entries in it relating to individual customer records. So one customer could have 1 entry, or it could have 10 entries. I need to be able to identify the earliest of these records, and then on this earliest record update a field value. Example: My Fields in the table are as follows. Log_ID, Cust_Ref_No, ActionDateTime, Action_Code The Log_ID is the primary key, and I need to update the Action_Code field on only the earliest entry against a customer record i.e. 12345,ABCDEF,01/01/2007 00:00:01.000,6 12346,ABCDEF,01/01/2007 09:00:00.000,6 12347,ABCDEF,01/01/2007 17:00:00.000,2 In the above I need to change the first record Action_Code from a 6 to a 1, but leave all other records unaffected. All help greatly appreciated, Thanks : o )
View Replies !
View Related
Datetime Entry For Querying Analysis Service Cube
Hi everybody, I have two problems while using a analysis service cube as data source for a reporting service report. 1.) I've an individual time dimension which has day entries in the standard date format "mm/dd/yyyy". When using an parametric entry for the date hierachy the reporting offers me all entries as a list (some 1000 entries). Looking under report parameters I recognized that the input parameter is listed as of the type string. However I know that the underlying field and as well the hierachy in the cube is of the format datetime. Change it to datetime causes the reporting service to fail with the error message: An error occured during local report processing. The property 'ValidValues' of report parameter 'DIM...' doesn't have the expected type. How can I use the parameter in the format datetime to restrict the time dimension? ...so that I can select the date over the calendar function. 2.) I have another dimension with the hierachy cycle which has the string format "year-month". I would like to use the selection of the date hierachy to create the restriction on the cycle hierachy. I.e. entering '01/16/2007' on the time dimension should write the value '2007-01' to a parameter which is then used to restrict the cycle hierachy. Experimenting with report parameters always caused the error message: An error occured during local report processing. An error has occured during report processing. Query execution failed for data set 'DIM...'. Query (1,453) The restriction by the CONSTRAINED-flag in the STRTOSET-function has been violated. As I only allow single value entries I thought about changing the STRTOSET command in the underlying MDX query into STRTOMEMBER. However this didn't solve the problem. How can I create an input for a restriction on a dimension based on a parameter with a self constructed string? Thanks, StSt
View Replies !
View Related
Identical Database Entry Already?
Here's some code that says it should identify if a user already exists in my database. I have changed the code to match my database, but it seems to have somewhat the opposite affect, rejecting all names (even new ones) or accepting all names (including existing ones). The switch in situations occurs in the "if" statement towardsd the end, when I change the sign of objDR.RecordsAffected. Do you have any idea what could be wrong? Thanks. Function DoesUserExist(ByVal userName As String) As Boolean Dim connectionString As String = "server='(local)Netsdk'; trusted_connection=true; Database='AuthorizedUsers'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [Users].[UserName] FROM [Users] WHERE ([Users].[UserName] = @UserName)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) Dim Cmd as New SQLCommand(queryString, sqlConnection) With Cmd.Parameters .Add(New SQLParameter("@username", username)) End With sqlConnection.Open Dim blHasRows As Boolean Dim objDR As System.Data.SqlClient.SqlDataReader = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection) if objDR.RecordsAffected > 0 then blHasRows="True" else blHasRows="False" End If Return blHasRows End Function
View Replies !
View Related
Null Entry In The Database
I am using a script component to retrieve some values from a server. This script component using Script task which uses VB.NET code. I have tested the VB.NET code and it returns a non null value for a string field 'sentence'. The 'sentence' feild is then passed on to an OLE DB Command transformation to be stored into the database along with a couple of other feilds. The OLE DB Command transformation uses a stored procedure to store these feilds into the database. But when I check my table in the database, all other feilds are stored properly other than the 'sentence' feild which is null. I am wondering what could be causingthis problem. Thanks
View Replies !
View Related
DateTime Unable To Save In Datetime Field Of SQL Database
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
View Replies !
View Related
NORTHWIND Database Was Re-created From A Different Database:How Can I Change The Entry In Sysdatabases For Database 'NORTHWIND'?
Hi all, From the http://msdn.microsoft.com/en-us/library/bb384469.aspx (Walkthrough: Creating Stored Procedures for the Northwind Customers Table, I copied the following sql code: --UpdateSPforNWcustomersTable.sql-- USE NORTHWIND GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'SelectCustomers' AND user_name(uid) = 'dbo') DROP PROCEDURE dbo.[SelectCustomers] GO CREATE PROCEDURE dbo.[SelectCustomers] AS SET NOCOUNT ON; SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM dbo.Customers GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'InsertCustomers' AND user_name(uid) = 'dbo') DROP PROCEDURE dbo.InsertCustomers GO CREATE PROCEDURE dbo.InsertCustomers ( @CustomerID nchar(5), @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24) ) AS SET NOCOUNT OFF; INSERT INTO [dbo].[Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID) GO IF EXISTS (SELECT * FROM sysobjects WHERE name = 'UpdateCustomers' AND user_name(uid) = 'dbo') DROP PROCEDURE dbo.UpdateCustomers GO CREATE PROCEDURE dbo.UpdateCustomers ( @CustomerID nchar(5), @CompanyName nvarchar(40), @ContactName nvarchar(30), @ContactTitle nvarchar(30), @Address nvarchar(60), @City nvarchar(15), @Region nvarchar(15), @PostalCode nvarchar(10), @Country nvarchar(15), @Phone nvarchar(24), @Fax nvarchar(24), @Original_CustomerID nchar(5) ) AS SET NOCOUNT OFF; UPDATE [dbo].[Customers] SET [CustomerID] = @CustomerID, [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE (([CustomerID] = @Original_CustomerID)); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID) GO ==================================================================================== I executed the above code in my SQL Server Management Studio Express (SSMSE) and I got the following error messages: Msg 911, Level 16, State 1, Line 1 Could not locate entry in sysdatabases for database 'NORTHWIND'. No entry found with that name. Make sure that the name is entered correctly. =============================================================================================================== I know I recreated the NORTHWIND Database from a different Database before and I did not do anything for the entry in sysdatabases. How can I change the entry in sysdatabases for database 'NORTHWIND' now? Please help and advise. Thanks in advance, Scott Chang
View Replies !
View Related
Adding Datetime To Database?
I have DateCreated with datetime datatype in my SQL Express 2005. I'd like to add the record to my Task table so I have a form in my ASPX and create a button event in my ASPX.CS here is the code protected void Button_AddTask_Click(object sender, EventArgs e) { SqlDataSource newTask = new SqlDataSource(); newTask.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString(); newTask.InsertCommand = "INSERT INTO [Task] ([MemberAccountName], [Title], [Place], [TaskDetail],[DateCreated]) VALUES (@MemberAccountName, @Title, @Place, @TaskDetail,@DateCreated)"; newTask.InsertParameters.Add("MemberAccountName", User.Identity.Name); newTask.InsertParameters.Add("Title", TextBox_Title.Text); newTask.InsertParameters.Add("Place", TextBox_Place.Text); newTask.InsertParameters.Add("TaskDetail", TextBox_Detail.Text); newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString()); newTask.Insert(); Response.Redirect("Default.aspx"); }but I got this errorArithmetic overflow error converting expression to data type datetime.The statement has been terminated.Any idea?
View Replies !
View Related
Update Datetime Into Database
I have Log table for employees columns : EmployeeID - int In - DateTime Out - DateTime When employee come ,he press button and program insert new row ( EmployeeID and In ) when he go out he press button and my problem begin : How to UPDATE exactly that row with that EmployeeID and lastest day when he came ?
View Replies !
View Related
Problem When Insert DateTime To DataBase
the column type of stime and etime in Dabase both are datetime -------------------------------------------------------------------------------------------------- public DateTime loginTime { get { //got user enter page's time return DateTime.Now; } } //record user's using time when user click exit button protected void Button1_Click(object sender, EventArgs e) { int Member_No = Int32.Parse(cls_login2.GetCurrent_login_Member_no(this.Page)); int Lesson_No = Int32.Parse(Lesson_no); int Prestep = 1; string RemoteIp = Request.ServerVariables["REMOTE_ADDR"].ToString(); string sqlConn = "Insert into pro_Study_log (Member_no,Lesson_no,Study_log_stepid,Study_log_stime,Study_log_etime,Study_log_ip) "; sqlConn += "values(" + Member_No + "," + Lesson_No + "," + Prestep + "," + loginTime + ", getdate(), '" + RemoteIp + "')"; SqlConnection connLog = new SqlConnection(StrConn); SqlCommand cmdLog = new SqlCommand(sqlConn, connLog); cmdLog.CommandType = CommandType.Text; connLog.Open(); cmdLog.ExecuteNonQuery(); connLog.Close(); ----------------------- and then I got error message on column Study_log_stime I also trid set the dateimt tostring , still doesn't work.. I don't know where I did wrong... pleae help many thanks
View Replies !
View Related
DateTime Null In Sql Server Database
Hi, I'm using this source code in order to set the DateTime field of my Sql Server database to null.I am retreiving dates from an excel sheet. If no date is found, then I set my variable myDate to DateTime.MinValue then i test it just before feeding my database. I have an error saying that 'object' does not contain definition for 'Value'. In french : Message d'erreur du compilateur: CS0117: 'object' ne contient pas de définition pour 'Value'dbCommand.Parameters["@DateRDV"].Value = System.Data.SqlTypes.SqlDateTime.Null; The funny thing is that in the class browser i can see the Value property for the class Object... C#, asp.netstring sqlStmt ; string conString ; SqlConnection cn =null; SqlCommand cmd =null; SqlDateTime sqldatenull ; try{sqlStmt = "insert into Emp (Date) Values (@Date) ";conString = "server=localhost;database=Northwind;uid=sa;pwd=;";cn = new SqlConnection(conString);cmd = new SqlCommand(sqlStmt, cn); cmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime));sqldatenull = System.Data.SqlTypes.SqlDateTime.Null; if (myDate == DateTime.MinValue) {cmd.Parameters ["@Date"].Value =sqldatenull ;} else{cmd.Parameters["@Date"].Value = myDate;}cn.Open();cmd.ExecuteNonQuery();Label1.Text = "Record Inserted Succesfully";}catch (Exception ex){Label1.Text = ex.Message;}finally{cn.Close();}
View Replies !
View Related
Inserting Datetime In Microsoft SQL Database
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
View Replies !
View Related
Get Latest Values From Database With Max Datetime
Hi, I have two tables TABLEA and TABLEB. They both have the same columns. I need to insert data from TABLEA to TABLEB based on the following conditions TABLEA Structure with Sample Data PKEY Attribute Value Updated_DateTime 1 A 2.2 08:30AM 2 A 3.3 08:40AM 3 B 3.2 08:40AM 4 C 1.1 08:00AM 5 C 1.2 08:43PM 6 D 1.01 08:42AM This is the sample data for TABLEB PKEY Attribute Value 01 A 2.2 02 F 3.3 03 B 3.2 04 D 1.01 Now..When inserting data into TABLE B , ->I need to check if the Attribute already exists in TABLE B . -> If it already exists then I must update the Value feild with the latest value from TABLEA based on updated_datetime. (Ex: Attribute 'A' already exists in tableB so i need to update the VALUE feild in TABLE B to 3.3 which is the latest updated value in TABLEA at 08:40AM) -> If the Attribute doesnt exist then I simply need to insert that row into TABLEB Can some one please post the query or the procedure that I can use for the above requirement. Any help greatly appreciated.This is a urgent requirement for me.Please help Thanks
View Replies !
View Related
Convert Datetime Columns To Smalldatetime In Whole Database
Hi, I need to convert all datetime columns to smalldatetime in the whole database. I really don't want to do it by hand and It would probably take me a whole day to figure out how to write such a procedure. If someone could help me out that would be great. My database is divided into schemas just like AdventureWorks. Also, no need to worry about date conversion, value could be set to current date. Thanks
View Replies !
View Related
Issue: How To Convert A Database Field From Varchar To Datetime..?
Can anyone help me on this!I've got more than a 1000 records in a SQL server database.The problem is that the the date field is set to varchar, and that gives a lot of trouble. (for example by sorting a table, it's a mess)How can i make sure that i will have a table with the date field set to datettime en that those 1000 records still will be in it. thanks in advance!
View Replies !
View Related
Automatically Update Datetime Field In A Database Table.
hi e'body: I have some database tables, and each one of them have a creation_date and modified_date in them. I was trying to figure out a way where when a row in one of these tables is changed using Enterprise Manager (Database -> Tables -> select table -> right click -> select all rows -> change a field in a row inside a table), is there a way apart from triggers, such that the "modified_date" column for that row get changed to 'getdate()' (rather picks up the current datetime). thanks in advance.
View Replies !
View Related
Howto Insert A Null DateTime Into A Sql Server 2005 Database
I'm importing data form an Excel file to a Sql Server Database. Some of the data imported represents time as a double type so i convert the times into DateTime to be inserted into the database. The time values that aren't available in the Excel file are 0.. so what i want to do is insert null into the database for all the values that are 0 in the excel file... How do i do that based on this code i have so far:protected void ButtonImport_Click(object sender, EventArgs e){PanelUpload.Visible = false;PanelView.Visible = false;PanelImport.Visible = true;LabelImport.Text = "";OleDbCommand objCommand = new OleDbCommand();objCommand = ExcelConnection(); OleDbDataReader reader;reader = objCommand.ExecuteReader(); while (reader.Read()){DateTime? in_1 = null;DateTime? out_1 = null;DateTime? in_2 = null;DateTime? out_2 = null; int emp_id = Convert.ToInt32(reader["emp_id"]);DateTime date_entry = Convert.ToDateTime(reader["date_entry"]);if (Convert.ToDouble(reader["in_1"]) != 0)in_1 = ConvertDoubleToDateTime((double)reader["in_1"]);if (Convert.ToDouble(reader["out_1"]) != 0)out_1 = ConvertDoubleToDateTime((double)reader["out_1"]);if (Convert.ToDouble(reader["in_2"]) != 0)in_2 = ConvertDoubleToDateTime((double)reader["in_2"]);if (Convert.ToDouble(reader["out_2"]) != 0)out_2 = ConvertDoubleToDateTime((double)reader["out_2"]); ImportIntoAttendance(emp_id, date_entry, in_1, out_1, in_2, out_2);} reader.Close();}protected void ImportIntoAttendance(int emp_id, DateTime date_entry, DateTime? in_1, DateTime? out_1, DateTime? in_2, DateTime? out_2){ SqlDataSource AttendanceDataSource = new SqlDataSource();AttendanceDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["SalariesConnectionString1"].ToString();AttendanceDataSource.InsertCommandType = SqlDataSourceCommandType.Text;AttendanceDataSource.InsertCommand = "INSERT INTO Attendance (emp_id, date_entry, in_1, out_1, in_2, out_2) " +"VALUES ('" + emp_id + "', '" + date_entry + "', '" + in_1 + "', '" + out_1 + "', " +"'" + in_2 + "', '" + out_2 + "')"; int rowsAffected = 0;try{rowsAffected = AttendanceDataSource.Insert();}catch(Exception ex){LabelImport.Text += "<font color=red>" + ex + "</font><br />";} }private DateTime ConvertDoubleToDateTime(double dbTime){string[] SplitTime = dbTime.ToString().Split('.');string hours = SplitTime[0];string minutes = String.Empty;string time = String.Empty; if (dbTime.ToString().IndexOf('.') != -1){if (SplitTime[1].Length >= 1){if (SplitTime[1].Length == 1)minutes = Convert.ToString(Convert.ToDouble(SplitTime[1]) * 10);else if (SplitTime[1].Length > 1)minutes = SplitTime[1];}}elseminutes = "00";time = hours + ":" + minutes;return Convert.ToDateTime(time);}
View Replies !
View Related
Inserting And Retriving Datetime Field In Database MSSQL 2000
Hi, 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
View Replies !
View Related
Millisecond Values Missing When Inserting Datetime Into Datetime Column Of Sql Server
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
View Replies !
View Related
Inserting Datetime Through Sqldatasource - String Was Not Recognized As A Valid DateTime
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
View Replies !
View Related
Last Entry In A Log
(I don't post here often, so in case I'm violating long-standing taboosof this newsgroup, I apologize in advance for calling a relation atable, using nulls, and other ignorant, destructive, and comtemptibleterminology.)I have a table that's keeping a sort of running log of different typesof changes to pieces of data. The table has a foreign key of the databeing changed, the foreign key for the type of change occuring, someinformation about the change in a couple more columns, and a timestampfor each entry. So it's:dataIDeventIDeventInfotimestampWhat I'd like to do, if at all possible, is a single SQL query that,given a dataID, returns the most recent eventInfo and timestamp foreach eventID. Is this possible?Many thanks.-Eric
View Replies !
View Related
Could Not Add The Entry...
Can someone please help me solve the following error? I am not sure of the version my hosting company is using and where to get the manual. Could not add the entry because: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-id, title, entry, date_entered) VALUES ('testing', 'testing', . The query was INSERT INTO blog_entries (blog-id, title, entry, date_entered) VALUES ('testing', 'testing', 'testing'. PHP Code: if ($dbh = mysql_connect ('localhost', 'blu6592_money', 'rich')) { print '<p>Successfully connected to MySQL.</p>'; if (mysql_select_db ('blu6592_myblog')) { print '<p>The database has been selected.</p>'; } else { die ('<p>Could not select the database because: <b>' . mysql_error() . '</b></p>'); } $query = "INSERT INTO blog_entries (blog-id, title, entry, date_entered) VALUES (0, '{$_POST['title']}', '{$_POST['title']}', '{$_POST['entry']}', NOW())"; if (mysql_query ($query)) { print '<p>The blog entry has been added.</p>'; } else { print "<p>Could not add the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>"; } } else { die ('<p>Could not connect to MySQL because: <b>' . mysql_error() . '</b></p>'); } mysql_close(); Thanks in advance.
View Replies !
View Related
SQL Query: Finding Records Between Datetime Inside Datetime
Hey :)I'm facing a lot of troubles trying to create a new pause/break-system. Right now i'm building up the query that counts how many records that is inside 2 fields. Let me first show you my table: ID (int) | stamp_start (Type: DateTime) | stamp_end (Type: DateTime) | Username (varchar)0 | 17-03-07 12:00:00 | 17-03-07 12:30:00 | Hovgaard The client will enter a start time and a end time and this query should then count how many records that are inside this periode of time. Example: The client enter starttime: 12:05 and endtime: 12:35.The query shall then return 1 record found. The same thing if the user enters 12:20 and 12:50.My current query looks like this:SELECT COUNT(ID) AS Expr1 FROM table WHERE (start_stamp <= @pausetime_start) AND (end_stamp >= @pausetime_end)But this will only count if I enter the exact same times as the one inside the table.Any ideas how I can figure this out?Thanks for your time so far :)/Jonas Hovgaard - Denmark
View Replies !
View Related
Datetime Data Type Resulted In An Out-of-range Datetime Value. Please Help
Hi, I have a column of type datetime in sqlserver 2000. Whenever I try to insert the date '31/08/2006 23:28:59' I get the error "...datetime data type resulted in an out-of-range datetime value" I've looked everywhere and I can't solve the problem. Please note, I first got this error from an asp.net page and in order to ensure that it wasn't some problem with culture settings I decided to run the query straight in Sql Query Anaylser. The results were the same. What else could it be? cheers, Ernest
View Replies !
View Related
Data Entry
We are into BPO and Software development business for past six and half years. We are looking for the following any kind of business from your end. Back Office Process Data Entry, Large Volume Data Processing, Data Conversion, Forms Processing, Process flow & Quality Data Entry Text, Numeric or Alphanumeric entry, Printed or Handwritten matter, Hardcopies or Scanned Images, Some typical examples include, Mortgage Documents, Abstract records, Index cards, Market Research Tabulations, Various different forms, Guest/Customer comment cards, Property tax records, Accounting and Book keeping, Extracting data from Catalog, Payroll Services, Dictionaries, Manuals, Encyclopedia etc,Surveys,Questionnaires,Company reports, Material Safety Data Sheets (MSDS) forms, Airway Bill entries Large Volume Data Processing Formatting of Data like mailing list, Database creation and updation,Data Conversion,OCR Clean up, ICR, OMR,Scanning from hardcopies, microfilms, microfiche,Indexing,Archiving,PDF conversion: PDF to text, HTML etc,HTML conversion, XML tagging, Document conversion Forms Processing Credit Card Processing, Insurance Claims Processing, Market Research forms entry, Survey Forms Processing, Order Processing Financial Services Accounts Payable Management, Accounts Receivable Management, Account Setup, Account Reconciliation, Bank Reconciliation, Bookkeeping, Financial Statements Financial/Operational Budget, Payroll Services, Prepare and File Tax Returns, US GAAP Reporting Drafts Contact COMPUTER CONSULTANCY SERVICES INC, URL: www.ccsgroupindia.com Email: jobs@ccsgroupindia.com, (24 x 7 online support) Chat MSN : rajamanickam73@hotmail.com Chat Yahoo: rajamanickam73@yahoomail.com Tel: +91 44 42037794, 42037795
View Replies !
View Related
Error Log Entry
I keep getting this message in my error logs. I can't find any references to it in Books online. Does anybody know what this error is? "dbswritecheck: Read of first trailer failed"
View Replies !
View Related
Duplicate Entry
Hi All, I have a table with name C1_Subscribers with three fields (1)-MobileNumber [varchar] (2)-ReceivedTime [datetime] (3)-Status [char]. Now here how to remove duplicate entry of same mobile number to MobileNumber field? Regards Shaji
View Replies !
View Related
Anyone Know What Causes This Error Log Entry?
"High priority system task thread: Operating system error Exception 0xAE encountered." we are seeing this on one of our development servers, running SQL 2005 Enterprise SP1. The symptom is that after the sql service has been running a while (order of days), this error starts getting written to the sql server log file over and over, and eventually the logfile fills the drive. Apparently it's fixed by this hotfix, but our ops team doesn't want to install this - they'd rather just cycle the service and delete the log when it happens. I was wondering if anyone else has seen it might know the root cause so we might be able to workaround it without cycling the service. I know, lame question. The correct answer is "install the hotfix" or "install sp2" but our ops team won't do that for now... www.elsasoft.org
View Replies !
View Related
Automatic Field Entry
Hi,I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:IDUserIdDateDescription(UserId is a foreign key to asp_net_Users as I am supporting user accounts)Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).Can anyone point me in the right direction on the steps needed to create this scenario?Thanks for any helpDaniel
View Replies !
View Related
Verifying Entry Is Not In DB B4 Inserting
How do i do that... in sch lab, the lecturer said: Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Try SqlDataSource2.SelectParameters("Title").DefaultValue = txtProductTitle.Text GridView1.DataBind() If GridView1.Rows.Count > 0 Then //if no repeated Label1.Text = "Existing Record Found. Please enter another Software." Else 'SqlDataSource1.Insert() 'Label1.Text = txtProductTitle.Text & " successfully added to the database." End If Catch ex As Exception Label1.ForeColor = Drawing.Color.Red Label1.Text = "An error occured!" & ControlChars.NewLine & ControlChars.NewLine & _ ex.Message.ToString() End Try End Sub but its not working for me... the GridView1.Rows.Count is always 0, if there is a repeat, it should be > 0
View Replies !
View Related
Selecting First Entry In Results By Name
Hello, I have a table of names/dates as such: Class 1 1/1/2007Class 1 1/3/2007Class 1 1/5/2007Class 2 2/1/2007Class 2 2/3/2007Class 3 3/1/2007 What I want to do is select only the nearest entry from the list for each distinct class, so the results are like this: Class 1 1/1/2007Class 2 2/1/2007Class 3 3/1/2007 So basically, I want the first upcoming class in the list for each distinct class name. How do I do this? I'm using SQL 2005 Express. Thanks.
View Replies !
View Related
Could Not Find The Index Entry For RID...
Greetings. Today I was testing the sql backup/restore functions on ourprimary server. I was able to backup my database without any problemsbut when I tried to restore it, I received an error "Could not find theindex entry for RID '3610200101a03cc4b49d8bbc84bac50cbe042cecf76' inindex page (1:40), index ID 0, database 'walkthrough'." Thinking thatdata had been corrupted somehow I tried another database and received asimilar error for that database. In the past, our service provider hasattempted to restore data to this server and the restore failed do toanother data corruption. The problem is, I can't find out where thecorruption is located or how to fix it.When I open the restored database in enterprise manager, it returns theabove error and is unable to get a listing of my tables. In queryanalyzer I am able to view my data but receive the index error when Iattempt to look at the information_schema data. When I try to run a DBCCCHECKDB on the corrupted database it reports 0 errors and 0inconsistencies.So my diagnosis of the problem is that something is corrupting my systemtables and that problem shows up whenever I try to restore data from abackup. There could be a hardware failure but I believe the failurewould affect more then just the sql system tables. Can anyone offer anyadvice on how to find the corruption?Thank you in advanceRichard Bailey*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
View Related
Query Every Entry Not In Each Hour
Hello,I have been having a tough time writing the follow requirement for aquery.On a table that the primary key is a tagId and an hourly timestamp, Iwould like to find out for every hour which tags did not get enteredinto the database. Essentially I am looking for patterns of entriesthat are not making it into tableB.Examples of the tables:TableA TableBTagID and TagName TagId TimestampPK PK1 PK2approx 6000 rows approx 6000 rows per hourI am thinking that I will need to do something like:Select tableB1.time, count(*) from tableB1 group by tableB1.timehaving tableB1.time >= XXXX and tableB1.time <= XXXX and tableB1.tagIdnot in (select tagId from tableA where not exists (selecttableA.tagId, distinct.tableB2.time from tableB2)I have been trying to create an effecient query handle this but havenot had any luck. Any assistance would be more then appreciated.Thanks,Andy
View Replies !
View Related
Overwriting Entry In Table
Task: To insert entries into a table. The table has a primary key based on a field 'ID'. When inserting into the destination table, I want to make sure that the new entry will overwrite the old entry. What's the quickest/cleaniest way to do this ? thanks, Clayton
View Replies !
View Related
Number Of Entry Query.
MSSQL2K SP4 Howdy all. Im trying to write a query that will track a data modification grouped by employer ID and transaction date. I don't know if Im asking it right so here is what I have, plus my current and desired outputs. --drop table #foo create table #foo (empID int, transDate datetime, transType varchar(10)) insert into #foo values(1, '01/01/06 01:01:01','Insert') insert into #foo values(1, '01/01/06 01:01:02','Update') insert into #foo values(1, '01/01/06 01:01:03','Delete') insert into #foo values(2, '01/01/06 01:01:01','Insert') insert into #foo values(2, '01/01/06 01:01:02','Update') select f.empID, Change = (select count(transDate) from #foo f2 where f2.empID = f.empID group by empID), f.transDate, f.transType from #foo f Current results: 132006-01-01 01:01:01.000Insert 132006-01-01 01:01:02.000Update 132006-01-01 01:01:03.000Delete 222006-01-01 01:01:01.000Insert 222006-01-01 01:01:02.000Update Desired results: 112006-01-01 01:01:01.000Insert 122006-01-01 01:01:02.000Update 132006-01-01 01:01:03.000Delete 212006-01-01 01:01:01.000Insert 222006-01-01 01:01:02.000Update TIA, CFR
View Replies !
View Related
System DSN Entry Does Not Show Up
I am installing a product that sets up a System DSN entry for an added database. The problem is that it does not show up in the ODBC - System DSN group as it should. If I try to add it manually, the system tells me that it already exists. I try to overwrite it, which seems to work, but the entry still does not show up. Is there a place I can "clear" out the entry from beside that panel in the odbc administrator? If I try to user the application to access the data, it does not find it under the local system. If I plug in the server ID from the server manager, I do see the data source and can log onto the system. So, the connection is there, but ODBC Data Source Admin does not see it and the application does not see the server as Local. Any suggestions?
View Replies !
View Related
|
TRACKER
