|
charleslau2855 wrote: anyone know how to search the date by "dd/mm/yyyy" in sql query!
select convert(varchar(10), getdate(),101)
"My advice to you is to get married. If you find a good wife, you will be happy; if not, you will become a philosopher." Socrates
|
|
|
|
|
albCode wrote: charleslau2855 wrote:
anyone know how to search the date by "dd/mm/yyyy" in sql query!
select convert(varchar(10), getdate(),101)
That does not "search". It formats.
The original question has a false lead in it by requesting a specific format. Date searches are format free because SQL Server does not store the date in a specific locale.
|
|
|
|
|
Use parameters then you don't need to worry about date formats and it reduces your worries about a SQL Injection attack. Also, if you are going to put a date into a SQL string it is better to use the ISO format which is yyyy-mm-dd becuase it is locale independent.
|
|
|
|
|
You have too many items in your GROUP BY clause. The GROUP BY clause should contain only the non-aggregate items that are also in the SELECT clause. If your GROUP BY clause contains more items then it will appear to be producing duplicates (although it isn't, it is just throwing away the information the distinguishes one row from the other).
|
|
|
|
|
Don't you think it is increadibly rude when someone asks a question, you then answer it and a little while later they delete the original question. That means there is now an orphaned answer to a question no one now knows. This really is NOT helpful to anyone searching the forum for the answer to a similar problem.
I really have to dispair sometimes - I really do.
|
|
|
|
|
Colin Angus Mackay wrote: I really have to dispair sometimes - I really do.
Keep your chin up Colin...you do a fine job around here (IMO).
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Colin Angus Mackay wrote: you then answer it and a little while later they delete the original question.
That is why I make the attempt to put the question into my reply. Then they can delete it all they want but their question still sits there in my reply :->
You will see a delete button on each of your posts. Press it. - Colin Angus Mackay
|
|
|
|
|
PaulC1972 wrote: That is why I make the attempt to put the question into my reply. Then they can delete it all they want but their question still sits there in my reply
That's a good idea. Especially since it looks like Colin is either arguing with or insulting himself right now.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Chris Meech wrote: Especially since it looks like Colin is either arguing with or insulting himself right now.
The problem comes when I argue with myself to such an extent that it becomes violent. It's not easy to hit yourself in the face with a chair - I always manage to duck out of the way in time.
|
|
|
|
|
LOL.
Note to self. Not a good idea to be standing behind Colin when he's arguing with himself. Be aware of flying chairs.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
Is it possible to use a table in a foreign database via ODBC in a MS SQL Server 2005 SELECT query. I have data in a legacy database that I would like to use with data within SQL Server 2005.
For example, with MS Access you can "Get External Data" and link to tables in other database sources. I would like to do something similar in MS SQL Server
Thanks
Steve Jowett
|
|
|
|
|
You could always link your other database in as a Linked Server.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi there,
I've got a Messages table which looks roughly like this:
SenderID (int)
RecipientID (int)
TimeStamp (datetime)
Body (varchar(512)
Now I'd like to do a select which gets me (RecipientID = 1) the first message (depending on the timestamp) of each sender. Example: Sender ID 5 has sent two messages, the recordset should contain the oldest message of this fella. Sender ID 6 has sent five messages, the recordset should contain the oldest message of this guy as well.
I thought this could be done by tying a DISTINCT to a column. I hoped I could do it somehow like this:
SELECT DISTINCT(SenderID), TimeStamp, Body FROM Messages WHERE RecipientID = 1 ORDER BY TimeStamp DESC
I was just guessing into the wild. Is there an easy way to do that?
Thanks in advance!
/matthias
I love deadlines. I like the whooshing sound they make as they fly by. [Douglas Adams]
|
|
|
|
|
Try
SELECT M.SenderId, M.TimeStamp, M.Body FROM Messages AS M
WHERE M.TimeStamp = (SELECT MIN(M1.TimeStamp) FROM Messages AS M1 WHERE M1.SenderId = M.SenderId)
|
|
|
|
|
Distinct won't do this because unless the timestamp was the same for both records, then you have two distinct records. What you could do is to select based on the MIN timestamp (there are may ways to do this).
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Assuming "Oldest" means been in the DB the Longest.
This is more complex than first meets the eye due to the fact that you need to tie in the Oldest TimeStamp with its cooresponding Body field.
SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body
FROM Messages T1
INNER JOIN (
SELECT MIN[TimeStamp] AS MINTimeStamp
,Body
FROM Messages
GROUP BY Body
)T2
ON T1.Body = T2.Body
AND T1.[TimeStamp] = T2.[TimeStamp]
WHERE RecipeintID = 1
GROUP BY T1.SenderID
I did this blind, without sql around, so forgive me if it is a little off. It should be pretty close to what you are looking for if I understood what you were trying to do.
SELECT T1.SenderID, MIN(TimeStamp) AS MINStamp, Body AS Body
FROM @Messages T1
INNER JOIN (
SELECT
GROUP BY SenderID
|
|
|
|
|
Assuming "Oldest" means been in the DB the Longest. And I understood what you are trying to do this should be pretty close. I did this without a SQL Server so forgive if a little off.
This is more complex than first meets the eye due to the fact that you need to tie in the Oldest TimeStamp with its cooresponding Body field.
SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body
FROM Messages T1
INNER JOIN (
SELECT MIN[TimeStamp] AS MINTimeStamp
,Body
FROM Messages
GROUP BY Body
)T2
ON T1.Body = T2.Body
AND T1.[TimeStamp] = T2.[TimeStamp]
WHERE RecipeintID = 1
GROUP BY T1.SenderID
|
|
|
|
|
Assuming "Oldest" means been in the DB the Longest. And I understood what you are trying to do this should be pretty close. I did this without a SQL Server so forgive if a little off.
SELECT T1.SenderID, T2.MIN([TimeStamp]), T2.MIN(Body) AS Body
FROM Messages T1
INNER JOIN (
SELECT MIN[TimeStamp] AS MINTimeStamp
,Body
FROM Messages
GROUP BY Body
)T2
ON T1.Body = T2.Body
AND T1.[TimeStamp] = T2.[TimeStamp]
WHERE RecipeintID = 1
GROUP BY T1.SenderID
|
|
|
|
|
I have impersonated a user for my project (C# ASP.NET) lets call him Mr:X.
For security reasons I want to connect to a database with another user Mr:Y.
I still want to use windows authentication and Integrated Security=SSPI.
Is this possible?
|
|
|
|
|
hi folks,
Iam working sql server 2005
Iam unable to change the already existing identity value.
Pls think a while for this,,,,,,i already tried
alter table "ALTER TABLE <table_name> ALTER COLUMN <column_name> IDENTITY (x,y)"
its urgent
thanks in advance
Rupa
|
|
|
|
|
DBCC CHECKIDENT (jobs, RESEED, 30)
I hope this will help to reset the identity value
|
|
|
|
|
Hi genius,
Nice to meet you here!
When developing BI programs with SQL Server 2005, I got this problem:
Users want the report should be exported in office excel 2003 format, and stored in PIVOT TABLE.
We have decided to use OLAP tech in SSAS(SQL Server2005 Analysis Services) to generate cubes.
If we just use excel as a client tool, and create a pivot table which connects to the cube database, it's ok.
But, the users want to download the excel file from a web page, further more , they want to filter the data through the web page.
How can I do?
Wait inline.
Thank you!
|
|
|
|
|
Hi,
Myself imran, I am quite new to dotnet. i am developing web application using asp.net and C#(Visual Studio 2005). i have defined connection string in web-config like <connectionstrings>
<add name="connstr" providername="System.Data.SqlClient" connectionstring="server=local;database=HFSERVICES;uid=sa;pwd=;">
Can any one help me to know How to Access database using this connetion string.
thanks
regards
imran khan
|
|
|
|
|
Welcome to The Code Project.
Please don't post the same question all over the place. Pick the forum that best fits your question.
---
b { font-weight: normal; }
|
|
|
|
|
Thank you for first Lesson.
regards
imran khan
|
|
|
|