Cross Apply & Date - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Cross Apply & Date

Author

Topic

kappa02
Yak Posting Veteran

65 Posts

Posted - 2014-10-20 : 10:11:47

select stuff(convert(varchar(30),Givendate,107),1,3,DATENAME(MM,DATEADD(month,2,Givendate)))
from dateTBL
Cross Apply(
Select DATENAME(WK,DATEADD(week,-1,Givendate)) as Newdate
where id = XXX
) as alias_name

The Stuff statement is taking a given date out two months in advance But I call myself doing in the Cross Apply section is now subtracting a week from that given date but that is not happening my result is still just a date that's two months in advance. I'm getting December 20, 2014 instead of December 13, 2014 (using todays date as input).

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-20 : 10:56:12

can you post some examples of Givendate and what you need your query to do?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-20 : 10:56:51

I didn't quite understand what you are doing - if you want to add two months and then subtract one week, you could do it like this:

SELECT DATEADD( wk, -1, DATEADD(mm,2,GivenDate))
FROM dateTBL;

kappa02
Yak Posting Veteran

65 Posts

Posted - 2014-10-20 : 11:27:11

Yes that is exactly what I'm trying to do but I wanted the month name to be spell out, that why I was using the stuff command.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-20 : 13:47:28

You can convert the resulting expression of the query I posted previously

SELECT CONVERT( VARCHAR(30), DATEADD( wk, -1, DATEADD(mm,2,GivenDate)), 107)
FROM dateTBL;

kappa02
Yak Posting Veteran

65 Posts

Posted - 2014-10-20 : 16:14:32

This is what I did and it seems to work (98% of the time):

SELECT STUFF(CONVERT(varchar(12),DATEADD(wk, -1,getdate()),107),1,3,DATENAME(MM,DATEADD(month, 2, getdate())))

When it doesn't work is when the given date is during the end of the year, it calculate the month & day but the year does not increase like it should. if my input value is 11/01/2014 the result should be January 25, 2015 instead I get January 25, 2014.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-20 : 16:24:53

Can you post some examples of Givendate and what you need your query to do?

As I had posted earlier, I thought you wanted to add two months and then subtract one week. If you do that with GivenDate = Nov. 1, 2014: Add two months, and you get Jan 1, 2014. Subtract 1 week and you get December 25, 2014. So I misunderstood what you are looking for. Perhaps this?

SELECT CONVERT(VARCHAR(32), DATEADD(mm,DATEDIFF(mm,0,GivenDate)+3,-7), 107)
FROM dateTbl;

kappa02
Yak Posting Veteran

65 Posts

Posted - 2014-10-21 : 00:14:19

if given date is 10/21/2014, the result should be December 14 2014:
Given date 11/01/2014 result should be December 25 2014
Given date 12/01/2014 result should be January 25 2015

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-10-21 : 14:24:55

Now I am even more confused :) The query I posted on 10/20/2014 at 13:47:28 does exactly that. Didn't that work for you?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-10-21 : 15:27:44

I believe the issue is Dec versus December

djj

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-10-21 : 15:55:20

[code]
select stuff(convert(varchar(30),newdate,107),1,3,DATENAME(MM,newdate))
from dateTB
cross apply(
Select DATEADD(Week, -1, DATEADD(Month,-2,givendate)) as Newdate
--where id = XXX
) as alias_name
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-26 : 06:25:19

[code]-- Prepare sample data
DECLARE @Sample TABLE
(
theDate DATE NOT NULL
);

INSERT @Sample
(
theDate
)
VALUES ('20141021'),
('20141101'),
('20141201');

-- SwePeso solution
WITH cteDate(theDate)
AS (
SELECT DATEADD(DAY, -7, DATEADD(MONTH, 2, theDate))
FROM @Sample
)
SELECT theDate,
DATENAME(MONTH, theDate) + ' ' + DATENAME(DAY, theDate) + ' ' + DATENAME(YEAR, theDate) AS SwePeso
FROM cteDate;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources