May 17, 2012 at 8:41 pm
Hi Guys,
How can i make list of the last day of each month of the year 2011?
Output:
January-2011 :- 31-January-2011 Monday
February-2011:- 28-February-2011 Monday
March-2011:- 31-March-2011 Thursday
April-2011:- 30-April-2011 Saturday
May-2011:- 31-May-2011 Tuesday
June-2011:- 30-June-2011 Thursday
July-2011:- 31-July-2011 Sunday
August-2011:- 31-August-2011 Wednesday
September-2011:- 30-September-2011 Friday
October-2011:-31-October-2011 Monday
November-2011:- 30-November-2011 Wednesday
December-2011: 31-December-2011 Saturday
Please help me out..
May 17, 2012 at 9:17 pm
How about this:
DECLARE @Year INT = 2011;
SELECT TMP.lastDay,
CASE (DATEPART(dw, tmp.lastDay) + @@DATEFIRST) % 7
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 0 THEN 'Saturday'
END
FROM (SELECT DATEADD(DAY,
-1,
CASE M.month+1
WHEN 13 THEN
CAST(CAST(@Year+1 AS VARCHAR(4)) + '0101' AS DATE)
ELSE
CAST(CAST(@Year AS VARCHAR(4)) + REPLICATE('0', 2 - LEN(CAST(M.month+1 AS VARCHAR(2)))) + CAST(M.month+1 AS VARCHAR(2)) + '01' AS DATE)
END) AS lastDay
FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) M(month)) TMP;
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
May 17, 2012 at 9:24 pm
Try this:
declare @YearStart datetime = '01-Jan-2012'
; with numbers as
( select top 12 n = row_number() over(order by (select null))
from sys.columns
)
select LastDayOfEachMOnth = CrsApp.Dt
,[Month-YYYY] = stuff( convert(varchar(30), CrsApp.Dt , 107),4,4,'-')
,Dy = datename(weekday, CrsApp.Dt)
from numbers n
cross apply (select dateadd(DD, -1 , dateadd(MM, n.n,@YearStart))) CrsApp(Dt)
May 17, 2012 at 9:31 pm
codebyo (5/17/2012)
How about this:
SELECT TMP.lastDay,
CASE (DATEPART(dw, tmp.lastDay) + @@DATEFIRST) % 7
Be careful while using @@DateFirst. It is affected by the Language setting of the database.
A quick sample (from BOL)
SET LANGUAGE Italian;
GO
SELECT @@DATEFIRST; -- Yeilds 1
GO
SET LANGUAGE us_english; -- Yields 7
GO
SELECT @@DATEFIRST;
May 17, 2012 at 9:35 pm
Thank you, ColdCoffee.
I totally forgot about DATENAME. :w00t:
DECLARE @Year INT = 2011;
SELECT TMP.lastDay,
DATENAME(WEEKDAY, tmp.lastDay)
FROM (SELECT DATEADD(DAY,
-1,
CASE M.month+1
WHEN 13 THEN
CAST(CAST(@Year+1 AS VARCHAR(4)) + '0101' AS DATE)
ELSE
CAST(CAST(@Year AS VARCHAR(4)) + REPLICATE('0', 2 - LEN(CAST(M.month+1 AS VARCHAR(2)))) + CAST(M.month+1 AS VARCHAR(2)) + '01' AS DATE)
END) AS lastDay
FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12) M(month)) TMP;
EDIT: and you're right about @@DATEFIRST. Not always a language configuration means it's OUR real language. Thanks for pointing that out.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
May 17, 2012 at 9:40 pm
Thanks a lot guys for your quick reply..
Cold Coffee,
Is there any other work around instead of using of cross apply?
Please share your thoughts? Just curious to know..
May 17, 2012 at 9:45 pm
Jay Pete (5/17/2012)
Cold Coffee,Is there any other work around instead of using of cross apply?
Please share your thoughts? Just curious to know..
Yes, just replace CrsApp.Dt with dateadd(DD, -1 , dateadd(MM, n.n,@YearStart)) wherever you see.
The reason for CROSS APPLY is to have a readable code (i have not done any "number of executions" test yet on CROSS APPLY vs typing literals).
May 17, 2012 at 10:10 pm
Jay Pete (5/17/2012)
Hi Guys,How can i make list of the last day of each month of the year 2011?
Output:
January-2011 :- 31-January-2011 Monday
February-2011:- 28-February-2011 Monday
March-2011:- 31-March-2011 Thursday
April-2011:- 30-April-2011 Saturday
May-2011:- 31-May-2011 Tuesday
June-2011:- 30-June-2011 Thursday
July-2011:- 31-July-2011 Sunday
August-2011:- 31-August-2011 Wednesday
September-2011:- 30-September-2011 Friday
October-2011:-31-October-2011 Monday
November-2011:- 30-November-2011 Wednesday
December-2011: 31-December-2011 Saturday
Please help me out..
To return precisely what you asked for...
DECLARE @Year DATETIME;
SELECT @Year = '2011';
WITH
cteDate AS
(
SELECT EOM = DATEADD(mm,number,@Year)-1
FROM master.dbo.spt_values v
WHERE Type = 'P'
AND Number BETWEEN 1 AND 12
)
SELECT DATENAME(mm,EOM)+'-'+DATENAME(yy,EOM)+' :- '
+ DATENAME(dd,EOM)+'-'+DATENAME(mm,EOM)+'-'+DATENAME(yy,EOM)
+ ' '+DATENAME(dw,EOM)
FROM cteDate
;
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2012 at 3:20 am
Jeff Moden (5/17/2012)
To return precisely what you asked for...
DECLARE @Year DATETIME;SELECT @Year = '2011';
WITH
cteDate AS
(
SELECT EOM = DATEADD(mm,number,@Year)-1
FROM master.dbo.spt_values v
WHERE Type = 'P'
AND Number BETWEEN 1 AND 12
)
SELECT DATENAME(mm,EOM)+'-'+DATENAME(yy,EOM)+' :- '
+ DATENAME(dd,EOM)+'-'+DATENAME(mm,EOM)+'-'+DATENAME(yy,EOM)
+ ' '+DATENAME(dw,EOM)
FROM cteDate
;
Pretty compact and living to your performance-oriented standard for sure Jeff!
To avoid annoying you I shall not post my recursive CTE solution to this... today.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 18, 2012 at 6:26 am
dwain.c (5/18/2012)
Jeff Moden (5/17/2012)
To return precisely what you asked for...
DECLARE @Year DATETIME;SELECT @Year = '2011';
WITH
cteDate AS
(
SELECT EOM = DATEADD(mm,number,@Year)-1
FROM master.dbo.spt_values v
WHERE Type = 'P'
AND Number BETWEEN 1 AND 12
)
SELECT DATENAME(mm,EOM)+'-'+DATENAME(yy,EOM)+' :- '
+ DATENAME(dd,EOM)+'-'+DATENAME(mm,EOM)+'-'+DATENAME(yy,EOM)
+ ' '+DATENAME(dw,EOM)
FROM cteDate
;
Pretty compact and living to your performance-oriented standard for sure Jeff!
To avoid annoying you I shall not post my recursive CTE solution to this... today.
Heh... thanks for the feedback, Dwaine. However, I don't actually mind (shouldn't matter even if I did) you posting rCTE solutions for anything. I was a little taken back by your vigor on them but it all fits the primary rules of development... "It Depends" and "A Developer must Know". Just like anything else, they're worth exploring and have some very good application in some areas.
The only time I get really bent out of shape is when someone posts one (or anything for the matter) with certain and authorative sounding claims that something is really fast or "the best way" with no proof... not even a link to some proof. Even if they're correct, I still hate to see such unsupported claims because it just perpetuates myths.
I am getting pooped, though. I've recently taken to asking them to show proof instead of me always taking the time to do a test setup and show the proof instead of me taking the time. It's not working very well, though. It takes them too long and they frequently botch the testing because a lot of folks simply haven't done such a thing before or they don't know how to quickly make a lot of randomized test data.
An example of a "botched" test is that you can't use SET STATISTICS TIME ON to test most scalar functions be it doesn't handle the individual executions very well. Instead of batch showing (for example), 300 milliseconds run time, it might show a 20 second run time. I'm not sure why this happens but my speculation is that SET STATISTICS causes scalar UDFs to be recompiled for each and every row of execution.
As a side bar, the other methods posted would be damned fast even in the face of thousands of rows, as well. I just used a couple of nuances of T-SQL (like the trick with year) to make it look a bit easier.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply