Adding dates in SQL

I am trying to pull only records that are greater than 1 month prior to today's date. This is what I have so far...

select *
from MyTable
where eventDateStart > '$Now'
order by eventDateStart

$Now is a variable that pulls in today's date. This sql statement delivers only records that have a eventDateStart greater than today. My problem is I do not know how to make it so it only shows records that are 1 month prior.

Any idea how to do this?Have a look at the DATEDIFF function in SQL Server|||This should do it for you:

SELECT * FROM MyTable
WHERE eventDateStart < DATEADD(m, -1, GETDATE())

Cheers

Gary|||Ok, this is frustrating, I can not get the DATEADD function to work for some reason. This is exactly what I want..

select *
from MyTable
where eventDateStart > DATEADD (m,-1,'2004/12/26')

This "SHOULD" return all the events that have a start date greater than November 26,2004 right? Am I crazy or something? If I get rid of the dateadd function and just have this where clause...

where eventDateStart > '2004/12/26'

it returns exactly what it should...all events that have a start date later than the 12/26/2004. But as soon as I try to DATEADD it returns zero rows. What the heck am I doing wrong|||Hi again,

Sorry I screwed up the first time as I should have said:
eventDateStart > DATEADD(m, -1, GETDATE())
instead of
eventDaytStart < DATEADD(m, -1, GETDATE())

In any case I checked your hardcoded version (SELECT DATEADD (m,-1,'2004/12/26') )and my version (SELECT DATEADD(m, -1, GETDATE())) in query analyzer and both give me the expected results.

I don't know if this is typo on your part but your DATEADD version will produce a comparison date of 2004/11/26 not 2004/12/26, so you are likely to get different results. Try it in query analyzer making sure you are using a dateadd that will be the same date as your hardcoded value and see what happens.

If that failds post the entire query/procedure and I'll look at it again because you're right, there is no reason why this should be difficult.

Cheers

Gary

adding article in merge replication

Hi,
our configuration is SQL 2000 (publisher and subscribers as well), merge
replication, pull subscription.
From time to time we add new subscriber.
I need to add new table or stored procedure into current replication
schema. What to do exactly if I want to avoid reinit with total snapshot
transfer for existing subscriptions and any new subscription should be
created with new articles already?
Is following sequence correct?
EXEC sp_addmergearticle
@.publication = @.our_publication,
@.article = N'newarticle',
... @.force_invalidate_snapshot = 1
EXEC sp_refreshsubscriptions
@.publication = @.our_publication
Thanks for an answer or suggestion.
Lenka
Adding a new merge article to the publication will result in an entire
snapshot being created but NOT a reinitialization. Just the new article will
be transferred. Once the article has been added to the publication, you'll
need to also run the snapshot agent to have it propagated.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Adding a table to transactional repliaction

I am adding a table to an existing publication that has one pull subscription
using the steps listed below. When I run the snapshot agent, it does not
recognize the newly added article ("no subscriptions needed initialization").
I WANT TO RUN THE SNAPSHOT FOR JUST ONE TABLE. Do I need to drop and readd
the subscriber or subscription? If so, please let me know the steps.
ALSO, the steps below did work on a QA server but failed in production.
-- Thanks for your help.
exec sp_addarticle @.publication = N'my_PUBLICATION', @.article = N'my_TABLE',
@.source_owner = N'dbo', @.source_object = N'my_TABLE', @.destination_table =
N'my_TABLE', @.type = N'logbased', @.creation_script = null, @.description =
null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
@.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
sp_MSins_my_TABLE', @.del_cmd = N'CALL sp_MSdel_my_TABLE', @.upd_cmd = N'MCALL
sp_MSupd_my_TABLE', @.filter = null, @.sync_object = null, @.auto_identity_range
= N'false', @.force_invalidate_snapshot = 1
GO
select * from distribution..msArticles where article = 'my_TABLE'
go
EXEC sp_refreshsubscriptions
N'my_PUBLICATION'
go
exec sp_reinitsubscription
@.publication = 'my_PUBLICATION',
@.article = 'my_TABLE',
@.for_schema_change = 1,
@.subscriber = 'all'
GO
exec sp_addsubscription
@.publication = 'my_PUBLICATION',
@.article = 'my_TABLE',
@.subscriber = N'SUB_SERVER',
@.destination_db = N'pub_sub_database',
@.sync_type = N'automatic',
@.update_mode = N'read only'
I think you need to run sp_refreshpublications to get it to pick up the
changes.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sbawa" <sbawa@.discussions.microsoft.com> wrote in message
news:F24C7DED-985E-4011-B4E7-F2C5E972F8EC@.microsoft.com...
>I am adding a table to an existing publication that has one pull
>subscription
> using the steps listed below. When I run the snapshot agent, it does not
> recognize the newly added article ("no subscriptions needed
> initialization").
>
> I WANT TO RUN THE SNAPSHOT FOR JUST ONE TABLE. Do I need to drop and
> readd
> the subscriber or subscription? If so, please let me know the steps.
> ALSO, the steps below did work on a QA server but failed in production.
> -- Thanks for your help.
>
> exec sp_addarticle @.publication = N'my_PUBLICATION', @.article =
> N'my_TABLE',
> @.source_owner = N'dbo', @.source_object = N'my_TABLE', @.destination_table =
> N'my_TABLE', @.type = N'logbased', @.creation_script = null, @.description =
> null, @.pre_creation_cmd = N'drop', @.schema_option = 0x00000000000000F3,
> @.status = 16, @.vertical_partition = N'false', @.ins_cmd = N'CALL
> sp_MSins_my_TABLE', @.del_cmd = N'CALL sp_MSdel_my_TABLE', @.upd_cmd =
> N'MCALL
> sp_MSupd_my_TABLE', @.filter = null, @.sync_object = null,
> @.auto_identity_range
> = N'false', @.force_invalidate_snapshot = 1
> GO
> select * from distribution..msArticles where article = 'my_TABLE'
> go
> EXEC sp_refreshsubscriptions
> N'my_PUBLICATION'
> go
> exec sp_reinitsubscription
> @.publication = 'my_PUBLICATION',
> @.article = 'my_TABLE',
> @.for_schema_change = 1,
> @.subscriber = 'all'
> GO
> exec sp_addsubscription
> @.publication = 'my_PUBLICATION',
> @.article = 'my_TABLE',
> @.subscriber = N'SUB_SERVER',
> @.destination_db = N'pub_sub_database',
> @.sync_type = N'automatic',
> @.update_mode = N'read only'
>
|||I tried using object search but unable to find this procedure in any of the
databases. Please help.
Your book on transactional replication is great. But you can probably see
that I haven't gotten too far into it yet.
"Hilary Cotter" wrote:

> I think you need to run sp_refreshpublications to get it to pick up the
> changes.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "sbawa" <sbawa@.discussions.microsoft.com> wrote in message
> news:F24C7DED-985E-4011-B4E7-F2C5E972F8EC@.microsoft.com...
>
>