getting a deleted ids froma field

hi i need to get deleted dis froma table say customers id where in we have 1000 records and we have randomly deleted 100 records i want the ids of those randomly deleted recodsTry http://www.lumigent.com Log explorer
or if it was numeric column, you can guess IDs by this query
select
FreeRange=
case when (h.ID+1)=(min(l.Id)-1)
then convert(varchar(20),h.ID+1)
else isnull(convert(varchar(20),h.ID+1),'?')+'..'+isnul l(convert(varchar(20),min(l.Id)-1),'?')
end
,"Count"=isnull(convert(varchar(20),min(l.Id)-1-(h.ID+1)+1),'?')
from
(
select yt.ID
from YourTable yt
where not exists(select 'x' from YourTable ytH where yt.ID=ytH.ID-1)
) h
full join
(
select yt.ID
from YourTable yt
where not exists(select 'x' from YourTable ytL where yt.ID=ytL.ID+1)
) l on h.ID<l.Id
group by h.ID
order by h.ID|||see www.nigelrivett.com
Find gaps in sequence numbers|||see www.nigelrivett.com
Find gaps in sequence numbers|||nigelrivett's query

select convert(varchar(10),imin.ID) + ' - ' + convert(varchar(10)
,
(
select min(ID)
from
(
select ID
from YourTable
where not exists
(
select *
from YourTable a2
where YourTable.ID-1 = a2.ID
)
and YourTable.ID <> (select min(ID) from YourTable)) as imax where imax.ID > imin.ID)
)
from
(
select ID
from YourTable
where not exists
(
select *
from YourTable a2
where YourTable.ID+1 = a2.ID
)
and YourTable.ID <> (select max(ID) from YourTable)
) as imin

But it was a little slow (7s/4000 rows), I added index
create unique clustered index icx_YourTable on YourTable(ID ASC)
but it is still about 3s/4000 rows (By the way Index tuning wizard did not recomend any index !)
My query is about 1s/4000 rows with no index and I expect higher difference for larger dataset.

You said, you have 1000 rows, so time under 10 s is excelent for you.
I am speaking to terabyters :)

geting timeout error in application when inserting record

i currently have more tha 3 million of records in my table in sql 7. i am getting timeout error in my web application when i try to insert a record in that table
what could be rhe reason for this? how to avoid this type of problem?
Thanks in advanceis it a single record insert or an insert select? how many indices do you have on the table? have you run a trace and looked at the duration column for the steps in the process? Do you want to post your code?sql

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!sql

GetDate not working

Hi,
I have a very simple query as follows. When I run, it returns no records. I
know there are records that should be in th result. If I insert today's
date instead of getdate() I get all the records for today's date. Getdate()
works if I use > or < instead of =. Any idea why is this behavior? Or is
there another way of accomplishing this.
Select COURSE_NBR as ItemValue
FROM ED_COURSE_CL_1
Where CLASS_DATE = GETDATE()Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
you think match the exact point in time when you run the query? The way to
do this is to use a range query, as you have already discovered. >= {date}
AND < {date + 1} ...
A
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records.
> I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date.
> Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||It is because datetime include both a date and a time portion. See
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records. I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date. Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||Aaron,
Select getdate() is working good and it returns todays date. For today's
date in my query should return one record, which I can verify it by inserting
today's date instead of getdate(). How can I build a range query to only get
records with today's date?
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
> like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
> you think match the exact point in time when you run the query? The way to
> do this is to use a range query, as you have already discovered. >= {date}
> AND < {date + 1} ...
> A
>
> "Shan" <Shan@.discussions.microsoft.com> wrote in message
> news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> > Hi,
> > I have a very simple query as follows. When I run, it returns no records.
> > I
> > know there are records that should be in th result. If I insert today's
> > date instead of getdate() I get all the records for today's date.
> > Getdate()
> > works if I use > or < instead of =. Any idea why is this behavior? Or is
> > there another way of accomplishing this.
> >
> > Select COURSE_NBR as ItemValue
> > FROM ED_COURSE_CL_1
> > Where CLASS_DATE = GETDATE()
> >
> >
>
>|||> Select getdate() is working good and it returns todays date.
Correction : it returns today's date AND TIME.
> How can I build a range query to only get
> records with today's date?
DECLARE @.today SMALLDATETIME;
SET @.today = DATEDIFF(DAY, 0, GETDATE());
SELECT
...
WHERE DateColumn >= @.today
AND DateColumn < (@.today + 1);|||Thanks Aaron it's working great.
Cheers!!!
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Select getdate() is working good and it returns todays date.
> Correction : it returns today's date AND TIME.
> > How can I build a range query to only get
> > records with today's date?
> DECLARE @.today SMALLDATETIME;
> SET @.today = DATEDIFF(DAY, 0, GETDATE());
> SELECT
> ...
> WHERE DateColumn >= @.today
> AND DateColumn < (@.today + 1);
>
>
>

Get top 3 records for each ...

to relate my question to the pubs DB...

I want to return up to 3 titles for each publisher. The criteria

So if a publisher only has
1 title = return 1
2 titles = return 2
3 titles = return 3
4 titles = return only 3
>4 titles = return only 3

To make it more interesting, lets return the first 3 alphabetically as well...

ThanksTry this:

select * from titles a
where title_id in
(select top 3 title_id
from titles b
where a.pub_id = b.pub_id)
order by pub_id|||/*
JUST A LITTLE ADJUSTED
*/

select
case when t.title_id=(
select min( t3.title_id)
from titles t3
where t3.pub_id = t.pub_id and t3.title=(select min(t4.title) from titles t4 where t4.pub_id = t3.pub_id)
)
then p.pub_name else '' end
,t.title
from titles t
join publishers p on t.pub_id=p.pub_id
where title_id in
(
select top 3 t2.title_id
from titles t2
where t2.pub_id = t.pub_id
order by t2.title
)
order by p.pub_name,t.title

Get top 20 from aggregated records??

Hello
I'm using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows NT 5.0 (Build 2195: Service Pack 4)
I have a big problem getting the right result from the this example
table...
AgentID | Destination
----
10 | BKK
10 | BKK
10 | BKK
10 | LON
10 | BEG
10 | BEG
10 | SJJ
10 | NYC
96 | BKK
96 | BKK
96 | BKK
96 | BKK
96 | BKK
96 | LON
96 | LON
96 | LON
96 | LON
96 | BEG
96 | BEG
96 | BEG
96 | SJJ
96 | SJJ
96 | MRU
96 | MRU
1000 | BKK
1000 | BKK
1000 | BKK
1000 | LON
1000 | BEG
1000 | HAN
1000 | HKG
1000 | ZAG
1000 | BLX
The result I need to get out of this table is top 5 destinations for
every agent id used here. Can someone please help with this?
Thx guys.
Miroslav Ostojicyou mean top 5 distinct destinations?
or just top 5. If just top5, then in what order?|||If you are searching for distinct destination, then you can do it this way..
select a.agentid, a.Destination, count(distinct b.Destination)
as con from tbl1 a, tbl1 b
where a.agentid = b.agentid and a.Destination <= b.Destination
group by a.agentid, a.Destination
having count(distinct b.destination) <= 5
order by a.agentid|||You left everyone guessing about a lot of things, particularly what
you are ranking on to determine what is top.
My guess is that this query
SELECT AgentID, Destination, count(*) as rows
FROM Example
GROUP BY AgentID, Destination
ORDER BY 1, 3 desc
which counts how many occurances of each Destination there are for an
AgentID, might be the basis for the ranking. If that is correct you
might see what this does for you:
SELECT AgentID, Destination, count(*) as rows
FROM Example
WHERE Destination IN
(SELECT TOP 5 Destination
FROM Example as X2
WHERE X1.AgentID = X2.AgentID
GROUP BY Destination
ORDER BY count(*) desc)
GROUP BY AgentID, Destination
ORDER BY AgentID, count(*) desc
Roy Harvey
Beacon Falls, CT
On 7 Jun 2006 02:02:06 -0700, miroslav.ostojic@.gmail.com wrote:

>Hello
>I'm using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
>14:22:05
>Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
>Windows NT 5.0 (Build 2195: Service Pack 4)
>I have a big problem getting the right result from the this example
>table...
> AgentID | Destination
>----
> 10 | BKK
> 10 | BKK
> 10 | BKK
> 10 | LON
> 10 | BEG
> 10 | BEG
> 10 | SJJ
> 10 | NYC
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | BEG
> 96 | BEG
> 96 | BEG
> 96 | SJJ
> 96 | SJJ
> 96 | MRU
> 96 | MRU
> 1000 | BKK
> 1000 | BKK
> 1000 | BKK
> 1000 | LON
> 1000 | BEG
> 1000 | HAN
> 1000 | HKG
> 1000 | ZAG
> 1000 | BLX
>
>The result I need to get out of this table is top 5 destinations for
>every agent id used here. Can someone please help with this?
>Thx guys.
>Miroslav Ostojic|||There is no field available in your table to order by.
You need to have some field by which we can sort it.
"miroslav.ostojic@.gmail.com" wrote:

> Hello
> I'm using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
> 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
> Windows NT 5.0 (Build 2195: Service Pack 4)
> I have a big problem getting the right result from the this example
> table...
> AgentID | Destination
> ----
> 10 | BKK
> 10 | BKK
> 10 | BKK
> 10 | LON
> 10 | BEG
> 10 | BEG
> 10 | SJJ
> 10 | NYC
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | BKK
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | LON
> 96 | BEG
> 96 | BEG
> 96 | BEG
> 96 | SJJ
> 96 | SJJ
> 96 | MRU
> 96 | MRU
> 1000 | BKK
> 1000 | BKK
> 1000 | BKK
> 1000 | LON
> 1000 | BEG
> 1000 | HAN
> 1000 | HKG
> 1000 | ZAG
> 1000 | BLX
>
> The result I need to get out of this table is top 5 destinations for
> every agent id used here. Can someone please help with this?
> Thx guys.
> Miroslav Ostojic
>

Get Today's Records

Hello, I have a query that needs to run daily and only collect records for
that day's activity. The table has a date/time field called TranDateSold.
Could someone please advise the proper syntax for the following logic:
SELECT * FROM tablename
WHERE TranDateSold (is today only)
I experimented with GETDATE() but it seemed to want to match today's date
and time to the hour and minute. Any ideas would be most appreciated.Hi,
Strip the time portion from the date returned by getdate() function before
comparison. Something like this:
select *
from tablename
where trandatesold = convert(vachar(8), getdate(), 112)
This assumes that the time is already zero-ed in trandatesold column.
hth,
Dean
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:0BA6C334-70D4-4637-8528-1B838763F5DD@.microsoft.com...
> Hello, I have a query that needs to run daily and only collect records for
> that day's activity. The table has a date/time field called TranDateSold.
> Could someone please advise the proper syntax for the following logic:
> SELECT * FROM tablename
> WHERE TranDateSold (is today only)
> I experimented with GETDATE() but it seemed to want to match today's date
> and time to the hour and minute. Any ideas would be most appreciated.|||Try this
SELECT * FROM tablename
WHERE TranDateSold = DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||> Hello, I have a query that needs to run daily and only collect records for
> that day's activity. The table has a date/time field called TranDateSold.
If there can be nothing in the future:
DECLARE @.dt SMALLDATETIME
SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
SELECT <col_list> FROM tablename
WHERE TranDateSold >= @.dt;
If there may be future-dated rows:
SELECT <col_list> FROM tablename
WHERE TranDateSold >= @.dt
AND TranDateSold < @.dt + 1;
A|||Oops, I goofed the = should be >=|||This will only work if TranDateSold is intentionally stored with no time
value. Otherwise you will be asking for rows where '2006-04-21 13:26' =
'2006-04-21 00:00';
If you have to do the convert on the left-hand side to get rid of the time
component, you've just wiped out any chance of using an index.
http://www.aspfaq.com/2280
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145649232.038005.231380@.g10g2000cwb.googlegroups.com...
> Try this
> SELECT * FROM tablename
> WHERE TranDateSold = DATEADD(d, DATEDIFF(d, 0, GETDATE())+0, 0)
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||Thanks to everyone for your posts. The convert function is what I needed fo
r
my vendor, and the DECLARE stmt gave me all records after midnight today.
Have a nice wend, Pancho.
"Aaron Bertrand [SQL Server MVP]" wrote:

> If there can be nothing in the future:
> DECLARE @.dt SMALLDATETIME
> SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
> SELECT <col_list> FROM tablename
> WHERE TranDateSold >= @.dt;
> If there may be future-dated rows:
> SELECT <col_list> FROM tablename
> WHERE TranDateSold >= @.dt
> AND TranDateSold < @.dt + 1;
> A
>
>

get todays date and a certain time

Hi Everyone,

I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Thanks in advance,
AnnaYou can use DATEADD, for example:

SELECT DATEADD(hour,14,CONVERT(CHAR(10),getdate(),102))

Razvan

AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

Hi Everyone,
>
I am trying to write something to give me back all the data for a
sertain time range for today.
So for example: I need to get all records where change_date is <= today
2pm and today at 8pm.
I know i can get just the date for today by using
CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?
>
Thanks in advance,
Anna

|||On 27 Sep 2006 11:36:59 -0700, AKorsakova@.gmail.com wrote:

Quote:

Originally Posted by

>Hi Everyone,
>
>I am trying to write something to give me back all the data for a
>sertain time range for today.
>So for example: I need to get all records where change_date is <= today
>2pm and today at 8pm.
>I know i can get just the date for today by using
>CONVERT(CHAR(10),getdate(),102) but can i add a time range to that?

Hi Anna,

Use either

CONVERT(datetime, CONVERT(CHAR(10), getdate(), 126) + 'T14:00:00')

or

DATEADD(day, DATEDIFF(day, 0, getdate()), '14:00:00')

to get current date with a time of 2PM.

--
Hugo Kornelis, SQL Server MVP

Get the recent records

i have a datetime field in the post tables.

I would like to get the records within the latest 7 days.

Are there any functions for doing something like this?

my current query is something like

select * from post where creation_time ...

Thank you

try something like this:

create

table #test(datedatetime)

insert

into #test

values

('01/01/2007')

insert

into #test

values

('02/01/2007')

insert

into #test

values

('02/04/2007')

select

*from #test

where

date>dateadd(day,-7,getdate())

drop

table #test

I think that it will point you in correct direction, or maybe it is your solution?

|||

Could you not just do...

select

*from post

where

creation_time >dateadd(day,-7,getdate())

jpazgier, i am not following the reason for creating the additional table.

|||

I just try to provide working example in my answer so I created temporary table with my test data to show that it works and for future testing.

But in this case my example only points your how you can try to solve problem, you maybe would like to take care about not only day but also minutes?
This example if you run it at 12:31 today will show records inserted after 12:31 7 days ago so records inserted at 12:30 will be not visible and maybe author of the post would like to take care about this himself, I do not know if time of the day is important for him or not.

Thanks

|||Both answers are great!|||

Both answers are great!

Thank you

get the number of days it has been since a record was inserted

Hi

when inserting records into a table one of the fields is a date field. I am using the GETDATE() function to insert the date as the record is being inserted.

when i retrieve an entire record from the table i want to be able to select this date, but also to get the number of days it has been since that record was inserted.
eg: 3 days

if the record was inserted less than one day ago (<24 hrs ago) i would like it to return the number of hours.
e.g. 22 hrs

i dont want hours to be displayed if the days is >= 1.

please can anyone guide me with this?

thanks!

use the query like this

Declare @.MyVarasDateTime

Set @.Myvar='22/05/2007'

Select'satya', MyTime=

CASE

WHENDATEDIFF(hh,@.Myvar,GetDate())> 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate()))+' days'

ELSE

Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate()))+' hours'

END

Use the appropriate fields according to your database and tables

|||

Thanks Satya, this was really useful. Can you help me modify this so that it returns 1 day and 1 hour instead of 1 days and 1 hours

Appreciate the help!

|||

Sure change the code where its + "days" or + "hours"

1Declare @.MyVaras DateTime23Set @.Myvar='22/05/2007'45Select'satya', MyTime=67CASE8WHENDATEDIFF(hh,@.Myvar,GetDate()) > 23THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'910ELSE1112Convert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'1314END1516

|||

Sorry, i dont think i explained what i meant properly...

I need it to say 'days' and 'hours' all the time but the only exceptions are when days = 1 and when hours = 1...in them cases it should say 1 day and 1hour.

so as an example it could out the following :

11 days

21 days

1 day

...and

22 hours

6 hours

1 hour.

Thanks again!

|||

Declare @.MyVaras DateTime Set @.Myvar='05/22/2007'Select'satya', MyTime=CASEWHENDATEDIFF(hh,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hour'WHENDATEDIFF(hh,@.Myvar,GetDate()) < 23THENConvert(varchar(10),DATEDIFF(hh,@.Myvar,GetDate())) +' hours'WHENDATEDIFF(d,@.Myvar,GetDate()) < 2THENConvert(varchar(10),DATEDIFF(d,@.Myvar,GetDate())) +' day'WHENDATEDIFF(d,@.Myvar,GetDate()) > 1THENConvert(varchar(10),DATEDIFF(dd,@.Myvar,GetDate())) +' days'END

|||

Great, worked perfectly :)

