Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to print month names between dates for the complex query.

Posted on 2011-10-22
39
Medium Priority
?
204 Views
Last Modified: 2012-05-12
I have a table where the records are maintained in this way.
Table name - t
Fields - Name, State, StartDate, FinishDate.
The user will select the parameters
From Finishdate
To Finishdate
if the dates selected is 1 Jan 2011 - 5 May 2011, the output should be as in File attached.
 Query.xlsx
0
Comment
Question by:searchsanjaysharma
  • 22
  • 16
39 Comments
 
LVL 3

Expert Comment

by:ajcheung78
ID: 37010861
I can quite tell exactly what you want without some data.  If you can provide some data from table "t" that would produce the desired result in your spreadsheet that would be most helpful.  Here is a wild guess on what I assume you want:

select SUBSTRING(CONVERT(VARCHAR(20), t.FinishDate, 100), 0,4) AS Month
	, t.State, COUNT(*) AS TotalCount
from t
where t.FinishDate between '01/01/2011' and '05/05/2011'
group by SUBSTRING(CONVERT(VARCHAR(20), t.FinishDate, 100), 0,4), t.State

Open in new window


0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37010945
Here a nicer version. If you want your month name to be only 3 characters use LEFT function:

LEFT(DATENAME(mm,t.FinishDate),3) AS Month
SELECT 
	DATENAME(mm,t.FinishDate) AS Month,
	t.State, 
	COUNT(*) AS TotalCount
FROM 
	YourTable t
WHERE 
	t.FinishDate between '01/01/2011' and '05/05/2011'
GROU BY 
	year(t.FinishDate),
	month(t.FinishDate), 
	t.State

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37010948
Sorry, I had a typo. Here:
SELECT 
	LEFT(DATENAME(mm,t.FinishDate),3) AS Month,
	t.State, 
	COUNT(*) AS TotalCount
FROM 
	YourTable t
WHERE 
	t.FinishDate between '01/01/2011' and '05/05/2011'
GROUP BY 
	YEAR(t.FinishDate),
	MONTH(t.FinishDate), 
	t.State

Open in new window

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 27

Expert Comment

by:Zberteoc
ID: 37010952
And let's keep a nice order:
SELECT 
	LEFT(DATENAME(mm,t.FinishDate),3) AS Month,
	t.State, 
	COUNT(*) AS TotalCount
FROM 
	YourTable t
WHERE 
	t.FinishDate between '01/01/2011' and '05/05/2011'
GROUP BY 
	YEAR(t.FinishDate),
	MONTH(t.FinishDate), 
	t.State
ORDER BY
	YEAR(t.FinishDate),
	MONTH(t.FinishDate), 
	t.State

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37010960
One more thing, when you pass dates to SQL use the universal format:

YYYY-MM-DD or even YYYYMMDD, which is even more sure it will work in any settings.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37010963
SELECT
      LEFT(DATENAME(mm,t.FinishDate),3) AS Month,
      t.State,
      COUNT(*) AS TotalCount
FROM
      YourTable t
WHERE
      t.FinishDate between '2011-01-01' and '2011-05-05'
GROUP BY
      YEAR(t.FinishDate),
      MONTH(t.FinishDate),
      t.State
ORDER BY
      YEAR(t.FinishDate),
      MONTH(t.FinishDate),
      t.State
0
 

Author Comment

by:searchsanjaysharma
ID: 37011193
How to correct this in procedure
alter procedure test1
@f  datetime,
@t  datetime,
@s  varchar(200)
as
begin
declare @strqry varchar(2000)
set @strqry='SELECT lEFT(DATENAME(mm,t.aFinishDate),3) AS Month,t.State,COUNT(*) AS TotalCount FROM  r t WHERE t.aFinishDate>='''+@f+''' and t.afinishdate<='''+@t+''' and state='''+@s+''' GROUP BY  YEAR(t.aFinishDate),MONTH(t.aFinishDate),t.State ORDER BY YEAR(t.aFinishDate),MONTH(t.aFinishDate),t.State'
select @strqry
end


It is giving the following error.
Conversion failed when converting datetime from character string.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012630
I don't understand, why on earth would you want to make this a dynamic SQL procedure?

You can simply do it regular, see below. As I said when you pas in a date parameter you have to make sure it is in a universal format:

YYYY-MM-DD HH:MM:SS.mmm

or
YYYYMMDDTHH:MM:SS.mmm

the .mmm part is miliseconds and is optional.
create procedure test1 
(
	@f  datetime,
	@t  datetime,
	@s  varchar(200)
)
as
begin
	SELECT 
		  LEFT(DATENAME(mm,t.FinishDate),3) AS Month,
		  t.State, 
		  COUNT(*) AS TotalCount
	FROM 
		  YourTable t
	WHERE 
		  t.FinishDate between @f and @t
		  and t.[state]= @s
	GROUP BY 
		  YEAR(t.FinishDate),
		  MONTH(t.FinishDate), 
		  t.State
	ORDER BY
		  YEAR(t.FinishDate),
		  MONTH(t.FinishDate), 
		  t.State
end

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012637
By the way, that was the reason for your error, the date you passed in was in an unrecognizable format. Anyway yo should NEVER use dynamic query in a stored procedure if you want to do it right and to take full advantage of the SQL power.

If you really want to do it dynamic, and I can't see any valid reason for this particular example, then do it in your application code but NOT in SQL!
0
 

Author Comment

by:searchsanjaysharma
ID: 37012658
After replacing with actual fields and table

alter procedure test1
(
      @f  datetime,
      @t  datetime,
      @s  varchar(200)
)
as
begin
      SELECT
              LEFT(DATENAME(mm,t.aFinishDate),3) AS Month,
              t.State,
              COUNT(*) AS TotalCount
      FROM
              R3 t
      WHERE
              t.aFinishDate between @f and @t
              and t.[state]= @s
      GROUP BY
              YEAR(t.aFinishDate),
              MONTH(t.aFinishDate),
              t.State
      ORDER BY
              YEAR(t.aFinishDate),
              MONTH(t.aFinishDate),
              t.State
end
Error-Column 'R3.AFinishDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012666
Try this:
create procedure test1 
(
	@f  datetime,
	@t  datetime,
	@s  varchar(200)
)
as
begin
	SELECT 
		  LEFT(DATENAME(mm,(YEAR(t.FinishDate)*100+MONTH(t.FinishDate))*100+1),3) AS Month,
		  t.State, 
		  COUNT(*) AS TotalCount
	FROM 
		  YourTable t
	WHERE 
		  t.FinishDate between @f and @t
		  and t.[state]= @s
	GROUP BY 
		  YEAR(t.FinishDate),
		  MONTH(t.FinishDate), 
		  t.State
	ORDER BY
		  YEAR(t.FinishDate),
		  MONTH(t.FinishDate), 
		  t.State
end

Open in new window

0
 
LVL 27

Accepted Solution

by:
Zberteoc earned 2000 total points
ID: 37012668
If doesn't work try this:
create procedure test1 
(
	@f  datetime,
	@t  datetime,
	@s  varchar(200)
)
as
begin
	SELECT 
		  LEFT(DATENAME(mm,cast((YEAR(t.FinishDate)*100+MONTH(t.FinishDate))*100+1 as date)),3) AS Month,
		  t.State, 
		  COUNT(*) AS TotalCount
	FROM 
		  YourTable t
	WHERE 
		  t.FinishDate between @f and @t
		  and t.[state]= @s
	GROUP BY 
		  YEAR(t.FinishDate),
		  MONTH(t.FinishDate), 
		  t.State
	ORDER BY
		  YEAR(t.FinishDate),
		  MONTH(t.FinishDate), 
		  t.State
end

Open in new window

0
 

Author Comment

by:searchsanjaysharma
ID: 37012670
Arithmetic overflow error converting expression to data type datetime.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012672
See my second post after the one you tried.
0
 

Author Comment

by:searchsanjaysharma
ID: 37012680
Type date is not a defined system type.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012681
My bad. Try this:
create procedure test1 
(
	@f  datetime,
	@t  datetime,
	@s  varchar(200)
)
as
begin
	SELECT 
		  LEFT(DATENAME(mm,cast((YEAR(t.FinishDate)*100+MONTH(t.FinishDate))*100+1 as datetime)),3) AS Month,
		  t.State, 
		  COUNT(*) AS TotalCount
	FROM 
		  YourTable t
	WHERE 
		  t.FinishDate between @f and @t
		  and t.[state]= @s
	GROUP BY 
		  YEAR(t.FinishDate),
		  MONTH(t.FinishDate), 
		  t.State
	ORDER BY
		  YEAR(t.FinishDate),
		  MONTH(t.FinishDate), 
		  t.State
end

Open in new window

0
 

Author Comment

by:searchsanjaysharma
ID: 37012689
Arithmetic overflow error converting expression to data type datetime.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012691
can you post here how do you call that procedure?
0
 

Author Comment

by:searchsanjaysharma
ID: 37012694
alter procedure test1
(
      @f  datetime,
      @t  datetime,
      @s  varchar(200)
)
as
begin
      SELECT
              LEFT(DATENAME(mm,cast((YEAR(t.aFinishDate)*100+MONTH(t.aFinishDate))*100+1 as datetime)),3) AS Month,
              t.State,
              COUNT(*) AS TotalCount
      FROM
              r3 t
      WHERE
              t.aFinishDate between @f and @t
              and t.[state]= @s
      GROUP BY
              YEAR(t.aFinishDate),
              MONTH(t.aFinishDate),
              t.State
      ORDER BY
              YEAR(t.aFinishDate),
              MONTH(t.aFinishDate),
              t.State
end
--test1 '2 May 2011','1 Oct 2011','Assam'
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012696
not the procedure but how you execute it.
0
 

Author Comment

by:searchsanjaysharma
ID: 37012701
I m just testing with this
test1 '2 May 2011','1 Oct 2011','Assam'
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012709
You didn't follow my recommandations in regards to the date formats. Try with this instead:

EXEC test1 '2011-05-02','2011-10-01','Assam'
0
 

Author Comment

by:searchsanjaysharma
ID: 37012730
I same thing can be done by select the text and press F5
0
 

Author Comment

by:searchsanjaysharma
ID: 37012731
I tried with date format also - yyyy mm dd
0
 

Author Comment

by:searchsanjaysharma
ID: 37012733
exec test1 '2011 05 2','2011 10 2','Assam'

Error converting data type varchar to datetime.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012734
Yes, but normally you execute a stored procedure with EXEC. It works without it only if it is the first statement in the batch, which normally isn't. So make a habit to use EXEC.

Anyway that is not the issue but the date format is.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012736
Why don't you try exactly what I am posting?

Use dashes, spaces don't work!
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012740
So try exactly this:

EXEC test1 '2011-05-02','2011-10-01','Assam'
0
 

Author Comment

by:searchsanjaysharma
ID: 37012753
Arithmetic overflow error converting expression to data type datetime.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012773
OK, please show me what dates do you have in that table. Is the FinishDate column even of type datetime or si varchar with dates in text?

Execute this and post the results here:

select top 1 FinishDate from r3 t where FinishDate is not null

if you have spaces in that column you will have to get rid of them and turn them intu valid dates or NULL.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012781
Or better do this:

select FinishDate from r3 t where isdate(FinishDate)=0

if this select statement returns anything then you are in trouble.
0
 

Author Comment

by:searchsanjaysharma
ID: 37012797
2011-02-19 00:00:00.000
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012804
so try this and tell me fi it returns anything:

select FinishDate from r3 t where isdate(FinishDate)=0
0
 

Author Comment

by:searchsanjaysharma
ID: 37012816
Return null
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012836
Sorry, my bad. Try this:
create procedure test1 
(
	@f  datetime,
	@t  datetime,
	@s  varchar(200)
)
as
begin
	SELECT 
		  LEFT(DATENAME(mm,CAST(((YEAR(t.FinishDate)*100+MONTH(t.FinishDate))*100+1) as varchar(8))),3) AS Month,
		  t.State, 
		  COUNT(*) AS TotalCount
	FROM 
		  YourTable t
	WHERE 
		  t.FinishDate between @f and @t
		  and t.[state]= @s
	GROUP BY 
		  YEAR(t.FinishDate),
		  MONTH(t.FinishDate), 
		  t.State
	ORDER BY
		  YEAR(t.FinishDate),
		  MONTH(t.FinishDate), 
		  t.State
end

Open in new window

0
 

Author Comment

by:searchsanjaysharma
ID: 37012918
workinf fine, but returning many resultsets.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 37012971
?!?!??

Absolutely makes no sense. There is no recursion of any kind in that stored procedure. You lost me here.

I can't help unless I see exactly what you are doing. Are you using the dynamic again?

Please clarify this:

"workinf fine, but returning many resultsets"

what result sets when there is only ONE select statement in that procedure?

Can you post here the exact code for it?

Thanks.

I am loosing my patience now.
0
 

Author Comment

by:searchsanjaysharma
ID: 37013083
Ya i know, many thanks for your effort.

0
 

Author Closing Comment

by:searchsanjaysharma
ID: 37013093
ok
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question