DateTime From Database Entry

Nov 9, 2007

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 4 Replies


ADVERTISEMENT

Data Entry In A Datetime Field

Jan 22, 2007

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 9 Replies View Related

Data Entry In A Datetime Field.

Jun 1, 2007

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 29 Replies View Related

Find The Earliest Datetime Entry & Then Update

Mar 12, 2007

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 10 Replies View Related

Datetime Entry For Querying Analysis Service Cube

Jan 16, 2007

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 4 Replies View Related

Identical Database Entry Already?

Aug 14, 2005

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 4 Replies View Related

Null Entry In The Database

Jan 31, 2008



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 3 Replies View Related

Selecting The SECOND To Most Recent Entry From A Database

May 4, 2008

I know how to select the most recent row from a database:
SELECT TOP (1) Location, Date FROM Images ORDER BY Date DESC
But how do I select the second to most recent? or the third most recent? or the 4th, ect, ect, ect.
There must be some method to it, anyone have any suggestions?
 

View 5 Replies View Related

Checking If An Entry Exists In A Database

Apr 22, 2006

I would like to be able to check if a certain entry exists in a SQL table. Ideally, the output would be a boolean so I can use it in an IF statement which would tell it what to do depending on whether or not the entry exists. Thanks for anyone that helps. :)

View 6 Replies View Related

Web Based Data Entry Into SQL 7.0 Database

Jun 1, 2000

Hi,

Do you have any idea if there is / where can I find web based data entry into SQL 7.0 database. What I'm looking for is something like Oracle Forms but for SQL 7.0.

Thanks in advance,

Boaz

View 1 Replies View Related

How To Prevent The Insertion Of The Same Entry Of An Unique Value Into The Database

Jun 4, 2008

how to prevent the insertion of the same entry of an unique value into the database?
assume that I have a primary key username " abc"  in the database
and then I insert the "abc" again ,the debuggging error msg pops out saying the primary key cannot be duplicated..
how can I do an if--else statement to check the database against this unintentional inputs of the same unique data " abc"???
 

View 7 Replies View Related

NORTHWIND Database Was Re-created From A Different Database:How Can I Change The Entry In Sysdatabases For Database 'NORTHWIND'?

Jan 14, 2008

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 5 Replies View Related

DateTime Unable To Save In Datetime Field Of SQL Database

Mar 14, 2007

 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 4 Replies View Related

URGENT!!! Could Not Locate Entry In Sysdatabases For Database 'msdb'

Aug 7, 2002

Any idea what this error is?
error 911: could not locate entry in sysdatabases for database 'msdb'. No entry found with that name

Thanks.
Sa

View 1 Replies View Related

Insert From Formview And Checking Database To Avoid A Duplicate Entry

Apr 6, 2007

I have a form view that I am using to insert new data into a sql express database and would like to find a way to avoid attempting to insert a record if the key already exists.  is there a way to do this with the formview insert command.  Everything works great until I try to add a record with an already existing value in the unique key field, then it breaks.

View 1 Replies View Related

System.Data.SqlClient.SqlException: Could Not Locate Entry In Sysdatabases For Database

Oct 18, 2007

I am getting the exception - System.Data.SqlClient.SqlException: Could not locate entry in sysdatabases for database. Does anyone has any idea, how to resolve this?
Thanks

View 2 Replies View Related

Adding Datetime To Database?

Dec 23, 2005

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 5 Replies View Related

Update Datetime Into Database

Feb 7, 2008



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 3 Replies View Related

Problem When Insert DateTime To DataBase

May 16, 2007

 
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 2 Replies View Related

Inserting Datetime In Microsoft SQL Database

Dec 29, 2004

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 2 Replies View Related

DateTime Null In Sql Server Database

May 11, 2005

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 3 Replies View Related

Get Latest Values From Database With Max Datetime

Sep 12, 2006

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 1 Replies View Related

What Default Date To Use In Database Datetime Field

Aug 4, 2006

We want to add a default date to our database tables. Looking at other database samples people use all sorts of dates to add as default date e.g. 1/1/1997 or the getdate() function.
Is it good practice to set a default date and what should the default date be????
Newbie

View 2 Replies View Related

Convert Datetime Columns To Smalldatetime In Whole Database

Nov 7, 2007

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 14 Replies View Related

How Do I Set The DateTime Attribute To My SQL Database So That It Shows UK Time?

Aug 3, 2007

hello,
I have been searching everywhere and I didn't find anything about that:

How do I set the DateTime attribute to my SQL Database so that it shows UK time? (ASP.NET,C#),
thank-you!

View 2 Replies View Related

Issue: How To Convert A Database Field From Varchar To Datetime..?

Jul 22, 2005

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 1 Replies View Related

Automatically Update Datetime Field In A Database Table.

Feb 28, 2006

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 1 Replies View Related

Howto Insert A Null DateTime Into A Sql Server 2005 Database

Apr 2, 2008

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 3 Replies View Related

Inserting And Retriving Datetime Field In Database MSSQL 2000

Jan 10, 2006

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 4 Replies View Related

SQL 2012 :: Loading Raw Files Into Database - Datetime Format Conversion

May 23, 2014

I am using SSIS to load raw files into database. In my files I have columns Date which has format

1/1/2010 12:00:00 PM.

I want to load this column in format 1/1/2010 24:00:00. I mean in 24 hour format.

View 5 Replies View Related

Millisecond Values Missing When Inserting Datetime Into Datetime Column Of Sql Server

Jul 9, 2007

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 3 Replies View Related

Inserting Datetime Through Sqldatasource - String Was Not Recognized As A Valid DateTime

Dec 6, 2006

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 1 Replies View Related

Could Not Add The Entry...

Nov 22, 2005

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 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved