DB access from master page

I am using content and master pages. The content page has to query the database to get the master page name (among other things), which is done in 'Page_PreInit', and then the master page has to query the database to get some layout options, done in 'Page_Load'.

Is it possible for the master page to use the existing open connection, or am I forced to close the connection in the content page and open up a new connection in the master page? I've tried various things without success.

The second question is, does it matter? Will opening and closing two connections be much slower than opening and closing one?

In my opinion you should open a connection as late as possible and close it as early as possible. So I think you should not use the same connection, instead close the one as soon as its work gets done (dispose it or use theusing block) and then create a new one for the other work.

HTH,

Vivek

|||

Thanks.

I've since done some reading up on connection pools, and I agree with what you are saying. I assumed that opening and closing a connection twice would require twice as much work, but this is not the case.

We live and learn.

|||Yes, connection pooling reduces the number of times that new connections need to be opened. The pooler maintains ownership of the physical connection. Every time a connection is open/closed, the pooler does not actually open/close a connection, thus improves performance. For details you can take a look at this article:Using Connection Pooling
And this article gives some tips on connection pooling performance:
Tuning Up ADO.NET Connection Pooling in ASP.NET Applications

DAY() not working with 'Left Join'?

I have two tables. Days (1-31) and dates (random dates)

If I have a query that is

Select Day, Date

From days LEFT JOIN dates ON days.Day = DAY(dates.date)

Order By Day, Date

The left join will not return all the days in days just the ones that join with dates. It returns as if I am doing and 'Inner join'. What do I need to do different?

Thanks.

ry this:

Select a.Day, b.date

From days a LEFT JOIN dates b ON a.Day = DAY(b.date)

Order By a.Day, b.date

|||That didn't seem to do anything. What was the thought behind this if you don't mind?|||

CREATE TABLE [dbo].[Dates]([Date] [datetime] NULL,

[id] [int] NULL)

INSERT INTO [Dates] ([Date],[id])VALUES('Oct 2 2006 12:00:00:000AM',1)
INSERT INTO [Dates] ([Date],[id])VALUES('Oct 4 2006 12:00:00:000AM',2)

CREATE TABLE [Days]([Day] [int] NULL)

INSERT INTO [Days] ([Day])VALUES(1)
INSERT INTO [Days] ([Day])VALUES(2)
INSERT INTO [Days] ([Day])VALUES(3)
INSERT INTO [Days] ([Day])VALUES(4)
INSERT INTO [Days] ([Day])VALUES(5)
INSERT INTO [Days] ([Day])VALUES(6)
INSERT INTO [Days] ([Day])VALUES(7)
INSERT INTO [Days] ([Day])VALUES(8)
INSERT INTO [Days] ([Day])VALUES(9)
INSERT INTO [Days] ([Day])VALUES(10)
INSERT INTO [Days] ([Day])VALUES(11)
INSERT INTO [Days] ([Day])VALUES(12)
INSERT INTO [Days] ([Day])VALUES(13)
INSERT INTO [Days] ([Day])VALUES(14)
INSERT INTO [Days] ([Day])VALUES(15)
INSERT INTO [Days] ([Day])VALUES(16)
INSERT INTO [Days] ([Day])VALUES(17)
INSERT INTO [Days] ([Day])VALUES(18)
INSERT INTO [Days] ([Day])VALUES(19)
INSERT INTO [Days] ([Day])VALUES(20)
INSERT INTO [Days] ([Day])VALUES(21)
INSERT INTO [Days] ([Day])VALUES(22)
INSERT INTO [Days] ([Day])VALUES(23)
INSERT INTO [Days] ([Day])VALUES(24)
INSERT INTO [Days] ([Day])VALUES(25)
INSERT INTO [Days] ([Day])VALUES(26)
INSERT INTO [Days] ([Day])VALUES(27)
INSERT INTO [Days] ([Day])VALUES(28)
INSERT INTO [Days] ([Day])VALUES(29)
INSERT INTO [Days] ([Day])VALUES(30)
INSERT INTO [Days] ([Day])VALUES(31)

And the script that works:

Select a.Day, b.date

From days a LEFT JOIN dates b ON a.Day = DAY(b.date)

Order By a.Day, b.date

If you cannot run this, let's see what is the problem again.

|||

So I get to playing around with your example and descovered some stuff I didn't know about left joins.

My query has touble when I add a Where clause on it to filter dates to a certain range. The differents querys are below in case someone else needs help. Thanks.

NOT WORKING

Select a.Day, b.date

From days a LEFT JOIN dateshiftcrewTable b ON a.Day = DAY(b.date)

Where b.date Between '1/1/1999' and '4/4/1999'

Order By a.Day, b.date

WORKING

Select a.Day, b.date

From days a LEFT JOIN dateshiftcrewTable b ON a.Day = DAY(b.date) and

b.date Between '1/1/1999' and '4/4/1999'

Order By a.Day, b.date

|||

If you use a subquery with a where clause fro your LEFT JOIN, it should work.

Select a.Day, b.date

From days a LEFT JOIN (select * FROM dateshiftcrewTable Where date Between '1/1/1999' and '4/4/1999') b ON a.Day = DAY(b.date)

Order By a.Day, b.date

David Portas on Any one? Query Help Please

Thanks for your response.
I got that query working,
SELECT P.productname, COUNT(S.productname)
FROM Products AS P
LEFT JOIN Sales AS S
ON P.productname = S.productname
GROUP BY P.productname ;
but what if there was another column CustomerId, which exists in both tables
and I want to just see products just for a particular customer?
So if a product is offered to a customer , but he doesn't order it then in
result I want to show that product with count as 0...Try this:
SELECT P.productname, COUNT(S.productname)
FROM Products AS P
LEFT JOIN Sales AS S
ON P.productname = S.productname
AND P.customerid = S.customerid
WHERE P.customerid = /* specify the required customer */
GROUP BY P.productname ;
The best way to get help with a problem such as this is to post DDL and
sample data. The following artcile explains how:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thnx David,
It worked for me.
"David Portas" wrote:

> Try this:
> SELECT P.productname, COUNT(S.productname)
> FROM Products AS P
> LEFT JOIN Sales AS S
> ON P.productname = S.productname
> AND P.customerid = S.customerid
> WHERE P.customerid = /* specify the required customer */
> GROUP BY P.productname ;
> The best way to get help with a problem such as this is to post DDL and
> sample data. The following artcile explains how:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>sql

DateTime without the time

Hi,

Im moving data from a OLE DB Source to a Flat File Destination.

I have a DateTime field in my database.

My current query returns:
2007-05-21 00:00:00

How can I make it return:
2007-05-21

Thank you!!

Use a derived column to cast the field to DT_DBDATE...

(DT_DBDATE)[YourDateTimeField]|||

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

|||

MrHat wrote:

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

Yes, you need to define the data type of that column to DT_DBDATE in the flat file connection manager.|||My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?|||

JStutz wrote:

My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?

Displaying just the time is a simple transact-sql statement using the CONVERT function.

|||

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

|||

SQL-PRO wrote:

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

Still if that's in your source query, you can't store it that way -- not in SQL Server anyway. (Unless you're storing it in a varchar field.)

DateTime without the time

Hi,

Im moving data from a OLE DB Source to a Flat File Destination.

I have a DateTime field in my database.

My current query returns:
2007-05-21 00:00:00

How can I make it return:
2007-05-21

Thank you!!

Use a derived column to cast the field to DT_DBDATE...

(DT_DBDATE)[YourDateTimeField]|||

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

|||

MrHat wrote:

I′ve modified the query so it returns only the date.

However, the Flat File Destination always changes it back to a DateTime.

Yes, you need to define the data type of that column to DT_DBDATE in the flat file connection manager.|||My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?|||

JStutz wrote:

My SQL server destination changes back to DT_DBtimestamp.....in my sql table it has datatype of datetime....but I do not want to display the Time.....just the date....any ideas?

Displaying just the time is a simple transact-sql statement using the CONVERT function.

|||

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

|||

SQL-PRO wrote:

Can you use a SQL Command in your source?

If so, use CONVERT(varchar, <dateField>, 112) in your select list

Still if that's in your source query, you can't store it that way -- not in SQL Server anyway. (Unless you're storing it in a varchar field.)

DateTime variable problem

Hi,
The followng snippet runs OK in SQL Query analyser when a literal date
'1/11/2006' is used for the first date comparision
When local variable @.StartDate is used instead it runs on forever (I think,
certainly an order of magnitude longer)
(I added the set dateformat dmy and switched to the numeric date format in
an effort to solve this.
Ideally I want to run with '1-Nov-2006' which for some reason runs faster
than the numeric version.)
Any ideas what I am doing wrong?
thanks
Bob
declare @.StartDate datetime
declare @.EndDate datetime
set dateformat dmy
set @.StartDate='1/11/2006'
set @.EndDate = '2/11/2006'
create Table #Temp (icp_id int)
insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory rh
inner join metershistory mh
ON rh.id = mh.routehistory_id
inner join icps i on mh.icp_id=i.id
WHERE rh.type = 1 and i.company_id =1
and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
mh.cant_read_code is null
It will be because the optimiser is allowing for different values in the
variable. With the literal it knows to use the date index - with the variable
it is allowing for a large date range.
Look at the query plan and change the query (mayne a subquery for the date)
or give a hint or maybe include the other columns in the date index to make
it covering.
Maybe something like
FROM (select * from routehistory where read_date between @.StartDate and
@.EndDate) rh
.....
"Bob" wrote:

> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
>
|||Bob
I have a couple of questions
1) Do you have an index (probably CI would be good choice) on read_date
column?
2) What happened if you change date format to YYYYMMDD and don't use SET
DATEFORMAT
"Bob" <bob@.nowhere.com> wrote in message
news:uRoiCglIHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I
> think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
> rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
|||Bob,
I think that you are being encountering what has become know as 'parameter
sniffing'.
You may wish to review these articles:
Stored Procedure -Parameter Sniffing
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx
http://tinyurl.com/f9r2
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Bob" <bob@.nowhere.com> wrote in message
news:uRoiCglIHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I
> think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
> rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
|||Hi All,
Thank you for your replies.
I won't pretend I understand Parameter sniffing.
What I have done is put the query into a sproc which was my end goal anyway
and I am tuning that up.
regards
Bob

DateTime variable problem

Hi,
The followng snippet runs OK in SQL Query analyser when a literal date
'1/11/2006' is used for the first date comparision
When local variable @.StartDate is used instead it runs on forever (I think,
certainly an order of magnitude longer)
(I added the set dateformat dmy and switched to the numeric date format in
an effort to solve this.
Ideally I want to run with '1-Nov-2006' which for some reason runs faster
than the numeric version.)
Any ideas what I am doing wrong?
thanks
Bob
declare @.StartDate datetime
declare @.EndDate datetime
set dateformat dmy
set @.StartDate='1/11/2006'
set @.EndDate = '2/11/2006'
create Table #Temp (icp_id int)
insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory rh
inner join metershistory mh
ON rh.id = mh.routehistory_id
inner join icps i on mh.icp_id=i.id
WHERE rh.type = 1 and i.company_id =1
and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
mh.cant_read_code is nullIt will be because the optimiser is allowing for different values in the
variable. With the literal it knows to use the date index - with the variable
it is allowing for a large date range.
Look at the query plan and change the query (mayne a subquery for the date)
or give a hint or maybe include the other columns in the date index to make
it covering.
Maybe something like
FROM (select * from routehistory where read_date between @.StartDate and
@.EndDate) rh
....
"Bob" wrote:
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
>|||Bob
I have a couple of questions
1) Do you have an index (probably CI would be good choice) on read_date
column?
2) What happened if you change date format to YYYYMMDD and don't use SET
DATEFORMAT
"Bob" <bob@.nowhere.com> wrote in message
news:uRoiCglIHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I
> think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
> rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>|||Bob,
I think that you are being encountering what has become know as 'parameter
sniffing'.
You may wish to review these articles:
Stored Procedure -Parameter Sniffing
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx
http://tinyurl.com/f9r2
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Bob" <bob@.nowhere.com> wrote in message
news:uRoiCglIHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I
> think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
> rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>|||Hi All,
Thank you for your replies.
I won't pretend I understand Parameter sniffing.
What I have done is put the query into a sproc which was my end goal anyway
and I am tuning that up.
regards
Bob

DateTime variable problem

Hi,
The followng snippet runs OK in SQL Query analyser when a literal date
'1/11/2006' is used for the first date comparision
When local variable @.StartDate is used instead it runs on forever (I think,
certainly an order of magnitude longer)
(I added the set dateformat dmy and switched to the numeric date format in
an effort to solve this.
Ideally I want to run with '1-Nov-2006' which for some reason runs faster
than the numeric version.)
Any ideas what I am doing wrong?
thanks
Bob
declare @.StartDate datetime
declare @.EndDate datetime
set dateformat dmy
set @.StartDate='1/11/2006'
set @.EndDate = '2/11/2006'
create Table #Temp (icp_id int)
insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory rh
inner join metershistory mh
ON rh.id = mh.routehistory_id
inner join icps i on mh.icp_id=i.id
WHERE rh.type = 1 and i.company_id =1
and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
mh.cant_read_code is nullIt will be because the optimiser is allowing for different values in the
variable. With the literal it knows to use the date index - with the variabl
e
it is allowing for a large date range.
Look at the query plan and change the query (mayne a subquery for the date)
or give a hint or maybe include the other columns in the date index to make
it covering.
Maybe something like
FROM (select * from routehistory where read_date between @.StartDate and
@.EndDate) rh
....
"Bob" wrote:

> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I think
,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
>|||Bob
I have a couple of questions
1) Do you have an index (probably CI would be good choice) on read_date
column?
2) What happened if you change date format to YYYYMMDD and don't use SET
DATEFORMAT
"Bob" <bob@.nowhere.com> wrote in message
news:uRoiCglIHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I
> think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
> rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>|||Bob,
I think that you are being encountering what has become know as 'parameter
sniffing'.
You may wish to review these articles:
Stored Procedure -Parameter Sniffing
http://blogs.msdn.com/queryoptteam/.../31/565991.aspx
http://tinyurl.com/f9r2
http://sqlblogcasts.com/blogs/tonyr.../05/17/444.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Bob" <bob@.nowhere.com> wrote in message
news:uRoiCglIHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I
> think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
> rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>sql

DateTime validation throgh sql query

hai friends,
how can i made validation of date time through sql query?

Swati

Check out the SQL Function: IsDate()|||Validation is best done from front end technology rather than make a round trip to the DB just to validate a date field.

Date-time unsupported in subquery?

I have a query that returns 2 fields, a date-time field and a Count(*)
field. Basically it tells me how many people exist for a specific date. I
want to use this as a subquery and test for the earliest date that the
Count(*) field (members) is below a certain number. However, once I use it
in a subquery I get an error on the date-time field that it is an
unsupported data type... any clues?
SELECT TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS SAre you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>|||Yes this will do the same thing. I'm not finished with the main query yet.
The end query will look something like below. Just trying to simplify thin
gs to find the source of the problem. The question remains - Why is the dat
etime field not showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members,
BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID = S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" <arnie@.1568.com> wrote in message news:eVSMvOcpGHA.2400@.TK2M
SFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>|||OK I cast the datetime field to varchar and then back to datetime in the mai
n query and it seems to work. Here's my final query.
SELECT TOP (1) S.Members, CAST(S.Expr1 AS datetime) AS TermEnd
FROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS Varchar) AS E
xpr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID = B.BoardID AND S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OJ3MOdcpGHA.4548@.TK2
MSFTNGP03.phx.gbl...
Yes this will do the same thing. I'm not finished with the main query yet.
The end query will look something like below. Just trying to simplify thin
gs to find the source of the problem. The question remains - Why is the dat
etime field not showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members,
BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID = S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" <arnie@.1568.com> wrote in message news:eVSMvOcpGHA.2400@.TK2M
SFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>|||Just to clarify:
if you remove the parenthesis around the number 100 so that the code reads
... (SELECT TOP 100 PERCENT ...
your code is good to run.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
>field. Basically it tells me how many people exist for a specific date. I
>want to use this as a subquery and test for the earliest date that the
>Count(*) field (members) is below a certain number. However, once I use it
>in a subquery I get an error on the date-time field that it is an
>unsupported data type... any clues?
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS
> Members
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>|||On Wed, 12 Jul 2006 09:48:39 -0500, Ryan wrote:

>I have a query that returns 2 fields, a date-time field and a Count(*)
>field. Basically it tells me how many people exist for a specific date. I
>want to use this as a subquery and test for the earliest date that the
>Count(*) field (members) is below a certain number. However, once I use it
>in a subquery I get an error on the date-time field that it is an
>unsupported data type... any clues?
Hi Ryan,
None at all. Datetime columns are allowed in subqueries. It might help
if could post the actual error message (use copy and paste to prevent
transcription errors). I'd also like to see the structure of the table
(posted as a CREATE TABLE statement).

>SELECT TermEndDate, Members
>FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
The ORDER BY and the TOP (100) PERCENT in the subquery are completely
useless. Get rid of them.
If you need the results to be ordered, put an ORDER BY clause on the
outer query:
SELECT TermEndDate, Members
FROM (SELECT TermEndDate, COUNT(*) AS Members
FROM Person_mm_Board AS P
WHERE BoardID = @.BoardID)
GROUP BY TermEndDate) AS S
ORDER BY TermEndDate
Hugo Kornelis, SQL Server MVP|||It still looks like you are making this too difficult.
Datetime fields work just fine in sub-queries.
Are you attempting to locate the next Board Member with term expiring?
Having a bit more detail about what you are working with (table DDL, sample
data, complete problem story) sure would make it easier to assist you. Witho
ut that, we are just playing twenty questions with you.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OE5bfhcpGHA.756@.TK2M
SFTNGP05.phx.gbl...
OK I cast the datetime field to varchar and then back to datetime in the mai
n query and it seems to work. Here's my final query.
SELECT TOP (1) S.Members, CAST(S.Expr1 AS datetime) AS TermEnd
FROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS Varchar) AS E
xpr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID = B.BoardID AND S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OJ3MOdcpGHA.4548@.TK2
MSFTNGP03.phx.gbl...
Yes this will do the same thing. I'm not finished with the main query yet.
The end query will look something like below. Just trying to simplify thin
gs to find the source of the problem. The question remains - Why is the dat
etime field not showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members,
BoardID
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID = S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" <arnie@.1568.com> wrote in message news:eVSMvOcpGHA.2400@.TK2M
SFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same without err
or.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID = @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.
gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
> field. Basically it tells me how many people exist for a specific date.
I
> want to use this as a subquery and test for the earliest date that the
> Count(*) field (members) is below a certain number. However, once I use i
t
> in a subquery I get an error on the date-time field that it is an
> unsupported data type... any clues?
>
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Member
s
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>
>

Date-time unsupported in subquery?

I have a query that returns 2 fields, a date-time field and a Count(*)
field. Basically it tells me how many people exist for a specific date. I
want to use this as a subquery and test for the earliest date that the
Count(*) field (members) is below a certain number. However, once I use it
in a subquery I get an error on the date-time field that it is an
unsupported data type... any clues?
SELECT TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members
FROM Person_mm_Board AS P
WHERE (BoardID = @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS SThis is a multi-part message in MIME format.
--=_NextPart_000_0BC1_01C6A589.01BE9E50
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Are you making this too difficult? Perhaps this will do the same without =error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*) > field. Basically it tells me how many people exist for a specific =date. I > want to use this as a subquery and test for the earliest date that the =
> Count(*) field (members) is below a certain number. However, once I =use it > in a subquery I get an error on the date-time field that it is an > unsupported data type... any clues?
> > SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS =Members
> FROM Person_mm_Board AS P
> WHERE (BoardID =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_0BC1_01C6A589.01BE9E50
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Are you making this too difficult? =Perhaps this will do the same without error.
SELECT TermEndDate , count( Members )FROM =Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY TermEndDateORDER BY =TermEndDate
-- Arnie Rowland* "To be =successful, your heart must accompany your knowledge."
"Ryan" wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...>I =have a query that returns 2 fields, a date-time field and a Count(*) > =field. Basically it tells me how many people exist for a specific date. I => want to use this as a subquery and test for the earliest date =that the > Count(*) field (members) is below a certain number. =However, once I use it > in a subquery I get an error on the date-time field =that it is an > unsupported data type... any clues?> > SELECT TermEndDate, Members> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) =AS Members> &nbs=p;  =; FROM =Person_mm_Board AS P> &nbs=p; WHERE (BoardID =3D @.BoardID)> &n=bsp; &nb=sp; GROUP BY TermEndDate> = &=nbsp; ORDER BY TermEndDate) AS S > >

---

--=_NextPart_000_0BC1_01C6A589.01BE9E50--|||This is a multi-part message in MIME format.
--=_NextPart_000_0033_01C6A59D.202C71F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Yes this will do the same thing. I'm not finished with the main query =yet. The end query will look something like below. Just trying to =simplify things to find the source of the problem. The question remains =- Why is the datetime field not showing up when used as a subquery? =Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS =Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S INNER JOIN Board AS B ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members "Arnie Rowland" <arnie@.1568.com> wrote in message =news:eVSMvOcpGHA.2400@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same =without error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a =Count(*) > field. Basically it tells me how many people exist for a specific =date. I > want to use this as a subquery and test for the earliest date that =the > Count(*) field (members) is below a certain number. However, once I =use it > in a subquery I get an error on the date-time field that it is an > unsupported data type... any clues?
> > SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS =Members
> FROM Person_mm_Board AS P
> WHERE (BoardID =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_0033_01C6A59D.202C71F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Yes this will do the same thing. =I'm not finished with the main query yet. The end query will look =something like below. Just trying to simplify things to find the source of the problem. The question remains - Why is the datetime field not =showing up when used as a subquery? Thanks.
SELECT TOP(1) TermEndDate, MembersFROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) =AS Members, BoardID &n=bsp; FROM =Person_mm_Board AS P &n=bsp; WHERE (BoardID =3D @.BoardID) = =GROUP BY TermEndDate &nbs=p;  =; ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" wrote in message =news:eVSMvOcpGHA.2400=@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? =Perhaps this will do the same without error.

SELECT TermEndDate , count( Members )FROM Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY =TermEndDateORDER BY TermEndDate

-- Arnie Rowland* "To be =successful, your heart must accompany your knowledge."


"Ryan" wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...>I have a query that returns 2 fields, a date-time field and a Count(*) > =field. Basically it tells me how many people exist for a specific date. =I > want to use this as a subquery and test for the earliest date =that the > Count(*) field (members) is below a certain number. = However, once I use it > in a subquery I get an error on the =date-time field that it is an > unsupported data type... any =clues?> > SELECT TermEndDate, Members> FROM (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS =Members> &nbs=p;  =; FROM =Person_mm_Board AS =P> &nbs=p; WHERE (BoardID =3D =@.BoardID)> &n=bsp; &nb=sp; GROUP BY =TermEndDate> = &=nbsp; ORDER BY TermEndDate) AS S > > =

---

--=_NextPart_000_0033_01C6A59D.202C71F0--|||This is a multi-part message in MIME format.
--=_NextPart_000_004C_01C6A59E.30B9DA20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
OK I cast the datetime field to varchar and then back to datetime in the =main query and it seems to work. Here's my final query.
SELECT TOP (1) S.Members, CAST(S.Expr1 AS datetime) AS TermEnd
FROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS Varchar) =AS Expr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID =3D B.BoardID AND =S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:OJ3MOdcpGHA.4548@.TK2MSFTNGP03.phx.gbl...
Yes this will do the same thing. I'm not finished with the main query =yet. The end query will look something like below. Just trying to =simplify things to find the source of the problem. The question remains =- Why is the datetime field not showing up when used as a subquery? =Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS =Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S INNER JOIN Board AS B ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members "Arnie Rowland" <arnie@.1568.com> wrote in message =news:eVSMvOcpGHA.2400@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same =without error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a =Count(*) > field. Basically it tells me how many people exist for a specific =date. I > want to use this as a subquery and test for the earliest date that =the > Count(*) field (members) is below a certain number. However, once =I use it > in a subquery I get an error on the date-time field that it is an > unsupported data type... any clues?
> > SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) =AS Members
> FROM Person_mm_Board AS P
> WHERE (BoardID =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_004C_01C6A59E.30B9DA20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

OK I cast the datetime field to varchar =and then back to datetime in the main query and it seems to work. Here's my =final query.
SELECT TOP (1) =S.Members, CAST(S.Expr1 AS datetime) AS TermEndFROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS =Varchar) AS Expr1, COUNT(*) AS Members, BoardID &n=bsp; FROM =Person_mm_Board AS P &n=bsp; WHERE (BoardID =3D @.BoardID) = =GROUP BY TermEndDate, BoardID &n=bsp; ORDER BY TermEndDate) AS S INNER JOIN  =; Board AS B ON S.BoardID =3D B.BoardID AND S.Members < B.NumberMembers
"Ryan" = wrote in message news:OJ3MOdcpGHA.4548=@.TK2MSFTNGP03.phx.gbl...
Yes this will do the same =thing. I'm not finished with the main query yet. The end query will look =something like below. Just trying to simplify things to find the source of the problem. The question remains - Why is the datetime field not =showing up when used as a subquery? Thanks.