Thanks.

|||

You are welcome... Answer it if solved

get the list of records for last registered emails

hello,
i have a table for example mytable with 2 fields
email (varchar50) regdate(datetime)
i want to have a list of emails which are more times registered - sort by
last time when registered
example of entries in the table
u1@.dom1.com 26.03.2006 15:12:02
u2@.dom1.com 24.03.2006 15:12:02
u3@.dom1.com 24.03.2006 14:12:02
u1@.dom1.com 23.03.2006 13:12:02
u2@.dom1.com 22.03.2006 12:12:02
u1@.dom1.com 21.03.2006 11:12:02
u2@.dom1.com 20.03.2006 12:12:02
u2@.dom1.com 19.03.2006 12:12:02
i want to get something like
3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
time
4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
u3 - is not listed because it is only one time registered
the information is sort desc by last registration time
Yes i know what you think about the "tabledesign..." but my customer has
such a table - and he asked me for that information:The information what
will result is then inserted in a new table...
thanksTry:
select
count (*)
, email
, max (regdatetime) regdatetime
group by
email
having
count (*) > 1
order by
regdatetime desc
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:B43ACCC2-9ADA-4B0B-B8E3-4DFDA2694567@.microsoft.com...
hello,
i have a table for example mytable with 2 fields
email (varchar50) regdate(datetime)
i want to have a list of emails which are more times registered - sort by
last time when registered
example of entries in the table
u1@.dom1.com 26.03.2006 15:12:02
u2@.dom1.com 24.03.2006 15:12:02
u3@.dom1.com 24.03.2006 14:12:02
u1@.dom1.com 23.03.2006 13:12:02
u2@.dom1.com 22.03.2006 12:12:02
u1@.dom1.com 21.03.2006 11:12:02
u2@.dom1.com 20.03.2006 12:12:02
u2@.dom1.com 19.03.2006 12:12:02
i want to get something like
3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
time
4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
u3 - is not listed because it is only one time registered
the information is sort desc by last registration time
Yes i know what you think about the "tabledesign..." but my customer has
such a table - and he asked me for that information:The information what
will result is then inserted in a new table...
thanks|||Tom it works perfect ...
thanks for your help
"Tom Moreau" wrote:

> Try:
> select
> count (*)
> , email
> , max (regdatetime) regdatetime
> group by
> email
> having
> count (*) > 1
> order by
> regdatetime desc
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:B43ACCC2-9ADA-4B0B-B8E3-4DFDA2694567@.microsoft.com...
> hello,
> i have a table for example mytable with 2 fields
> email (varchar50) regdate(datetime)
> i want to have a list of emails which are more times registered - sort by
> last time when registered
>
> example of entries in the table
> u1@.dom1.com 26.03.2006 15:12:02
> u2@.dom1.com 24.03.2006 15:12:02
> u3@.dom1.com 24.03.2006 14:12:02
> u1@.dom1.com 23.03.2006 13:12:02
> u2@.dom1.com 22.03.2006 12:12:02
> u1@.dom1.com 21.03.2006 11:12:02
> u2@.dom1.com 20.03.2006 12:12:02
> u2@.dom1.com 19.03.2006 12:12:02
>
> i want to get something like
> 3 u1@.dom1.com 26.03.2006 15:12:02 <- three times registered - last
> time
> 4 u2@.dom1.com 24.03.2006 15:12:02 <- four times registered - last time ...
> u3 - is not listed because it is only one time registered
>
> the information is sort desc by last registration time
> Yes i know what you think about the "tabledesign..." but my customer has
> such a table - and he asked me for that information:The information what
> will result is then inserted in a new table...
> thanks
>

Get the last 100 records

My sql database table gets filled automatically.

Every record gets a current date/time stamp.

I want to select the last 100 records, ordered by the date/time stamp.

The newest records should be the last record in the 100 recordset.

How can I do this?

select id, createdon from
(select top 100 id, createdon
from table
order by createdondesc) a
order by createdon

|||

I get an incorrect syntax error on the last ")":

SELECT

DT, VALUE

FROM

(SELECTTOP 10

DT, VALUE

FROM [CAS SHORT HISTORY]

ORDERBY DTDESC)

|||

Make sure you've given the derived table an alias. Here's a working example:

declare @.table1table (idint identity (1,1), createdondatetime)declare @.startdatedatetimedeclare @.enddatedatetimeset @.startdate ='20060101'set @.enddate ='20070101'while @.startdate < @.enddatebegininsert @.table1values (@.startdate)set @.startdate = @.startdate + 1endselect id, createdonfrom (select top 100 id, createdonfrom @.table1order by createdondesc) aorder by createdon

|||

Hi ca8msm,

You are filling a new table, but I'm having a table [CAS SHORT HISTORY] that is already filled, how should I create an alias for this table?

|||

The above is just an example table. Use the query at the bottom and change the table and field names.

|||

select id, createdonfrom
(select top 100 id, createdon
from @.table1
order by createdondesc) a
order by createdon

What is the "a" doing?

|||

It's creating an alias for the derived table.

|||

Bingo! Found it!

SELECT

*

FROM(SELECTTOP 10

DT

FROM

[CAS SHORT HISTORY] CASALIAS

ORDERBY

DT

DESC)

CASALIAS

ORDERBY

DT

ASC

Get records count from SQL cursor

Hi experts,
I have created a SQL cursor for records processing in a stored procedure. I
probably can use the @.@.Cursor_Rows function in order to obtain total rows of
record contained inside the cursor. But once I declare the cursor as
FAST_FORWARD, it always return me -1. I need to declare the cursor as
FAST_FORWARD as it really helps in tuning the performance. Else my stored
procedure will take longer time to execute.
Any other way I can use to get the total records being returned? I've tried
to signal another SQL statement to perform the records count but this seems
to create redundant overhead. I believe if I can do anything to existing
cursor without having extra Select Count statement, it would help to reduce
unnecessary processing and shorten the overall time required.
Really appreciate for any advice or suggestion. Thanks a lot.Can you test perfoermance between your current cursor declaration and the on
e
I am posting?
declare my_cursor cursor
local
forward_only
static
read_only
for
...
AMB
"LBT" wrote:

> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've trie
d
> to signal another SQL statement to perform the records count but this seem
s
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to reduc
e
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||If performance is a concern for you then how about getting rid of the
cursor altogether? Cursors are rarely a good idea.
David Portas
SQL Server MVP
--|||Thanks for the suggestion. The time taken is still approximately equal to th
e
cursor which is declared without those keywords. And I can't declare the
cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I forgo
t
to write out this concern in my previous post).
"Alejandro Mesa" wrote:
> Can you test perfoermance between your current cursor declaration and the
one
> I am posting?
> declare my_cursor cursor
> local
> forward_only
> static
> read_only
> for
> ...
>
> AMB
>
> "LBT" wrote:
>|||You can use select count(*)... yada,,, the optimizer can use the index
entries instead of having to read the data ( if there is a supporting
index.)
You can also use select @.@.rowcount AFTER the query...
You have to open the cursor before the rowcount info is available for
cursors...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"LBT" <LBT@.discussions.microsoft.com> wrote in message
news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
> Hi experts,
> I have created a SQL cursor for records processing in a stored procedure.
> I
> probably can use the @.@.Cursor_Rows function in order to obtain total rows
> of
> record contained inside the cursor. But once I declare the cursor as
> FAST_FORWARD, it always return me -1. I need to declare the cursor as
> FAST_FORWARD as it really helps in tuning the performance. Else my stored
> procedure will take longer time to execute.
> Any other way I can use to get the total records being returned? I've
> tried
> to signal another SQL statement to perform the records count but this
> seems
> to create redundant overhead. I believe if I can do anything to existing
> cursor without having extra Select Count statement, it would help to
> reduce
> unnecessary processing and shorten the overall time required.
> Really appreciate for any advice or suggestion. Thanks a lot.|||I performed the following test but @.@.rowcount return me 0. Please check for
me if there is something wrong with the code. Thanks
---
declare @.temp varchar(50)
declare my_cursor cursor fast_forward for
select column01 from table01
open my_cursor
select @.@.rowcount
fetch my_cursor into @.temp
while @.@.fetch_status = 0
begin
print @.temp
fetch my_cursor into @.temp
end
close my_cursor
deallocate my_cursor
----
--
"Wayne Snyder" wrote:

> You can use select count(*)... yada,,, the optimizer can use the index
> entries instead of having to read the data ( if there is a supporting
> index.)
> You can also use select @.@.rowcount AFTER the query...
> You have to open the cursor before the rowcount info is available for
> cursors...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "LBT" <LBT@.discussions.microsoft.com> wrote in message
> news:2A03FED0-12BB-48CE-A8B0-1B5706BC9439@.microsoft.com...
>
>|||I do agree that cursor is definately not a good idea to be used...I did thin
k
of using table variable previously but since I need to use dynamic SQL and
table variable is not supported to be used together with dynamic SQL...any
other way to get rid of cursor? Will temp table use the equivalent overhead
as cursor? I think i need to temporarily store records (based on passed-in
filtering criteria) to somewhere else so that i'm able to loop the records
and perform further analysis. My stored procedure will return one summarized
result based on passed-in filtering criteria.
"David Portas" wrote:

> If performance is a concern for you then how about getting rid of the
> cursor altogether? Cursors are rarely a good idea.
> --
> David Portas
> SQL Server MVP
> --
>|||> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
Who said that?
Example:
use northwind
go
declare @.sql nvarchar(4000)
declare @.c cursor
set @.sql = N'set @.c = cursor local forward_only static read_only for select
orderid, orderdate from orders where orderdate >= ''19960101'' and orderdate
< ''19970101''; open @.c'
execute sp_executesql @.sql, N'@.c cursor output', @.c output
if cursor_status('variable', '@.c') >= 0
begin
print @.@.cursor_rows
close @.c
deallocate @.c
end
go
I am not advocating for cursors.
AMB
"LBT" wrote:
> Thanks for the suggestion. The time taken is still approximately equal to
the
> cursor which is declared without those keywords. And I can't declare the
> cursor as local as I'm creating the cursor using dynamic SQL (Sorry, I for
got
> to write out this concern in my previous post).
>
> "Alejandro Mesa" wrote:
>|||Your reply just poses more questions upon questions. Why dynamic SQL?
Dynamic SQL, like cursors, is something you should aim to avoid, or at
least minimize. Why "loop the records"? Or for that matter, why
"temporarily store records"? (BTW the term "rows" is generally
preferred to "records" in RDBMS).
In short, the best way to get help is to describe *what* you want to do
rather than *how* you think you should go about it. Tell us what your
goal is and show us your data structure as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||>> My stored procedure will return one summarized result based on
passed-in filtering criteria. <<
Get a **basic** software engineering book and look up coupling and
cohesion. In a properly designed module, you do not pass in criteria.
That module would have no cohesion. This is far more basic than SQL;
this is how to be any kind of programmer.
Let's go back to square one and find out what you are trying to do and
then we can look for a set-based, relational approach that will give
you a maintainable procedure. You might also want to consider taking
college courses on software engineering, then learn databasess and data
modeling.

Get records count from SQL cursor

Have you thought of declaring a int then adding one to it
per record ?
Peter
"Status quo, you know, that is Latin for "the mess we're
in."
Ronald Reagan

>--Original Message--
>Hi experts,
>I have created a SQL cursor for records processing in a
stored procedure. I
>probably can use the @.@.Cursor_Rows function in order to
obtain total rows of
>record contained inside the cursor. But once I declare
the cursor as
>FAST_FORWARD, it always return me -1. I need to declare
the cursor as
>FAST_FORWARD as it really helps in tuning the
performance. Else my stored
>procedure will take longer time to execute.
>Any other way I can use to get the total records being
returned? I've tried
>to signal another SQL statement to perform the records
count but this seems
>to create redundant overhead. I believe if I can do
anything to existing
>cursor without having extra Select Count statement, it
would help to reduce
>unnecessary processing and shorten the overall time
required.
>Really appreciate for any advice or suggestion. Thanks a
lot.
>.
>I've tested to append 1 to an int variable each time while looping the curso
r
but the effect is not so significant to boost the performance...Anyway,
thanks for the suggestion...
"Peter The Spate" wrote:

> Have you thought of declaring a int then adding one to it
> per record ?
> Peter
> "Status quo, you know, that is Latin for "the mess we're
> in."
> Ronald Reagan
>
> stored procedure. I
> obtain total rows of
> the cursor as
> the cursor as
> performance. Else my stored
> returned? I've tried
> count but this seems
> anything to existing
> would help to reduce
> required.
> lot.
>

get records count

i have this function

