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
I have two tables. Table A contains a unique column of some Keys. Table B contains Key column,Value column and Entry_Time column. Now I need the most recently entered value for each of the Keys in table A using table B. I dont need any repeatation of Keys in my result.
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?
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task. In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful. Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
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
I'm trying to create a report which would give the latest transaction on a database, which all sit on different servers. I wanted to know if there is an a simple way tracking the latest transactions instead of getting the information from the database tables.
guys this is kind of a biggie for me. i've only used sql server to compare the Date Portion of date(s), not the whole string (mm/dd/yyyy hh:mi). i have a table called Time_Check which has two fields with timestamps, "TimeLastRun & TimeNextRun". i was trying to compare the Current TimeStamp that i pass from a vb.net form to a stored procedure and compare that DateTime to the TimeNextRun. I'd like to then Update the two Time Columns if rows are found. Should i maybe be using the DateDif Func if i know ahead of time how much time should be elapsed(say five minutes)? Any Code help is appreciated
Create Procedure dbo.My_Time_Check @myDate smalldatetime as select alertnumber,alertname,TimeLastRun,TimeNextRun from time_check where timeNextRun >= @myDate --would like to update these two columns if rows are found
greets again folks, The values LastLoginDate and LastActivityDate in my SQl Express membership dBase are always off. The date is usually correct but the time is always hours off. Is there some way to get the time part of the DateTime to be correct? Do I have to write code to set the time when the user logs in? Thanks a mil!
Hello all,I'm kind of new to ASP.NET and I've hit my first unsolvable roadblock. I'm trying to create a formview insertcommand that allows me to place the current date into a DateTime field but I continuously get an Input string was not in a correct format error. I may be using the wrong functions, I'm unsure. I've messed with DateTime.Now.ToString() as well as GETDATE() but I have no idea how to get it to work. I've also tried to switch the variable type of the field to a String type as well as DateTime. I am running visual basic .net with Microsoft SQL Server 2005. Here is my code right now: Experiencing problems with technology? Just fill out the form below and your problem will be forwarded to the Information Technology department.</em></strong><br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TechticketdbConnectionString %>" SelectCommand="SELECT [CRDL#] AS column1, [Employee ID] AS Employee_ID, [Problem description] AS Problem_description, [Date submitted] AS Date_submitted, [Ticket number] AS Ticket_number FROM [Tickets]" DeleteCommand="DELETE FROM [Tickets] WHERE [Ticket number] = @Ticket_number" InsertCommand="INSERT INTO [Tickets] ([CRDL#], [Employee ID], [Problem description], [Date submitted]) VALUES (@column1, @Employee_ID, @Problem_description, GETDATE() )" UpdateCommand="UPDATE [Tickets] SET [CRDL#] = @column1, [Employee ID] = @Employee_ID, [Problem description] = @Problem_description, [Date submitted] = @Date_submitted WHERE [Ticket number] = @Ticket_number"> <DeleteParameters> <asp:Parameter Name="Ticket_number" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="column1" Type="Int32" /> <asp:Parameter Name="Employee_ID" Type="Int32" /> <asp:Parameter Name="Problem_description" Type="String" /> <asp:Parameter Name="Date_submitted" Type="String" /> <asp:Parameter Name="Ticket_number" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="column1" Type="Int32" /> <asp:Parameter Name="Employee_ID" Type="Int32" /> <asp:Parameter Name="Problem_description" Type="String" /> <asp:Parameter Name="Date_submitted" Type="String" /> </InsertParameters> </asp:SqlDataSource> I've left out the EditItemTemplate because it isn't going to be used for this form. Here is the InsertItemTemplate: <InsertItemTemplate> column1: <asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>'> </asp:TextBox><br /> Employee_ID: <asp:TextBox ID="Employee_IDTextBox" runat="server" Text='<%# Bind("Employee_ID") %>'> </asp:TextBox><br /> Problem_description: <asp:TextBox ID="Problem_descriptionTextBox" runat="server" Text='<%# Bind("Problem_description") %>'> </asp:TextBox><br /> <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert"> </asp:LinkButton> <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel"> </asp:LinkButton> </InsertItemTemplate> <ItemTemplate> column1: <asp:Label ID="column1Label" runat="server" Text='<%# Bind("column1") %>'></asp:Label><br /> Employee_ID: <asp:Label ID="Employee_IDLabel" runat="server" Text='<%# Bind("Employee_ID") %>'> </asp:Label><br /> Problem_description: <asp:Label ID="Problem_descriptionLabel" runat="server" Text='<%# Bind("Problem_description") %>'> </asp:Label><br /> Date_submitted: <asp:Label ID="Date_submittedLabel" runat="server" Text='<%# Bind("Date_submitted") %>'> </asp:Label><br /> Ticket_number: <asp:Label ID="Ticket_numberLabel" runat="server" Text='<%# Eval("Ticket_number") %>'> </asp:Label><br /> <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit"> </asp:LinkButton> <asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete"> </asp:LinkButton> <asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="New"> </asp:LinkButton> </ItemTemplate> </asp:FormView> I would like to get the value formatted as a DateTime but a String will do just fine if a DateTime variable won't work. Any help would be greatly appreciated. Thanks!
If myDateTimeColumn contains a <NULL> value. How do you handle that when reading into a DateTime object in your code?DateTime myDate = Convert.ToDateTime(dr["myDateTimeColumn"]);Does not work, it throws: System.InvalidCastException: Object cannot be cast from DBNull to other types. I am curious as to what others are doing to handle this?
Hi, When I try to insert a new record into a table that has a datetime field that allows nulls, a default 01/01/1900 date is inserted instead of null. I recreated the table and set the datatype to smalldatetime and I still get the error. What have I missed?
am making a CV program and i need a way to count the experience the user has: i have his begin date and end Date as datetime in an sql server. i can do it programicly but i prefer to do it at the sql side the question: how can i get how much exp he has aka : SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) but for all the datarow (he has more than one BeginDate and EndDate (for each job he has one)) P.S i want to be able to use it in a where clause : select * from jobap where -- or HAVING JobAp.ind = JobApExp.JobAp AND SUM(DATEDIFF(year , JobApExp.BeginDate , JobApExp.EndDate )) > CONVERT(int,@Exp) thanks in advance
I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:
SELECT * FROM ADMIN WHERE date_edited = NULL
I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:
I've recently discovered the MS CRM 3.0 stamps any datetime field in SQL as UTC (aka GMT). Even though the end user may select 12:00 noon on the CRM gui interface, the datetime is stamped in SQL as 16:00 (I'm in EST).
So, is there an easy way to read the 16:00 back to local EST, taking into account daylightsavings (DST)? Apparently in VB or C# this conversion is a trivial matter of using ToLocalTime(), but I don't believe SQL 2005 has such a convenient function.
I'm currently building a SQL view that I wish to use to support multiple reports, and it would be ideal for the view to translate the UTC datetime fields (even if via a user-defined function) to EST rather than coding every report or custom app to translate the UTC.
Any suggestions or links to code will be greatly appreciated!
Hi All I am working on a query to get all the datetime values in a column in a table into a comma separated text. eg. ColumnDate --------------------------- 2005-11-09 00:00:00.0002005-11-13 00:00:00.0002005-11-14 00:00:00.0002005-11-16 00:00:00.000 I wanted to get something like 2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16 Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks
I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.
Is there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger?
Here is a very basic example of what I am trying to do:
--example: '09-22-2007 15:07:18.850' this is the value inserted into the table by the code select getdate()
--example: '2007-09-22 15:07:00.000' this is the value I want to store in the table select dateadd(mi, datediff(mi, 0, getdate()), 0)
I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?
Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.
I found some thing to achieve the above thing. 1. Take the input row and check in the database. 2. If the row is not found in the database then treat it as new row. 3. If row is found in the database then a) Take the source row and prepare a concatenated string for all the columns b) Take the database row and prepare a concatenated string for all the columns c) Find out the hash code for the 2 strings and then compare hash codes for equal.
The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.
Can anybody suggest a good method to do this?
What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do? Will it generates hash code based on all the columns values?
) ================================================= In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code: --Form_Kimmel.vb-- Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
End Class ==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
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.
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?
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 ?
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
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?
