Dim ldDatetime As DateTime = Now() '11/12/2005 13:05:09
my command text is:
"Insert Into WebSiteThread (TransactionDate) Values ( " & " ' " & ldDatetime & " ' " & ") "
The result I got was "12/11/3091 13:05:09" Not "11/12/2005 13:05:09"
What's wrong please!!
I have been using MySQL for a long time but because of company policy changes I have started to switch from MySQL to MsSQL
OMG MsSQL is very slow with every thing it does compared to MySQL. I.e updating 300.000+) data in a table with 15 million+ records using 'update' syntax. In MySQL it was done in 4 minutes. In MsSQL it was not done in 20 minutes (and then I stoped it in anger)
I have a script that I have created to give our daily revenue. In MySQL that takes 30 sec to finish. In MsSQL it takes atleast 1.30 minutes.
I have 1,5G of memory and MsSQL uses it all. MySQL uses 200mb at most.
What am I doing wrong. Is it the memory or what can it be? I have indexed the tables in the same way. I really don't know what to do anymore.
Perhaps You can give me some hints
Tx in advance ;-)
I have a stored procedure to add a poeersons DOB to a table. The date is being stored as Dec 12 1980
Instead of as 12/12/1980
The table uses a datetime data type.
The data type of the date being passed into the stored procedure is also set to datetime. (@DOB datetime) Even though the value passed into the procedure is 12/12/1980 It saves the date as Dec 12 1980
What do I need to do to fix this?
Miranda
I am trying to convert my datetime to 107 ex: Dec 11 2006 ,but when sorting I get the months sorted correctly but not by year????
ex:
Jan 2004
Feb 2001
Mar 2006
In my report I have two date parameters, both are of type DateTime.
The problem is that when the report is called for the first time the report parameter value is not shown in the expected format (de-DE, 01.01.2007) but in en-US 01/01/2007
Only when I change a Date using the calendar popup and click on the "show report" button, the right format will be taken over.
Language properties of the report are"=User!Language"
IE language is de-DE.
Report Server is a MS Windows Server 2003 R2 Standart Edition SP2
with MS SQL SERVER 2005 SP2
P.S.
on a server with MS SQL SERVER 2005 SP1, this report works fine.
may be this bug is a new feature of SP2?
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1
W2003 + IIS6.0
Pls advice?
Hi People
I've just imigrated from mysql to mssql, but i've a few problems when im converting my data.
When im trying to insert a date formated as yyyy-mm-dd hh:mm:ss intro a smalldate field, im just getting the following error:
Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '2006'.
Their isnt set any special settings on the db or the table, í've tried to google it, but widthout finding any solution.
Anybody have a idea of what im doing wrong?
Thanks in advance,
Torben
Hi All,This is only my second day using C#/asp.net and English isn't my native language so I'll apologise in advance if this question doesn't make sense and/or is stupid . I'm trying to make a little event planner which was going well until I hit a small problem, the user needs to enter the event name and choose a date/time from dropdowns, this bit works okay... however I'm having difficulties with storing the date/time, at the moment I'm making a DateTime object and filling out the year/month/day/hour/minute selected on the form and adding that to the database field (which is a datetime field type).Problem is, I'm using the date format DD/MM/YYYY HH:MM:SS but the database stores it as MM/DD/YYYY HH:MM:SS so if I wanted to do things like:
select * from tbl where date > DateTime.Now - it isn't going to work because the month is switched with the day in the DB-versions.
Just to try and give another example, if I'm not being very clear, if I print DateTime.Now.ToString() it will display 10/06/2008 02:50:48 however if I put it into the database and pull it back out cast to a DateTime Object and then display that object via ToString() it'll be 06/10/2008 02:50:48
Any suggestions on what to do would be appreciated, usually in cases like this I'd just use the unix timestamp, but since C#/ASP.NET doesn't support that natively I was hoping there was an easier/native alternative. Also I don't have access to the server configuration so changing the localization of mssql isn't an option unfortunately.Thanks in advance!
Hi, i am trying to retrive some datetime set from the database (2 posts in this table), senastposted is a datetime field.when i run:SELECT senastposted FROM ftp WHERE senastposted <= 2005-10-28i get this result:
senastposted
0 record(s) affected.and when i run: SELECT senastposted FROM ftp WHERE senastposted >= 2005-10-28i get this result:
senastposted
25-10-2005
29-10-2005
2 record(s) affected.what i want is to retrive the first value based on the date:25-10-2005any idea why SELECT senastposted FROM ftp WHERE senastposted <= 2005-10-28 dosent work?( i have tried to change the dates to 28-10-2005 and 28/10-2005)
I am using Sql Server 2012. I have a table which has a field as Datetime (it is a table in Dynamics CRM 2011 so I have no control of the data type). Say this field is called BisStartDate. If I run this query in management studio.
select
BisStartDate, BisStartDateutc
from myTable
where _bisnumber=10375
I will get:
BisStartDate BisStartDateutc
2014-07-29 00:00:00.000 2014-07-29 05:00:00.000
*in CRM, datetime is saved in 2 fields, one is the current time, the other one is the utc time.
You can see the offset between the datetime and utc is 5 hours.
However when the same statement was running inside a SSIS package on the server, the result returned is:
BisStartDate BisStartDateutc
2014-07-28 23:00:00.0000000 2014-07-29 05:00:00.000
And if I do
datediff(MINUTE,CRMAF_BisSection.ttc_section_startdatetimeutc,CRMAF_BisSection.ttc_section_startdatetime)
I will get -5 if I run it in ManagementStudio and -6 is running on server package(running inside VisualStudio will be -5, same as running a query in ManagementStudio).
I think when the record was saved, “date” is 5 hours offset to UTC time but now the system use the current utc offset which is 6 hours. I just want to use the BisStartDate as it is. How do I let the SSIS turn off the conversion.
The same datatime is saved in another system then we compare them to check the data entry. Now because of this one hour difference, sometime the Day will be different.
Hi expert.. I am doing a project that have function to capture the Day and Night time talking duration of caller.... I make use of MSSQL to store the date time file..Let say I have this date: "2006-10-02 00:02:09".. How di I select "00:02:09" only???? Thanks in advancesuigion
View 2 Replies View Related
I am using SQL 2000 and have a major issue! I am new to SQL and not sure what to do about the license key being lost due to an update.
Background:
The server was originally loaded with a temp verison of SQL 2000 by the installer of the hardware until the Enterprise Edition arrived on site to insure that all the hardware worked fine. The Enterprise software was loaded onto the system and the license keys were installed. Everything has been working fine for several months. The server has been rebooted a few times after installing the license keys.
Trouble:
I loaded SP2 for Server 2003 and rebooted the server and now SQL thinks it is running the temp version and states that the license key has expired. I tried the suggested solution listed on the site of "upgrading with no changes" and the installation completes but never asks for my license keys. I rebooted the server and it states the samething thing that the temp has expired. It appears that one section can see the keys and another section does not. I have an active database that I do not want to loose but I am stumped on what to do to in order to re-install the license keys.
Any suggestions? Can copy the databases that I have created, uninstall SQL, re-install SQL from CD and then paste back my database? Will the databases restore correctly or is this to simple of a solution?
I have run out of ideas and options in order not to loose the database.
Hi
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:
thanks
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
hi ,
this is my first post on MS Forums .
Q: my question is that , i want to format a date column .. d/m/yyyy .. but there is no any format facility ..
anybody can help me here ??
UPDATE CART SET TOTAL_COUNT = TOTAL_COUNT + 1, SET TOTAL_COUNT1 = TOTAL_COUNT1 + 1, SET DATE1 = 10/30/2006 6:17:02 PM, SET PERSON = luke WHERE CART_ID = 1
I have wacked my head over this query I dont see anything wrong with it
any ideas?
I wrote this update statement and it looks right but I keep getting an error that says "incorrect syntax next to c" what did I do wrong?
Here is the statement:
UPDATE tblclients c
SET intproviderId = (SELECT TOP 1 t.inttransferid FROM tbltransfer t WHERE t.intclientids = c.intclientid ORDER BY t.inttransferid DESC)
My DataAdapter.Update() Method is running this query against my database and all the parameters and the formatting looks correct to me. I was wondering if anyone could identify obvious errors.... Thanks!
exec sp_executesql
N'NewUpdateCommand',
N'@Name nvarchar(50),
@PriContactFName nvarchar(50),
@PriContactLName nvarchar(50),
@PriContactWork nvarchar(20),
@PriContactFax nvarchar(20),
@PriContactCell nvarchar(20),
@PriContactEmail nvarchar(50),
@MainOfficeLocationAddr1 nvarchar(50),
@MainOfficeLocationAddr2 nvarchar(50),
@MainOfficeLocationCity nvarchar(50),
@MainOfficeLocationState nvarchar(50),
@MainOfficeLocationZip nvarchar(15),
@Description nvarchar(50),
@Original_ClientID int,
@ClientID int',
@Name=N'Brinker International',
@PriContactFName=N'Steves',
@PriContactLName=N'Maubley',
@PriContactWork=N'913-876-9876',
@PriContactFax=N'913-098-8765',
@PriContactCell=N'913-987-7654',
@PriContactEmail=N'a@b.c',
@MainOfficeLocationAddr1=N'1234 Burkwood',
@MainOfficeLocationAddr2=NULL,
@MainOfficeLocationCity=N'Tallahassee',
@MainOfficeLocationState=N'Florida',
@MainOfficeLocationZip=N'99111',
@Description=N'This is a good client.',
@Original_ClientID=1,
@ClientID=1
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'NewUpdateCommand'.
here is my code:
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
hi.
i am working with PHP and MSSQL (i know its not a smart move, but I don't have a choice).
I got almost everything working correctly except the date format.
I can select the date and display it the way I want by doing this select convert(VARCHAR(10), date, 105) as new_date FROM table.
But when I have a field to update the date I am having trouble.
How do I update the MSSQL database with a new date if the date displayed is DD-MM-YY
Table A, Table B. need to update table A balance field from table b sum of amount
UPDATE CUSTOMERS SET BALANCE=(SELECT SUM(AMOUNT) FROM PAYMENT,CUSTOMERS
WHERE CUSTOMERS.ID=PAYMENT.ID GROUP BY PAYMENT.ID)
Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
In order to update a MSSQL table, named [Table_1]. The procedure is:
(@id int,@Field0 nvarchar(1000) = NULL,@Field1 nvarchar(1000) = NULL,... ...@Field9 nvarchar(1000) = NULL)Method 1:
UPDATE Table_1SET Field0 = CASE WHEN @Field0 IS NULL THEN Field0 ELSE @Field0 END, Field1 = CASE WHEN @Field1 IS NULL THEN Field1 ELSE @Field1 END, ... ... Field9 = CASE WHEN @Field9 IS NULL THEN Field9 ELSE @Field9 ENDWHERE id = @id
Method 2
IF @Field0 IS NOT NULL UPDATE Table_1 SET Field0 = @Field0 WHERE id = @id ... ... UPDATE Table_1 SET Field9 = @Field9 WHERE id = @id
Method 3:
DECLARE @SQL nvarchar(max)DECLARE @NeedComma bitSET @SQL = 'UPDATE Table_1 SET 'SET @NeedComma = 0IF @Field0 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field0 = @Field0' SET @NeedComma = 1 END... ...IF @Field9 IS NOT NULL BEGIN IF @NeedComma = 1 SET @SQL = @SQL + ', ' SET @SQL = @SQL + 'Field9 = @Field9' SET @NeedComma = 1 ENDSET @SQL = @SQL + ' WHERE id = @id'EXEC sp_executeSQL @SQL, N'@id int,@Field0 nvarchar(1000), ... ... , @Field9 nvarchar(1000)', @id = @id, @Field0 = @Field0, ... ... , @Field9 = @Field9
Which is the best? Thanks!
I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.
Thank you
"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"
Hello guys,
Here is the case. I have up and running database on MSSQL server. My client deliver me updates. Since now they were as INSERT statements, but they decided to change some stuff at their side. Now the updates will come as pairs of files (.dbf and .mdx) for each table...
How I can insert them in the running database?
Is there any way where I can add the MySQL Server as a Linked server to MS SQL server?
Is there any way where I can update a table in MySQL server based because a row was updated in MS SQL server using a trigger?
Thanks,
Amit
Hi all,I have a
problem here where I am trying to use SQL Parameters to update a column
in the database, but the problem is that I need to concatenate the same
column to some Text from an Application
This is the Code I Have, But it throws an exception
UPDATE rdm_comments SET commentinfo = commentinfo + 'additional info' where commentid=2356
I get an Error stating that i cannot use the addition operator for a string. Help
Hi im new to SSIS even i did some basice things in SSIS. Now i run in to the problem, I hawe a access file with arount one milion records and i won to transfare this records in Ms SqlServer . But befora i transfare that i nead to check if that record exsist by ID if exsist i must do update else i must do insert.
Can some one Help me how can i do it..
THX
Sorry for my bad englisht
Here is my problem: I've created a table in MSSQL named MyReferences and then a typed dataset to connect to it.
Then I realized that I needed to add a column to MyReferences table and then I did it.
The problem is that I cannot find a way to add the new column to the typed dataset. I am unable to view it in the available columns.
Can anyone help me?
I'm using Visual Studio 2005 sp1 and C#.
Best regards
Alessandro
I'll installing update http://support.microsoft.com/?kbid=899761 to correct memory usage. When installation process started I recieved this error - "An error in updating your system is occured." and installation failed. What I must doing to correct this issue?
Hi ~
I made simple stored procedure that is to update user information following as...
ALTER PROCEDURE UpdateUserProfile( @user_id uniqueidentifier, @user_firstname nvarchar(50), @user_lastname nvarchar(50), @user_birth nvarchar(20), @user_gender nvarchar(20) )
AS
UPDATE user_profile SET user_firstname = @user_firstname, user_lastname = @user_lastname, user_birth = @user_birth, user_gender = @user_gender WHERE user_id = @user_id RETURN
When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message.
What's the problem ?
QuestionHow can one update a DateTime field when the change request may alter any of the attributes of the time? (Day | hour | hour & minute).
WHYOf course the whole point of a timestamp is to stamp the time
of the record. The problem comes in when the user wants to tweak that
timestamp. I am not interested changing anything about the time at the seconds point. The user will only change major time factors.
Platform.Net 1xC#SQL Server 2000Eventually this will be in a Stored Procedure.
advTHANKSance
Hey,
I have a case where I fill a datatable with two column wich contain
datetime. Il filled them and everything seems alright in debug. Problem
is that when I update with the SQLAdapter, it says that it cannot
insert NULL into a datetime column, but the fact is that it is not NULL.
Here what it looks like:
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["BD"]);
conn.Open();
adapter.InsertCommand = new SqlCommand("Commun.SP__PeriodeEnregistrer", conn);
adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
adapter.InsertCommand.Parameters.Add("@idperiode", SqlDbType.Int, 10, "IDPeriode");
adapter.InsertCommand.Parameters.Add("@nosemaine", SqlDbType.Int,10,"NoSemaine");
adapter.InsertCommand.Parameters.Add("@annee", SqlDbType.Int, 10, "Annee");
adapter.InsertCommand.Parameters.Add("@periode", SqlDbType.Int, 10, "Periode_no");
adapter.InsertCommand.Parameters.Add("@datedebut", SqlDbType.DateTime, 150, "Debut_date");
adapter.InsertCommand.Parameters.Add("@datefin", SqlDbType.DateTime, 150, "Fin_date");
adapter.Update(dtPeriode);
Thanks in advance
I'm trying to update a datetime column from another datetime column. However, I just want the date transferred to the new column without the time. Any ideas? Thanks for your help.
View 6 Replies View Related