April 28, 2008 at 10:25 pm
Dear All,
I have one senario.
I want to list out the last date for each month for the given period.
Say for example,
I have two parameters which will get the input like
'01/Apr/2007' ,'29/Apr/2008'
Then i want to list out the last date for each month for the given period.
Expected Output:
Month Year LastDate
1 2007 31/Jan/2007
2 2007 28/Feb/2007
.
.
.
Upto Current month last date.
Suppose if i give the input like
'01/Apr/2005','01/Jan/2008'
Then it would display the last date for each month for the given period.
Help would be highly appreciated !
karthik
April 29, 2008 at 1:27 am
Hello,
this can be done by using the dateadd and datediff functions like this:
declare @pSampleDate smalldatetime
set @pSampleDate = CONVERT(SMALLDATETIME, '2008-01-01',120)
Select @pSampleDate, dateadd(dd,-1,
dateadd(mm,
datediff(mm,0,@pSampleDate)
+1,0)
)
Regards
w. Lengenfelder
April 29, 2008 at 2:15 am
To get all of the dates in the range all at once, then the following will do it for you...
--===== Here are the two parameters you wanted
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '01/Apr/2007',
@DateEnd = '29/Apr/2008'
--===== Derived table "d" finds the dates using a Tally table as a counter.
-- The outer select formats it.
SELECT FmtDate = STUFF(CONVERT(VARCHAR(11),TheDate,103),4,2,LEFT(DATENAME(mm,TheDate),3))
FROM (--==== This finds the last day of every month touched by
-- the two parameters. The formulas find the start of
-- of each "next" month and then backs off by 1 day
SELECT TheDate = DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)-1
FROM dbo.Tally t
WHERE DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)-1
<= DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0)-1 ) d
Be very aware that I never recommend formatting dates in SQL Server... they should be formatted in the GUI or Report Generator so that local settings prevail.
Also, if you don't already have a Tally Table, here's how to make one...
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2008 at 2:48 am
Jeff,
It is not working. I got the below error message.
Function DATEDIFF invoked with wrong number or type of argument(s).
But when i execute it seperately like
select DATEDIFF(MM,'01/apr/2007','01/apr/2008')
its showing 12.
Please help me.
karthik
April 29, 2008 at 4:43 am
Hi,
Here is a function that computes the last day of the month :
create function GetLastDayOfTheMonth(@Date datetime)
returns datetime
as
BEGIN
Return dateadd(day,-1* day(dateadd(month,1,@Date)),dateadd(month,1,@Date))
END
Cheers
April 29, 2008 at 5:31 am
i have executed the below statements.
select dateadd(mm,0,0)
select dateadd(mm,0,-1)
I got the following error.
Function DATEADD invoked with wrong number or type of argument(s).
select datediff (yy, "1984", 0)
Function DATEDIFF invoked with wrong number or type of argument(s).
karthik
April 29, 2008 at 6:05 am
Hi,
try this :
select dateadd(month, 0, 0)
select dateadd(year, 0, 0)
April 29, 2008 at 6:17 am
Jeff,
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '04/Apr/2007', @DateEnd = '29/Apr/2008'
SELECT convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N-1,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N-1,@DateStart))),103),
DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103))
FROM dbo.Tally
Above code is working fine. But it is running like a car without break:). Yes,It is giving first Day & LastDay perfectly without break. I need to put break point based on our @DateEnd.
Output:
Apr 1 2007 12:00AM Apr 30 2007 12:00AM
May 1 2007 12:00AM May 31 2007 12:00AM
Jun 1 2007 12:00AM Jun 30 2007 12:00AM
Jul 1 2007 12:00AM Jul 31 2007 12:00AM
Aug 1 2007 12:00AM Aug 31 2007 12:00AM
Sep 1 2007 12:00AM Sep 30 2007 12:00AM
Oct 1 2007 12:00AM Oct 31 2007 12:00AM
Nov 1 2007 12:00AM Nov 30 2007 12:00AM
Dec 1 2007 12:00AM Dec 31 2007 12:00AM
Jan 1 2008 12:00AM Jan 31 2008 12:00AM
Feb 1 2008 12:00AM Feb 29 2008 12:00AM
Mar 1 2008 12:00AM Mar 31 2008 12:00AM
Apr 1 2008 12:00AM Apr 30 2008 12:00AM
May 1 2008 12:00AM May 31 2008 12:00AM
Jun 1 2008 12:00AM Jun 30 2008 12:00AM
Jul 1 2008 12:00AM Jul 31 2008 12:00AM
Aug 1 2008 12:00AM Aug 31 2008 12:00AM
Sep 1 2008 12:00AM Sep 30 2008 12:00AM
Oct 1 2008 12:00AM Oct 31 2008 12:00AM
Nov 1 2008 12:00AM Nov 30 2008 12:00AM
Dec 1 2008 12:00AM Dec 31 2008 12:00AM
Jan 1 2009 12:00AM Jan 31 2009 12:00AM
Feb 1 2009 12:00AM Feb 28 2009 12:00AM
Mar 1 2009 12:00AM Mar 31 2009 12:00AM
Apr 1 2009 12:00AM Apr 30 2009 12:00AM
May 1 2009 12:00AM May 31 2009 12:00AM
Jun 1 2009 12:00AM Jun 30 2009 12:00AM
Jul 1 2009 12:00AM Jul 31 2009 12:00AM
Aug 1 2009 12:00AM Aug 31 2009 12:00AM
Sep 1 2009 12:00AM Sep 30 2009 12:00AM
Oct 1 2009 12:00AM Oct 31 2009 12:00AM
Nov 1 2009 12:00AM Nov 30 2009 12:00AM
Dec 1 2009 12:00AM Dec 31 2009 12:00AM
Jan 1 2010 12:00AM Jan 31 2010 12:00AM
Feb 1 2010 12:00AM Feb 28 2010 12:00AM
Mar 1 2010 12:00AM Mar 31 2010 12:00AM
Apr 1 2010 12:00AM Apr 30 2010 12:00AM
May 1 2010 12:00AM May 31 2010 12:00AM
Jun 1 2010 12:00AM Jun 30 2010 12:00AM
Jul 1 2010 12:00AM Jul 31 2010 12:00AM
Aug 1 2010 12:00AM Aug 31 2010 12:00AM
Sep 1 2010 12:00AM Sep 30 2010 12:00AM
Oct 1 2010 12:00AM Oct 31 2010 12:00AM
Nov 1 2010 12:00AM Nov 30 2010 12:00AM
Dec 1 2010 12:00AM Dec 31 2010 12:00AM
Jan 1 2011 12:00AM Jan 31 2011 12:00AM
Feb 1 2011 12:00AM Feb 28 2011 12:00AM
Mar 1 2011 12:00AM Mar 31 2011 12:00AM
Apr 1 2011 12:00AM Apr 30 2011 12:00AM
May 1 2011 12:00AM May 31 2011 12:00AM
Jun 1 2011 12:00AM Jun 30 2011 12:00AM
Jul 1 2011 12:00AM Jul 31 2011 12:00AM
Aug 1 2011 12:00AM Aug 31 2011 12:00AM
Sep 1 2011 12:00AM Sep 30 2011 12:00AM
Oct 1 2011 12:00AM Oct 31 2011 12:00AM
Nov 1 2011 12:00AM Nov 30 2011 12:00AM
Dec 1 2011 12:00AM Dec 31 2011 12:00AM
Jan 1 2012 12:00AM Jan 31 2012 12:00AM
Feb 1 2012 12:00AM Feb 29 2012 12:00AM
Mar 1 2012 12:00AM Mar 31 2012 12:00AM
Apr 1 2012 12:00AM Apr 30 2012 12:00AM
May 1 2012 12:00AM May 31 2012 12:00AM
Jun 1 2012 12:00AM Jun 30 2012 12:00AM
Jul 1 2012 12:00AM Jul 31 2012 12:00AM
Aug 1 2012 12:00AM Aug 31 2012 12:00AM
Sep 1 2012 12:00AM Sep 30 2012 12:00AM
Oct 1 2012 12:00AM Oct 31 2012 12:00AM
Nov 1 2012 12:00AM Nov 30 2012 12:00AM
Dec 1 2012 12:00AM Dec 31 2012 12:00AM
Jan 1 2013 12:00AM Jan 31 2013 12:00AM
Feb 1 2013 12:00AM Feb 28 2013 12:00AM
Mar 1 2013 12:00AM Mar 31 2013 12:00AM
Apr 1 2013 12:00AM Apr 30 2013 12:00AM
May 1 2013 12:00AM May 31 2013 12:00AM
Jun 1 2013 12:00AM Jun 30 2013 12:00AM
Jul 1 2013 12:00AM Jul 31 2013 12:00AM
Aug 1 2013 12:00AM Aug 31 2013 12:00AM
Sep 1 2013 12:00AM Sep 30 2013 12:00AM
Oct 1 2013 12:00AM Oct 31 2013 12:00AM
Nov 1 2013 12:00AM Nov 30 2013 12:00AM
Dec 1 2013 12:00AM Dec 31 2013 12:00AM
Jan 1 2014 12:00AM Jan 31 2014 12:00AM
Feb 1 2014 12:00AM Feb 28 2014 12:00AM
Mar 1 2014 12:00AM Mar 31 2014 12:00AM
Apr 1 2014 12:00AM Apr 30 2014 12:00AM
May 1 2014 12:00AM May 31 2014 12:00AM
Jun 1 2014 12:00AM Jun 30 2014 12:00AM
Jul 1 2014 12:00AM Jul 31 2014 12:00AM
Aug 1 2014 12:00AM Aug 31 2014 12:00AM
Sep 1 2014 12:00AM Sep 30 2014 12:00AM
Oct 1 2014 12:00AM Oct 31 2014 12:00AM
Nov 1 2014 12:00AM Nov 30 2014 12:00AM
Dec 1 2014 12:00AM Dec 31 2014 12:00AM
Jan 1 2015 12:00AM Jan 31 2015 12:00AM
Feb 1 2015 12:00AM Feb 28 2015 12:00AM
Mar 1 2015 12:00AM Mar 31 2015 12:00AM
Apr 1 2015 12:00AM Apr 30 2015 12:00AM
May 1 2015 12:00AM May 31 2015 12:00AM
.
.
.
.
It is going till 11000 rows.
.
karthik
April 29, 2008 at 6:38 am
Jeff,
I have refined the above code as
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '04/Apr/2007', @DateEnd = '29/Apr/2008'
SELECT convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N-1,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N-1,@DateStart))),103),
DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103))
FROM dbo.Tally
where DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103)) <= @DateEnd
I got the below output:
Apr 1 2007 12:00AM Apr 30 2007 12:00AM
May 1 2007 12:00AM May 31 2007 12:00AM
Jun 1 2007 12:00AM Jun 30 2007 12:00AM
Jul 1 2007 12:00AM Jul 31 2007 12:00AM
Aug 1 2007 12:00AM Aug 31 2007 12:00AM
Sep 1 2007 12:00AM Sep 30 2007 12:00AM
Oct 1 2007 12:00AM Oct 31 2007 12:00AM
Nov 1 2007 12:00AM Nov 30 2007 12:00AM
Dec 1 2007 12:00AM Dec 31 2007 12:00AM
Jan 1 2008 12:00AM Jan 31 2008 12:00AM
Feb 1 2008 12:00AM Feb 29 2008 12:00AM
Mar 1 2008 12:00AM Mar 31 2008 12:00AM
But still i have a minor problem. It is not showing the @EndDate's Last Date.
Give me your comments and suggestions over the code,also help me to get the @EndDate's Last Date.
All,Inputs are welcome!
karthik
April 29, 2008 at 7:16 am
karthikeyan (4/29/2008)
Jeff,I have refined the above code as
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '04/Apr/2007', @DateEnd = '29/Apr/2008'
SELECT convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N-1,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N-1,@DateStart))),103),
DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103))
FROM dbo.Tally
where DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103)) <= @DateEnd
I got the below output:
Apr 1 2007 12:00AM Apr 30 2007 12:00AM
May 1 2007 12:00AM May 31 2007 12:00AM
Jun 1 2007 12:00AM Jun 30 2007 12:00AM
Jul 1 2007 12:00AM Jul 31 2007 12:00AM
Aug 1 2007 12:00AM Aug 31 2007 12:00AM
Sep 1 2007 12:00AM Sep 30 2007 12:00AM
Oct 1 2007 12:00AM Oct 31 2007 12:00AM
Nov 1 2007 12:00AM Nov 30 2007 12:00AM
Dec 1 2007 12:00AM Dec 31 2007 12:00AM
Jan 1 2008 12:00AM Jan 31 2008 12:00AM
Feb 1 2008 12:00AM Feb 29 2008 12:00AM
Mar 1 2008 12:00AM Mar 31 2008 12:00AM
But still i have a minor problem. It is not showing the @EndDate's Last Date.
Give me your comments and suggestions over the code,also help me to get the @EndDate's Last Date.
All,Inputs are welcome!
Why don't you run the code I gave you? It works perfectly as is...
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2008 at 7:52 am
Jeff,
Thanks a lot for your help!
I have purified one more time.
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '07/Apr/2008', @DateEnd = '29/Dec/2008'
SELECT Month = convert(varchar,DatePart(MM,DATEADD(mm,N-1,@DateStart))),
Year = convert(varchar,DatePart(YY,DATEADD(mm,N-1,@DateStart))),
DateStart = convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N-1,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N-1,@DateStart))),103),
DateEnd = DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103))
FROM dbo.Tally
where DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103)) <= DateADD(MM,1,@DateEnd)
It is giving the perfect result.
Month Year DateStart DateEnd
4 2008 Apr 1 2008 12:00AM Apr 30 2008
5 2008 May 1 2008 12:00AM May 31 2008
6 2008 Jun 1 2008 12:00AM Jun 30 2008
7 2008 Jul 1 2008 12:00AM Jul 31 2008
8 2008 Aug 1 2008 12:00AM Aug 31 2008
9 2008 Sep 1 2008 12:00AM Sep 30 2008
10 2008 Oct 1 2008 12:00AM Oct 31 2008
11 2008 Nov 1 2008 12:00AM Nov 30 2008
12 2008 Dec 1 2008 12:00AM Dec 31 2008
karthik
April 29, 2008 at 8:57 am
karthikeyan (4/29/2008)
Jeff,Thanks a lot for your help!
I have purified one more time.
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '07/Apr/2008', @DateEnd = '29/Dec/2008'
SELECT Month = convert(varchar,DatePart(MM,DATEADD(mm,N-1,@DateStart))),
Year = convert(varchar,DatePart(YY,DATEADD(mm,N-1,@DateStart))),
DateStart = convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N-1,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N-1,@DateStart))),103),
DateEnd = DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103))
FROM dbo.Tally
where DateAdd(DD,-1,convert(Datetime,'01'+'/'+convert(varchar,DatePart(MM,DATEADD(mm,N,@DateStart)))+'/'+convert(varchar,DatePart(YY,DATEADD(mm,N,@DateStart))),103)) <= DateADD(MM,1,@DateEnd)
It is giving the perfect result.
Month Year DateStart DateEnd
4 2008 Apr 1 2008 12:00AM Apr 30 2008
5 2008 May 1 2008 12:00AM May 31 2008
6 2008 Jun 1 2008 12:00AM Jun 30 2008
7 2008 Jul 1 2008 12:00AM Jul 31 2008
8 2008 Aug 1 2008 12:00AM Aug 31 2008
9 2008 Sep 1 2008 12:00AM Sep 30 2008
10 2008 Oct 1 2008 12:00AM Oct 31 2008
11 2008 Nov 1 2008 12:00AM Nov 30 2008
12 2008 Dec 1 2008 12:00AM Dec 31 2008
No, not "purified"... you changed the requirements, the date format, and you resorted to slower character based techniques. I'll try to get back to this tonight..
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2008 at 8:58 am
In the meantime, please try to figure out what dateformat you'd like for startdate and enddate... I recommend not using one at all.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2008 at 7:00 pm
This is what I mean...
(No character based date conversions) + (No concatenation) = Very high speed short code.
--===== Here are the two parameters you wanted
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '07/Apr/2007',
@DateEnd = '29/Dec/2008'
--===== Derived table "d" finds the dates using a Tally table as a counter.
-- The outer select formats it.
SELECT Month = DATEPART(mm,DateStart),
Year = DATEPART(yy,DateStart),
DateStart, --You can format a date here, but I wouldn't if it's going into a table
DateEnd --You can format a date here, but I wouldn't if it's going into a table
FROM (--==== This finds the first and last day of every month touched by
-- the two parameters. The formulas find the start of
-- of each "next" month and then backs off by 1 day
SELECT DateStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N-1,0),
DateEnd = DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)-1
FROM dbo.Tally t
WHERE DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)-1
<= DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0)) d
--Jeff Moden
Change is inevitable... Change for the better is not.
April 29, 2008 at 7:16 pm
As a bit of a side-bar, if you intend to join DateTime datatypes to this table, the preferred method of creating such a table would be...
--===== Here are the two parameters you wanted
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '07/Apr/2007',
@DateEnd = '29/Dec/2008'
--===== Derived table "d" finds the dates using a Tally table as a counter.
-- The outer select formats it.
SELECT Month = DATEPART(mm,DateStart),
Year = DATEPART(yy,DateStart),
DateStart, --You can format a date here, but I wouldn't if it's going into a table
NextStart --You can format a date here, but I wouldn't if it's going into a table
FROM (--==== This finds the first day of every month touched by
-- the two parameters and the first day of the following month.
SELECT DateStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N-1,0),
NextStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)
FROM dbo.Tally t
WHERE DATEADD(mm,DATEDIFF(mm,0,@DateStart)+t.N,0)-1
<= DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0)) d
And the preferred join would be...
ON somedatetime >= DateStart
AND somedatetime < NextStart
... which would include all of the TIMES for the final day of the month. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply