DateAdd Function not Working for 2007

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DateAdd Function not Working for 2007

Author

Topic

gshanhouse
Starting Member

7 Posts

Posted - 2007-03-02 : 13:57:52

I am having trouble with the following statements - the statement for "PM" works (references 2006); but the statement "MTD" would not work after February 1, 2007 and for "CM" stopped working yesterday (3/1/2007):

Note: There are no launch_dates later than 12/2007 in the dbo.wcdma_markets table.

MTD:
DatePart(Month, dbo.wcdma_markets.launch_date) <= DatePart(Month, dateadd(Month, -2, getdate()))
and DatePart(Year, dbo.wcdma_markets.launch_date) <= DatePart(Year, dateAdd(Month, -2, getdate()))
or
DatePart(Day, dbo.wcdma_markets.launch_date) = 1 and DatePart(Month, wcdma_markets.launch_date) <= DatePart(Month,getdate()))
and DatePart(Year, dbo.wcdma_markets.launch_date) <= DatePart(Year,getdate()))

CM:
DatePart(Month, dbo.wcdma_markets.launch_date) <= DatePart(Month, dateadd(Month, -2, getdate()))
and DatePart(Year, dbo.wcdma_markets.launch_date) <= DatePart(Year, dateAdd(Month, -2, getdate()))
or
DatePart(Day, dbo.wcdma_markets.launch_date) = 1 and DatePart(Month, wcdma_markets.launch_date) <= DatePart(Month, dateadd(Month, -1, getdate()))
and DatePart(Year, dbo.wcdma_markets.launch_date) <= DatePart(Year,dateadd(Month, -1, getdate()))

PM:
DatePart(Month, dbo.wcdma_markets.launch_date) <= DatePart(Month, dateadd(Month, -3, getdate()))
and DatePart(Year, dbo.wcdma_markets.launch_date) <= DatePart(Year, dateAdd(Month, -3, getdate()))
or
DatePart(Day, dbo.wcdma_markets.launch_date) = 1 and DatePart(Month, wcdma_markets.launch_date) <= DatePart(Month, dateadd(Month, -2, getdate()))
and DatePart(Year, dbo.wcdma_markets.launch_date) <= DatePart(Year,dateadd(Month, -2, getdate()))

Any advice would be greatly appreciated!!!

gshanhouse
Starting Member

7 Posts

Posted - 2007-03-02 : 13:34:17

Thanks Ryan - I'll try this!

gshanhouse
Starting Member

7 Posts

Posted - 2007-03-02 : 13:44:30

Ryan,

Please forgive me, but I am new to SQL Server. I can't seem to get the syntax right - as this statement is a sub-select of an inner join:

INNER JOIN CNQ_REGION_CLUSTER_MARKET C on s.testercity = C.marketid
AND s.testercity in (SELECT marketid FROM wcdma_markets WHERE DatePart(Month, dbo.wcdma_markets.launch_date) <= DatePart(Month, dateadd(Month, -2, getdate()))
and DatePart(Year, dbo.wcdma_markets.launch_date) <= DatePart(Year, dateAdd(Month, -2, getdate()))

or

DatePart(Day, dbo.wcdma_markets.launch_date) = 1 and DatePart(Month, wcdma_markets.launch_date) <= DatePart(Month, dateadd(Month, -1, getdate()))
and DatePart(Year, dbo.wcdma_markets.launch_date) <= DatePart(Year,dateadd(Month, -1, getdate()))
)

Any other thoughts?

gshanhouse
Starting Member

7 Posts

Posted - 2007-03-02 : 13:47:32

Sample Data:

Market Launch Date
Chicago 6/13/2006
Dallas Ft. Worth 6/13/2006
Austin 6/13/2006
Houston 6/13/2006
San Antonio 6/30/2006
Northern OH 9/1/2006
Detroit 9/5/2006
Indianapolis 9/5/2006
Arkansas 9/11/2006
Tulsa 10/27/2006
Kansas City 10/27/2006
St. Louis 11/10/2006
Southern OH 11/10/2006
Minnesota 12/21/2006
Wisconsin 12/22/2006
Boston 6/13/2006
Washington DC-Baltimore 6/13/2006

gshanhouse
Starting Member

7 Posts

Posted - 2007-03-02 : 14:07:36

You are right - this must seem a bit ambiguous.

MTD:
I am trying to select markets where the launch date is less than or equal to the 1st of the current month. i.e. today, I only want to report on markets that launched on or before March 1, 2007.

CM:
I am trying to select markets where the launch date is less than or equal to the 1st of the month prior. i.e. today, I only want to report on markets that launched on or before February 1, 2007.

PM:
I am trying to select markets where the launch date is less than or equal to the 1st of two months prior. i.e. today, I only want to report on markets that launched on or before January 1, 2007.

I hope this helps - I really appreciate your help on this one. I am probably over-complicating the code for this.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-02 : 14:29:03

Could you give us some dates and the expected results?

I am able to run each of the "logical" statements by wrapping them in a CASE statement. One issue with the MTD is the parentheses. This might be a cut-n-paste error.

Here is the code I used to test:

DECLARE @DateTable TABLE(LaunchDate DATETIME)
DECLARE @dt DATETIME
SET @dt = '2007-2-1'

INSERT @DateTable
SELECT '2006-6-1' UNION ALL
SELECT '2006-6-15' UNION ALL
SELECT '2006-7-1' UNION ALL
SELECT '2006-7-15' UNION ALL
SELECT '2006-8-1' UNION ALL
SELECT '2006-8-15' UNION ALL
SELECT '2006-9-1' UNION ALL
SELECT '2006-9-15' UNION ALL
SELECT '2006-10-1' UNION ALL
SELECT '2006-10-15' UNION ALL
SELECT '2006-11-1' UNION ALL
SELECT '2006-11-15' UNION ALL
SELECT '2006-12-1' UNION ALL
SELECT '2006-12-15' UNION ALL
SELECT '2007-1-1' UNION ALL
SELECT '2007-1-15' UNION ALL
SELECT '2007-2-1' UNION ALL
SELECT '2007-2-15' UNION ALL
SELECT '2007-3-1' UNION ALL
SELECT '2007-3-15' UNION ALL
SELECT '2007-4-1' UNION ALL
SELECT '2007-4-15'


SELECT LaunchDate,
CASE WHEN
	(
DatePart(Month, LaunchDate) <= DatePart(Month, dateadd(Month, -2, @dt)) 
and DatePart(Year, LaunchDate) <= DatePart(Year, dateAdd(Month, -2, @dt)) 
or 
DatePart(Day, LaunchDate) = 1 and DatePart(Month, LaunchDate) <= DatePart(Month, dateadd(Month, -1, @dt))
and DatePart(Year, LaunchDate) <= DatePart(Year,dateadd(Month, -1, @dt))
	)
THEN 1
ELSE 0
END
FROM @DateTable

For example, that tests the CM code.

-Ryan

gshanhouse
Starting Member

7 Posts

Posted - 2007-03-02 : 14:39:18

This works perfectly! Thank you, Thank you!!!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-02 : 14:56:58

This is probably a case where it is very clear in your head what you are trying to accomplish, but I am struggling to wrap my head around it. Maybe it would be helpful if you put in words what each set of statements is supposed to do rather than code?.? And/or if, using the data you provided and a values for "getdate()" - what rows would you expect to be returned from the data you provided.

gshanhouse
Starting Member

7 Posts

Posted - 2007-03-02 : 15:18:01

I would definately like to understand better how this works. Please reply at your convenience - you both have been very helpful and I don't want to impose.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-03-02 : 15:28:29

Ok, that makes things, much clearer.. :)

MTD:

LaunchDate < DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())) - DATEPART(DAY, GETDATE()) + 2

CM:

LaunchDate <  DATEADD(MONTH, -1, (DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())) - DATEPART(DAY, GETDATE()) + 2))

PM:

LaunchDate <  DATEADD(MONTH, -2, (DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())) - DATEPART(DAY, GETDATE()) + 2))

There may be a more eloquent way to write these date calculations, but, I think they will work for you. If you would like me to explain what I am going, just post back and I can walk you through it.

-Ryan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 16:13:14

Or these

MTD:
LaunchDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP), 1)


CM:
LaunchDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 1)


PM:
LaunchDate < DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 2, 1)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources