|
Try this out
BETWEEN CONVERT(DATETIME, '2004-06-21 00:00:00', 102) AND CONVERT(DATETIME,
'2004-06-25 00:00:00', 102))
http://msdn2.microsoft.com/en-us/library/aa226054(sql.80).aspx
|
|
|
|
|
Hi,
currently tryin 2 find a connection from .net that can work with both sql server and windows ce as the application i am creating will need to access both? any words of wisdom would be great, was told maybe an ole db one might work but not too sure now,
thanks
gav
|
|
|
|
|
hi friends
i have two tables like t1,t2
In t1,i set primary key
In t2,i set foreign key
now both tables are related.
now i am update ,delete primary key column but it is not affected foreginkey table
how to write query affcted primarykey table as well as foregin key table
can anybody help me
regards
saravan
|
|
|
|
|
|
What's wrong with these code: a new field named "Field19" is created. i wanna set all the values in Field19 are "abc:
CDaoDatabase db;
CDaoTableDef tableDef(&db);
db.Open("C:\\ktt.mdb");
tableDef.Open("Table3");
CDaoRecordset recset( &db );
tableDef.CreateField("Field19",dbText,255,dbUpdatableField | dbVariableField);
if ( !recset.IsEOF() )
recset.MoveLast();
while( !recset.IsEOF() )
{
recset.SetFieldValue("Field19","abc");
recset.MovePrev();
}
|
|
|
|
|
You should call Edit before starting to change a record's values and call Update when done, before moving to the next record.
|
|
|
|
|
I am unable to 'add webreference' of a web service written as a SQL Server 2005 HTTP EndPoint in my VS 2005.
It gives an error - 'There was an error while downloading http://server/servicename?wsdl
Though:
1. I can open the wsdl in IE on my machine.
2. On other machine on VS 2005, I can download the wsdl and add it as a web reference.
3. I can add a web reference of an HTTP endpoint hosted on my local SQL Server.
So what could be wrong. Is there any configuration required in the SQL Server to allow my IP address access to download the proxy?
Thanks
|
|
|
|
|
Hello All,
We have a SSIS package which includes Fuzzy Grouping in Data Flow. It takes two columns from source table and saves outputs in different table with match score etc. Following is the way we are doing it:
1. Load required data from table using OLEDB connection (source)
2. Sort the data
3. Apply Fuzzy grouping (using dedicated database instead tempdb and MinSimilarity = 0.6)
4. Send to destination table using OLEDB connection (destination)
In input table we have millions of records. It takes too long to execute and even sometime it fails after running 12 hours. Any suggestions for performance improvement are welcomed.
Appreciate your help.
- Ashish
|
|
|
|
|
Hi,
I need to convert the DateTime to a date format. I know I have to use Convert, but I don't think I use it right because I'm not getting the expected result.
Here's my scenarion: I want to do a sum for all products in the DB that were bought during a given period of time. My query looks like this
select convert(datetime,convert(char(10),OrderDate,101)), SUM(Qty)
from Orders
where convert(datetime,convert(char(10),OrderDate,101)) between '05/29/05' and '06/10/05'
group by convert(datetime,convert(char(10),OrderDate,101))
order by convert(datetime,convert(char(10),OrderDate,101))
the result looks like this:
OrderDate Qty
2005-05-30 00:00:00.000 1
2005-05-31 00:00:00.000 4
How can I get rid of the 0s at the end of OrderDate?
thanx
|
|
|
|
|
You can change your where clause to be:
where OrderDate >= '2005-05-29' and OrderDate <= '2005-06-10 23:59:59'
Hope that helps.
Ben
|
|
|
|
|
Kubben,
Thanks for the reply, but changing the where clause didn't work. I need to change the 'convert' part of the select in order to alter how the data is being displayed, but to what?
I tried casting it to char, and it partially fixed my problem:
select cast(OrderDate as char(11)), SUM(Qty)
from Orders
where convert(datetime,convert(char(10),OrderDate,101)) between '05/29/05' and '06/10/05'
group by cast(OrderDate as char(11))
order by cast(OrderDate as char(11))
The result is:
Jun 1 2005 2
Jun 2 2005 1
May 30 2005 1
but now the problem is that the ordering will be done alphabetically and not by date.
stuck again 
|
|
|
|
|
Hi
If you are viewing the data in a datagrid you can use the following data formating expression:
{0:dd/MM/yyyy} without the need to use the convert staement you are using
otherwise try this:
SET DATEFORMAT mdy
select CAST(OrderDate as varchar(11)) as OrderDate, SUM(Qty)
where OrderDate between '05/29/05' and '06/10/05'
group by OrderDate
order by OrderDate
Shahil
|
|
|
|
|
Shahil,
The 'order by' will do the sorting in alphabetical order, since now OrderDate is a string, so I'll get 'June' before 'May'.
I need to be able to still sort by date.
thanx
|
|
|
|
|
okay,
I think I found a way around this: add a dummy column and sort by that
select cast(OrderDate as char(11)), SUM(Qty),
convert(datetime,convert(char(10),OrderDate,101))
from Orders
where convert(datetime,convert(char(10),OrderDate,101)) between '05/29/05' and '06/10/05'
group by cast(OrderDate as char(11)), convert(datetime,convert(char(10),OrderDate,101))
order by convert(datetime,convert(char(10),OrderDate,101))
|
|
|
|
|
Hi kozu
try this:
create table #tmp
(
OrderDate datetime,
qty int
)
insert into #tmp(orderdate, qty)
select OrderDate, sum(qty)
from orders
where OrderDate between '05/29/80' and '06/10/05'
group by OrderDate
order by OrderDate
select CAST(OrderDate as varchar(11)) as OrderDate, Qty
from #tmp
DROP TABLE #tmp
Shahil
|
|
|
|
|
Form your result set then use it as the source for the final query where you can format your data.
SELECT
CONVERT(VARCHAR(20),OrderDate,101) AS OrderDate,
TotalQty
FROM
(
SELECT
CAST(CONVERT(VARCHAR(20),OrderDate,101) AS DATETIME) AS OrderDate,
SUM(Qty) AS TotalQty
FROM
Orders
WHERE
CAST(CONVERT(VARCHAR(20),OrderDate,101) AS DATETIME) between '05/29/05' and '06/10/05'
GROUP BY
CAST(CONVERT(VARCHAR(20),OrderDate,101) AS DATETIME)
) AS subqry
ORDER BY
OrderDate
Speed wise, I think kubben is correct. I would remove the CAST/CONVERT in the WHERE clause of the subquery with a little intelligent manipulation of the date ranges.
|
|
|
|
|
Hi guys
I'm working in Visual studio on a website and using ADO.NET to connect to my pubs sql-database. I keep on getting error messages when I test the website in my browser. Is there anyone who can tell me what could be wrong - I've been through all the configurations steps e.g setting the remote connections... at least I think so...
I've gotten error messages like "login failure" and "The user is not associated with a trusted SQL Server connection" what might be wrong???????????
The strange thing is that if I chooe e.g. a gridview from the toolbox there is no problem at all for this autogenerated gridview to connect to the server - how can this be? - I've tried to use the autogenerated connection string from the toolbox gridview, but that doesn't seem to help...
her is some connection string that I have tried out
string CONN_STRING = "Server=.\\SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI";
or
string CONN_STRING = "Server=.\\SQLEXPRESS;Initial Catalog=pubs;uid=sa;pwd=";
please someone help me...
All the best
Al
|
|
|
|
|
ComCoderCsharp wrote: how can this be?
Because Visual Studio is running in your account. The web application is running in the ASPNET account - You have to tell SQL Server about this by adding ASPNET as a windows authenticated login and giving it permission to use the appropriate database.
|
|
|
|
|
Hi thanks for the reply
Maybe this could be the solution.. but it leads me to another question: How do I add windows authenticated login and give permission to the appropriate database, I can't seem to figure it out...sorry if this is a stupid question, I'm kind of new to configuring the database connections...
Thanks in advance
Al
|
|
|
|
|
In SQL Server 2000 use the Enterprise manage. In the security tab for the server you can add the log in. In the users tab for the individual database you can then set up access to the database and the objects it contains.
In SQL Server 2005 there will be a similar mechanism in the SQL Server Management Studio.
|
|
|
|
|
Hi,
I need to check if a field exists in a certain table (in an SQL database), and if not then add that field to the table.
For Instance I have Table "A" that should contain field "a". So I want to check if field a is already defined for the table, and if not then add it.
How can I d o this? Is there an SQL statement to check if a field exists?
Thanks
|
|
|
|
|
SWDevil wrote: Is there an SQL statement to check if a field exists?
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'MyColumnName')
BEGIN
ALTER TABLE ....
END
|
|
|
|
|
If I need to add more than one fields can I write than one ALTER statements:
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName' AND COLUMN_NAME = 'MyColumnName')
BEGIN
ALTER TABLE ....
ALTER TABLE ...
ALTER TABLE ...
ALTER TABLE ...
END
|
|
|
|
|
You can write as many ALTER TABLE statements as you like. Or you can put it all in one ALTER TABLE statement. It is up to you.
|
|
|
|
|
I have a single table with many phone numbers per person, i.e. person data is repeated for each phone number for that person. I while ago I normalised this using SQL updates, but I can't remember how I did it and maybe here someone can give me a better way anyway. The original table has no PK, and my first step is to get all unique person data, using distinct, into a new Person table. I then want to extract phone number columns from each record, and add a column to tell which phone record belongs to which Person record.
My problem is, using distinct to get unique Person data precludes including any ID unique to a Phone record, and I have to real columns that I can use to pair new Person records with Person records in the source table except comparing all columns, and some are null, so this is out. Any suggestions?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
