between

SQL Server2000 SP3
Field date is smalldatetime
I have 8 rows with data='06-Aug-2003'
SELECT * FROM TEST
where data between '06-Aug-2003' and '06-Aug-2003'
Results: 0 Rows
SELECT * FROM TEST
where data>='06-Aug-2003' and data<='06-Aug-2003'
Results: 0 Rows
What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')What is the time portion of the field ?
What does this give you
SELECT * FROM TEST
where data between '20030806' and '20030807'
OR
SELECT * FROM TEST
where CONVERT(char(8),data,112) = '20030806'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Dragos Hilbert" <dragos_hilbert@.yahoo.co.uk> wrote in message
news:eMh$KHAXDHA.652@.TK2MSFTNGP10.phx.gbl...
> SQL Server2000 SP3
> Field date is smalldatetime
> I have 8 rows with data='06-Aug-2003'
> SELECT * FROM TEST
> where data between '06-Aug-2003' and '06-Aug-2003'
> Results: 0 Rows
>
> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
> Results: 0 Rows
> What can I do? (I do not want to use '06-Aug-2003' and '07-Aug-2003')
>
>|||> SELECT * FROM TEST
> where data>='06-Aug-2003' and data<='06-Aug-2003'
SELECT * FROM test
WHERE data>='20030806' and data<'20030807'
(a) between can be ambiguous, I tend to avoid it for datetime queries.
(b) since datetime columns have time also, the above is converted to
midnight, so you won't likely have rows where "data" is between a day at
midnight and the same day at midnight.
(c) never use SELECT * in production code.
(d) consider changing the column name. "data" is not very descriptive at
all; I would never guess that it is a datetime column...

Best way to apply SP3 in Replication

We have a Pub, Dist and Sub Server all running under SP2 with worm update and are looking to apply SP3a . Replication is transactional with heavy usage . What is the best sequence to apply SP3
can it be spread out in 3 weekends or has to be done all together ?
ThanksI don't know why you have to spread the upgrade into three weekends. It shouldn't take that long unless you have many db servers. The SP3 upgrade I did to all db servers, with some minor problems of course, was done in one weekend. We are fully replicated environment. You need to shut down all subscriptions or just stop the SQL Agent on the subscriber(assuming it is a pull), no need to shut down the machine tough. The you shut down the Distributor, if it is on a different machine. Apply SP3 on Publisher first, then Distributor, and Subscriber. Remember to uncheck to autostart SQL Agent so replication agents won't get started incidently. After finished applying SP3, turn the replication back on. If you a certain agents fails, reintialize it. Good Luck.|||Reason is that databses are really huge and We had occasion when SP3 failed on us leaving dbs in SP2.5 LOL . then we spaend 3 hrs with Msoft and installed the SP3 in Debug mode . I am being careful because we get a window between 1:Am to 5:AM only to do any changes and is too short to do all three if something doesn't go well

Thanks|||Thanks ..

Anything specific to do on Publisher other than Backups / Agent Auto-run to manual (and disabling on dist and Subs.)|||With transactional replication, the proper sequence should be distributor, publisher, and subscriber. The distributor should always be upgraded first. Please refer to SQL 2000 books online <\Installing SQL Server\Upgrading to SQL Server 2000: Overview\ Replication and upgrading> for details.|||I will definately follow the guidelines !