SELECT TOP(1) TermEndDate, MembersFROM (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS Members, =BoardID &n=bsp; FROM =Person_mm_Board AS =P &n=bsp; WHERE (BoardID =3D =@.BoardID) = = GROUP BY =TermEndDate &nbs=p;  =; ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" wrote in =message news:eVSMvOcpGHA.2400=@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? =Perhaps this will do the same without error.

SELECT TermEndDate , count( Members )FROM Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY =TermEndDateORDER BY TermEndDate

-- Arnie Rowland* "To =be successful, your heart must accompany your knowledge."


"Ryan" wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...>I have a query that returns 2 fields, a date-time field and a Count(*) > field. Basically it tells me how many people exist for a =specific date. I > want to use this as a subquery and test for =the earliest date that the > Count(*) field (members) is below a =certain number. However, once I use it > in a subquery I get an =error on the date-time field that it is an > unsupported data =type... any clues?> > SELECT =TermEndDate, Members> FROM = (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS =Members> &nbs=p;  =; FROM =Person_mm_Board AS =P> &nbs=p; WHERE (BoardID =3D =@.BoardID)> &n=bsp; &nb=sp; GROUP BY =TermEndDate> = &=nbsp; ORDER BY TermEndDate) AS S > >

---

--=_NextPart_000_004C_01C6A59E.30B9DA20--|||Just to clarify:
if you remove the parenthesis around the number 100 so that the code reads
... (SELECT TOP 100 PERCENT ...
your code is good to run.
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a Count(*)
>field. Basically it tells me how many people exist for a specific date. I
>want to use this as a subquery and test for the earliest date that the
>Count(*) field (members) is below a certain number. However, once I use it
>in a subquery I get an error on the date-time field that it is an
>unsupported data type... any clues?
> SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS
> Members
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
>|||On Wed, 12 Jul 2006 09:48:39 -0500, Ryan wrote:
>I have a query that returns 2 fields, a date-time field and a Count(*)
>field. Basically it tells me how many people exist for a specific date. I
>want to use this as a subquery and test for the earliest date that the
>Count(*) field (members) is below a certain number. However, once I use it
>in a subquery I get an error on the date-time field that it is an
>unsupported data type... any clues?
Hi Ryan,
None at all. Datetime columns are allowed in subqueries. It might help
if could post the actual error message (use copy and paste to prevent
transcription errors). I'd also like to see the structure of the table
(posted as a CREATE TABLE statement).
>SELECT TermEndDate, Members
>FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS Members
> FROM Person_mm_Board AS P
> WHERE (BoardID = @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S
The ORDER BY and the TOP (100) PERCENT in the subquery are completely
useless. Get rid of them.
If you need the results to be ordered, put an ORDER BY clause on the
outer query:
SELECT TermEndDate, Members
FROM (SELECT TermEndDate, COUNT(*) AS Members
FROM Person_mm_Board AS P
WHERE BoardID = @.BoardID)
GROUP BY TermEndDate) AS S
ORDER BY TermEndDate
Hugo Kornelis, SQL Server MVP|||This is a multi-part message in MIME format.
--=_NextPart_000_0C84_01C6A5D2.9A8AF130
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
It still looks like you are making this too difficult.
Datetime fields work just fine in sub-queries.
Are you attempting to locate the next Board Member with term expiring?
Having a bit more detail about what you are working with (table DDL, =sample data, complete problem story) sure would make it easier to assist =you. Without that, we are just playing twenty questions with you.
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:OE5bfhcpGHA.756@.TK2MSFTNGP05.phx.gbl...
OK I cast the datetime field to varchar and then back to datetime in =the main query and it seems to work. Here's my final query.
SELECT TOP (1) S.Members, CAST(S.Expr1 AS datetime) AS TermEnd
FROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS =Varchar) AS Expr1, COUNT(*) AS Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate, BoardID
ORDER BY TermEndDate) AS S INNER JOIN
Board AS B ON S.BoardID =3D B.BoardID AND =S.Members < B.NumberMembers
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:OJ3MOdcpGHA.4548@.TK2MSFTNGP03.phx.gbl...
Yes this will do the same thing. I'm not finished with the main =query yet. The end query will look something like below. Just trying =to simplify things to find the source of the problem. The question =remains - Why is the datetime field not showing up when used as a =subquery? Thanks.
SELECT TOP(1) TermEndDate, Members
FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) AS =Members, BoardID
FROM Person_mm_Board AS P
WHERE (BoardID =3D @.BoardID)
GROUP BY TermEndDate
ORDER BY TermEndDate) AS S INNER JOIN Board AS B ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members "Arnie Rowland" <arnie@.1568.com> wrote in message =news:eVSMvOcpGHA.2400@.TK2MSFTNGP03.phx.gbl...
Are you making this too difficult? Perhaps this will do the same =without error.
SELECT
TermEndDate
, count( Members )
FROM Person_mm_Board
WHERE BoardID =3D @.BoardID
GROUP BY TermEndDate
ORDER BY TermEndDate
-- Arnie Rowland* "To be successful, your heart must accompany your knowledge."
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...
>I have a query that returns 2 fields, a date-time field and a =Count(*) > field. Basically it tells me how many people exist for a =specific date. I > want to use this as a subquery and test for the earliest date =that the > Count(*) field (members) is below a certain number. However, =once I use it > in a subquery I get an error on the date-time field that it is =an > unsupported data type... any clues?
> > SELECT TermEndDate, Members
> FROM (SELECT TOP (100) PERCENT TermEndDate, COUNT(*) =AS Members
> FROM Person_mm_Board AS P
> WHERE (BoardID =3D @.BoardID)
> GROUP BY TermEndDate
> ORDER BY TermEndDate) AS S > >
--=_NextPart_000_0C84_01C6A5D2.9A8AF130
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

It still looks like you are making this =too difficult.
Datetime fields work just fine in sub-queries.
Are you attempting to locate the next =Board Member with term expiring?
Having a bit more detail about what you =are working with (table DDL, sample data, complete problem story) sure would make it =easier to assist you. Without that, we are just playing twenty questions with =you.
-- Arnie Rowland* "To be successful, your heart must =accompany your knowledge."
"Ryan" = wrote in message news:OE5bfhcpGHA.756@.T=K2MSFTNGP05.phx.gbl...
OK I cast the datetime field to =varchar and then back to datetime in the main query and it seems to work. Here's =my final query.

SELECT TOP =(1) S.Members, CAST(S.Expr1 AS datetime) AS TermEndFROM (SELECT TOP (100) PERCENT CAST(TermEndDate AS =Varchar) AS Expr1, COUNT(*) AS Members, =BoardID &n=bsp; FROM =Person_mm_Board AS =P &n=bsp; WHERE (BoardID =3D =@.BoardID) = = GROUP BY TermEndDate, =BoardID &n=bsp; ORDER BY TermEndDate) AS S INNER =JOIN  =; Board AS B ON S.BoardID =3D B.BoardID AND S.Members < B.NumberMembers
"Ryan" = wrote in message news:OJ3MOdcpGHA.4548=@.TK2MSFTNGP03.phx.gbl...
Yes this will do the same =thing. I'm not finished with the main query yet. The end query will look =something like below. Just trying to simplify things to find the source =of the problem. The question remains - Why is the datetime field not =showing up when used as a subquery? Thanks.

SELECT TOP(1) TermEndDate, MembersFROM (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS Members, =BoardID &n=bsp; FROM =Person_mm_Board AS =P &n=bsp; WHERE (BoardID =3D =@.BoardID) = = GROUP BY =TermEndDate &nbs=p;  =; ORDER BY TermEndDate) AS S
INNER JOIN Board AS B
ON B.BoardID =3D S.BoardID
WHERE S.Members < B.Members
"Arnie Rowland" wrote in =message news:eVSMvOcpGHA.2400=@.TK2MSFTNGP03.phx.gbl...
Are you making this too =difficult? Perhaps this will do the same without error.

SELECT TermEndDate , count( Members )FROM Person_mm_BoardWHERE BoardID =3D @.BoardIDGROUP BY TermEndDateORDER BY TermEndDate

-- Arnie Rowland* "To =be successful, your heart must accompany your =knowledge."


"Ryan" wrote in message news:%237V2DKcpGHA.4760@.TK2MSFTNGP05.phx.gbl...>I have a query that returns 2 fields, a date-time field and a Count(*) => field. Basically it tells me how many people exist for a =specific date. I > want to use this as a subquery and test for =the earliest date that the > Count(*) field (members) is below =a certain number. However, once I use it > in a =subquery I get an error on the date-time field that it is an > unsupported =data type... any clues?> > =SELECT TermEndDate, Members> FROM (SELECT TOP (100) PERCENT TermEndDate, =COUNT(*) AS =Members> &nbs=p;  =; FROM =Person_mm_Board AS =P> &nbs=p; WHERE (BoardID =3D =@.BoardID)> &n=bsp; &nb=sp; GROUP BY =TermEndDate> = &=nbsp; ORDER BY TermEndDate) AS S > >

---

--=_NextPart_000_0C84_01C6A5D2.9A8AF130--

datetime to date??

When I send a query 'SELECT somedatecolumn FROM...' I got resultset
containing datetime. It's ok because it is a datetime field (or
smalldatetime) but it is a little frustrating because I'm interested only in
date. Is it possible to get only a date, without the time? My previous
database was in Visual FoxPro and there I had date columns but on MSDE or
MSSQL it seems I only can have datetime or smalldatetime.
Thx.
On Sat, 29 Jan 2005 20:04:47 +0100, Rutko wrote:

>When I send a query 'SELECT somedatecolumn FROM...' I got resultset
>containing datetime. It's ok because it is a datetime field (or
>smalldatetime) but it is a little frustrating because I'm interested only in
>date. Is it possible to get only a date, without the time? My previous
>database was in Visual FoxPro and there I had date columns but on MSDE or
>MSSQL it seems I only can have datetime or smalldatetime.
>Thx.
>
Hi Rutko,
The short answer: no.
The long answer: http://www.karaszi.com/SQLServer/info_datetime.asp
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||See the DATEPART functions in the Books On Line.
Jim
"Rutko" <rutko22001@.yahoo.com> wrote in message
news:egcU0VjBFHA.1040@.TK2MSFTNGP09.phx.gbl...
> When I send a query 'SELECT somedatecolumn FROM...' I got resultset
> containing datetime. It's ok because it is a datetime field (or
> smalldatetime) but it is a little frustrating because I'm interested only
> in
> date. Is it possible to get only a date, without the time? My previous
> database was in Visual FoxPro and there I had date columns but on MSDE or
> MSSQL it seems I only can have datetime or smalldatetime.
> Thx.
>

Datetime string

Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 =A4W=A4=C8 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 =A4W=A4=C8 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 =A4W=A4=C8 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 =A4W=A4=C8 09:24:17 .
Thanks.http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1146016353.858447.49210@.i40g2000cwc.googlegroups.com...
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 W 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 W 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 W 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 W 09:24:17 .
Thanks.|||Hi
declare @.dt varchar(20)
set @.dt='2006/4/26 10:22:55'
create table #table (c datetime)
insert into #table (c)
select cast(rtrim(y*10000+m*100+d)+' '+ t as datetime)
from
(
select year(@.dt) as y,month(@.dt) as m ,day(@.dt)as d,
right(@.dt, CHARINDEX(' ', REVERSE(@.dt))-1) t
) as der
select * from #table
"hon123456" <peterhon321@.yahoo.com.hk> wrote in message
news:1146016353.858447.49210@.i40g2000cwc.googlegroups.com...
Dear all,
I got a fieldA which is datetime , when I check the value
in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
When I get this field to a ADODB.recordset named rs_A, rs_A
returns :
2006/4/26 W 09:24:17 ,which is date time string format
with Chinese String.
When I try to insert '2006/4/26 W 09:24:17' into a datetime
field, the query analyzer complains
Syntax error converting datetime from character string.
When I cast('2006/4/26 W 09:24:17' as datetime), the query
analyzer also complains
Syntax error converting datetime from character string.
My quetions is how to keep the data time value as 2003-10-27
10:55:00.000 but not 2006/4/26 W 09:24:17 .
Thanks.|||hon123456 (peterhon321@.yahoo.com.hk) writes:
> I got a fieldA which is datetime , when I check the value
> in query analyzer, the value returned is: 2003-10-27 10:55:00.000.
> When I get this field to a ADODB.recordset named rs_A, rs_A
> returns :
> 2006/4/26 W 09:24:17 ,which is date time string format
> with Chinese String.
> When I try to insert '2006/4/26 W 09:24:17' into a datetime
> field, the query analyzer complains
> Syntax error converting datetime from character string.
> When I cast('2006/4/26 W 09:24:17' as datetime), the query
> analyzer also complains
> Syntax error converting datetime from character string.
> My quetions is how to keep the data time value as 2003-10-27
> 10:55:00.000 but not 2006/4/26 W 09:24:17 .
One answer to that particular question, is to change your regional settings
to Swedish, or at least change the datetime format in regional settings. I
would not recommend that though.
What I don't really understand is you need to take a value from an
ADO recordset and paste into Query Analyzer.
When you pass dates to and from SQL Server, you should do so in binary
format. The client API will then convert from/to string format according
to regional settings.
I suspect that you do something like this in your ADO code:
sql = "SELECT ... FROM tbl WHERE datetimecol = '" & rs("dt") & "'"
Don't do that. Run a parameterised query instead. Here is a quick sample
query:
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
" FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
cmd.Parameters.Append
cmd.CreateParameter("@.custid", adWChar, adParamInput, 5, custid)
End If
If shipname <> "" Then
cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
cmd.Parameters.Append cmd.CreateParameter("@.shipname", _
adVarWChar, adParamInput, 40, shipname)
End If
Set rs = cmd.Execute
This example does not includ a datetime parameter, but at least you get
to see the principle.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Datetime question

Hello - easy question hopefully,

I am writing a query that will run every morning to get the calls from the previous 24 hours. It is however broken up in core and non core hours (so it will actually be 2 queries), core being 6.30 am till 9.30 pm the previous day and non core being 9.30 pm till 6.30 am the next day. It looks at the CallStartDate field which is datetime field such as '04/11/07 7:00:00 AM'.

What will my WHERE clause look like? Do I have to split the time from the date in order to make this happen or is there a much easier way?

Thanks,

MB

This should do what you need it to:

create table YourTable
(
CallStartDate datetime
)
insert into YourTable
select '20070411 5:30'
union all
select '20070411 7:30'
union all
select '20070411 9:30'

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, CallStartDate)),
convert(datetime,convert(varchar(10), CallStartDate,108)) ,
CallStartDate
from YourTable
--past day
where CallStartDate >= dateadd(day,-1,getdate())
--not (between 6:30 and 9:30, not including 9:30_
and convert(datetime,convert(varchar(10), CallStartDate,108)) >= '6:30'
and convert(datetime,convert(varchar(10), CallStartDate,108)) < '9:30'

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, CallStartDate)),
convert(datetime,convert(varchar(10), CallStartDate,108)) ,
CallStartDate
from YourTable
--past day
where CallStartDate >= dateadd(day,-1,getdate())
--not (between 6:30 and 9:30, not including 9:30_
and not(convert(datetime,convert(varchar(10), CallStartDate,108)) >= '6:30'
and convert(datetime,convert(varchar(10), CallStartDate,108)) < '9:30' )|||

Code Snippet

WHERE CallStartDate >

DATEADD(day, -1, CONVERT(DATETIME, CONVERT(char(11), GETDATE()) + ' 06:30AM')

AND CallStartDate <=

DATEADD(day, -1, CONVERT(DATETIME, CONVERT(char(11), GETDATE()) + ' 09:30PM')

|||

Something like this could work for you:

Code Snippet

--core
WHERE ( CallStartDate >= dateadd ( day, -1, ( cast( convert( varchar(10), getdate(), 101 ) as datetime ) + '9:30 AM' ))
AND CallStartDate < dateadd ( hour, -12, ( cast( convert( varchar(10), getdate(), 101 ) as datetime ) + '9:30 AM' ))
)

--non core
WHERE ( CallStartDate >= dateadd ( hour, -12, ( cast( convert( varchar(10), getdate(), 101 ) as datetime ) + '9:30 AM' ))
AND CallStartDate < ( cast( convert( varchar(10), getdate(), 101 ) as datetime ) + '9:30 AM' )
)

|||it's working!! Thanks a million guys

datetime query?

Hi,

This is my code:

SqlCommand getPreviousDateCmd = new SqlCommand("SELECT Top 1 Open_date FROM Counter WHERE (Open_date <= @.todays_date) ORDER BY Open_date DESC", myConnection);

//Get previous date
getPreviousDateCmd.Parameters.AddWithValue("@.todays_date", DateTime.Now.ToString("dd/MM/yyyy"));
DateTime previousDateTime =Convert.ToDateTime(getPreviousDateCmd.ExecuteScalar().ToString());

//previousDate = previousDateTime.ToString("dd/MM/yyyy");
LabelToday.Text = previousDateTime.ToString("dd/MM/yyyy");
//If previous date is same as today's date (Get only date and year)

I get a huge unstoppable(?) error message when it converts STRING to DATETIME.
What I want to do is get DATETIME object and change the format to "dd/MM/yyyy"

Could someone give me some ideas? Thankx!

Assuming your query always returns a row (*no* nulls e.g DBNull, when you would need to deal with that too), why converting it to string in the middle? Or converting at all if you know it is a date

DateTime previousDateTime = (DateTime)(getPreviousDateCmd.ExecuteScalar());

Datetime query issue with C# stored procedure

Ok, i am using the convert function to get the date format from my datetime column. My problem is when C# try's to pull the column name the column name is not sent in the query. When I run the query in the query analyzer the column name is blank in the result window. How can I get the date from the record with out losing the name of my column in the query. My data binding needs the name of the column to bind the data to the drop down list control. Here is the statement:

SQL statement
SELECT DISTINCT convert(datetime, eventDT, 110) FROM tblRecognition

C# code
ddlDateTo.DataSource = _uiCode.Fill.Date();
ddlDateTo.DataTextField = "eventDT";
ddlDateTo.DataBind();

Thank you,assign a column alias in your SELECT

convert(datetime, eventDT, 110) as displaydate

ddlDateTo.DataTextField = "displaydate";|||well that was simple lol. thank you, i don't know why that didn't accur to me.

DateTime Query

Hi, am trying to build a scheduling system within my SQL Server application. Can someone point me in a good direction please?

OK, A user can select that they want something to happen Weekly, and on each Tuesday of every week. They of course can select any day from Monday through to Sunday. I would like to know how to take this data, and through a stored procedure update a table to set the "next execution date".

I have sorted the Daily timetable for each time, and the Monthly on a certain date seems easy enough, but I cant get the Weekly on a certain Day sorted. Any advice would be great!

Maybe you could post some code of your table and query...?

Though I'm not sure why you have multiple tables; monthly, weekly, daily.

You should just need one

NextExecutionMgr( DueDate datetime, FreqIntvl varchar(2), FreqAmt int, RecordKey varchar(200) )

index on DueDate, most likely a second index on RecordKey

The first item on your DueDate index is the next one to be processed.

When its time comes and once it is processesed you just adjust the date:

Code Snippet

case FreqIntvl when 'dy' then DueDate = DateAdd(dy, FreqAmt, DueDate)

when 'wk' then DueDate = DateAdd(wk, FreqAmt, DueDate)

etc.

end

(doesn't it suck that dateadd doesn't accept a variable for parameter one?)

|||

Why re-invent the wheel?

I would recommend exploring the SQL Agent Service, since it has full features calendaring and scheduling already built-in.

And if you are using SQL 2005 Express, which doens't include SQL Agent, you could explore a combination of using the Windows Scheduler service and SQLCmd.exe.

|||

Arnie, quite true.

I guess it just depends on what it is he's trying to schedule.

Agent is perfect for scheduled system level events and tasks.

But if he's trying to kick off application events with 1,000's of users, that a different thing.

Lotsa cats...

|||

And the skin just regrows...

I suspect that the solution will evolve into a combination of efforts -your outline about how to manage a 'queue' table, and some form of a scheduled process to 'POP' the queue.

There just isn't enough information to point the OP in the 'best' direction. SQL Agent, Notification Service, Service Broker Queues, some 'homegrown' hybrid, ...

datetime query

my database contains 1 field with "datetime" in sql server
hw can i query the database to just compare the date section of that field ? and not the time
WHERE CONVERT(VARCHAR(10),datecolumn,101) >= '08/06/2005'
|||Although Dinakar's suggestion will work, it will not perform as well as something like this:

WHERE datecolumn >= '20050806' AND datecolumn < '20050807'

When you perform a function on a column (such as CONVERT) this willmake the query non-sargable and will therefore not take advantage of anindex on the column.
For more tips on performance, seeSQL Server Transact-SQL WHERE Clause.

Datetime Query

i am trying to query a datetime column in a db.

e.g. 3/7/2005 4:24:01 AM
My query is below :-
--
select a.date, b.useruri as 'FROM', c.useruri as 'TO',
a.body as 'MESSAGE' from messages as a
inner join
users as b
on a.fromid = b.userid
inner join
users as c
on a.toid = c.userid
where a.date like '%2005-03-01%'
order by a.dateSpecify the times using BETWEEN. Otherwise, you won't use any indexes and this will be extremely slow.|||Do i specify the date as a i wrote in the query. since the datetime is like
3/7/2005 4:24:01 AM ?

or do i have to declare the datime if it was today and use the variable in the query.|||Well, it depends what you're trying to achieve. :) If the data was stored like that, then just go from 00:00:00 to 23:59:59. If it was stored with more accuracy, it can get a little tricky. Note the following code results followed by an excert from Books Online:

CODE:

DECLARE @.dates TABLE(date1 DATETIME)

INSERT @.dates(date1)
SELECT '01/01/05 13:58:01.000' UNION ALL
SELECT '01/02/05 00:00:00.000' UNION ALL
SELECT '01/02/05 00:00:00.001' UNION ALL
SELECT '01/02/05 23:59:59.999' UNION ALL
SELECT '01/03/05 00:00:00.000' UNION ALL
SELECT '01/03/05 00:00:00.001' UNION ALL
SELECT '01/04/05 10:00:00.001'

SELECT date1 FROM @.dates

SELECT date1
FROM @.dates
WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.999'

SELECT date1
FROM @.dates
WHERE date1 BETWEEN '01/02/05 00:00:00.000' AND '01/02/05 23:59:59.997'

BOL Quote:

Date and time data types for representing date and time of day.

datetime

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table.

Example Rounded example
01/01/98 23:59:59.999 1998-01-02 00:00:00.000
01/01/98 23:59:59.995,
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998 1998-01-01 23:59:59.997
01/01/98 23:59:59.992,
01/01/98 23:59:59.993,
01/01/98 23:59:59.994 1998-01-01 23:59:59.993
01/01/98 23:59:59.990 or
01/01/98 23:59:59.991 1998-01-01 23:59:59.990

Microsoft SQL Server rejects all values it cannot recognize as dates between 1753 and 9999.sql

Datetime Query

How to display records between datetime field??WHERE dateCol BETWEEN @.date1 AND @.date2

???|||how do i display records between two fields Callstartdt and endcalldt bot the fields are datetime fields...

What would be syntax for the query??

Please help..|||You really have to give us more information

Like ddl, sample data and expected results

Read the hint sticky at the top of the forum to see how to post a question here|||Do i need to post the design of the table??

How do i copy a design of the table...

I want to display all the records between two dates.|||go to enterprise mangler and right click on the table

Choose all tasks then generate sql server script

Did you read the hint link at the top of the page?

it's all explained up there, but in any case

When you say betyween 2 dates?

Which 2 dates?

Or is it a number of days betwen 2 dates

The question is not very clear, and that's why code examples would help us immensly|||How to display records between datetime field??



***********
datetime * records * field
***********|||Lmao! :D