it return 0 but the sql statement in the sql query return the right number?how is that

i want to get the number of records any other idea or fix?

PublicFunction UserAlbumPhotoQuota(ByVal userIDAsInteger)AsBoolean

Dim ConnAsNew SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)

Dim strSQLAsString

Dim drAs SqlDataReader

strSQL ="SELECT *, (select count(*) from userAlbumPic where userID=" & userID &") as rec_count from userAlbumPic "

Dim cmdAsNew SqlCommand()cmd =New SqlCommand(strSQL, Conn)

Conn.Open()

dr = cmd.ExecuteReader()

dr.Read()

userQuota = dr("rec_count").ToString

Conn.Close()

EndFunction

PublicFunction UserAlbumPhotoQuota(ByVal userIDAsInteger)AsBoolean
Dim ConnAsNew SqlConnection(ConfigurationManager.ConnectionStrings("Conn").ConnectionString)
Dim strSQLAsString ="select count(*) from userAlbumPic where userID=" & userID
Dim cmdAsNew SqlCommand()cmd =New SqlCommand(strSQL, Conn)
Conn.Open()
userQuota = cmd.ExecuteScalar()
Conn.Close()
EndFunction

Jos

|||

i don't know why it returns 0 ? ?

|||try this:strSQL = "SELECT *, rec_count from userAlbumPic PICSleft join (select userID, count(*) rec_count from userAlbumPic group by userID) usercountson PICS.userID = userCounts.userID"I did not test it but it should work|||

Check whether userID is having the value you are expecting.

Jos

get records after executing a stored procedure

Hi All,

I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.

EXEC [dbo].[ETLloadGROUPS]
@.countRun =?,
@.startTime =?,
@.endTime = ?

This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.

When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.

Am I missing something here ? Please Advice

Thanks in Advance

I am sure it is a type issue. SSIS has a VERY VERY irritating feature of not telling you it can't convert your var to the SQL type you set in the parameters section, it just ignores it and sets it to nothing.

Try setting your vars to "String" types and your parameters in the task to "VARCHAR". I bet it will work.

You might also try setting vars inside the SQL to the ?. I have had issues where it doesn't like ? in certain places.

DECLARE @.count INT, @.stime datetime, @.etime datetime
SET @.count = ?
SET @.stime = ?
SET @.etime = ?

EXEC [dbo].[ETLloadGROUPS]
@.countRun =@.count,
@.startTime =@.stime,
@.endTime = @.etime|||

Tom,

Thanks for the quick response. but guess am into a soup here... I have done the following in the parameters tab of the Execute SQL Task.

varName Direction Datatype ParaName

user::countRun Input varchar 0

user:endDate input varchar 1

user:runDate Input varchar 2

In the result set , I have done the following,

Result Name variable Name

0 user::countRun

1 user:endDate

2 user:runDate

al the three variables are of the datatype String.

when I execute the package its now failing with the error, the type of the value assigned to the variable differs from the current datatype. I guess the values from the table which are int and date are not accepted in this parameter mapping. How to handle this?

Thanks for the help so far

|||It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

|||

When you say "no rows are fetched" how are you determining this?

what are you doing with the results of the sqltask?

|||

Hi Jeff,

My whole idea is to query a table, get three values onto three variables, pass these values to a stored procedure and then get the entire set of records returned by the proc and then insert it to another OLE DB Destination.

|||

Tom Phillips wrote:

It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

I am selecting all the three values from a table and then passing them to a stored procedure.

|||

Hi All,

I have solved all the issues, now at the last summit though its giving me an error with the OLE DB Source component where am calling the stored proc. The erorr is, A rowset based on the SQL Command was not returned by the OLE DB Provider.

Any idea to resolve this ?

Thanks in advance.

|||adding "SET NOCOUNT ON" at the start of the stored proc resolved the issue. Thx for all the help :)

get record with latest date

if there are 2 records with different date

how to write query for --> get record with latest date

Hi,

use Order By clause.

For example,

Select top 1 * from YourTable Order By YourDateFieldName Desc

|||

Use something like this:

SELECT TOP 1 *
FROM MyTable
ORDER BY DateField DESC

Regards,
Martin

|||

Here is an example based on the NorthWinds database:

Select TOP 1 *from OrdersOrder by OrderDateDesc

|||

Here is another way to do it:

select *
from MyTable
where timestampfield = (select max(timestampfield)
from MyTable)