.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Display the Sum of hours and Month Year using sql Query

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :ASP.Net
Hi,  I have,hours(in varchar),Id in table tbl_x ,i need the sum of hours, monthyear(eg. mar 2010)for the last three months.But with two separate queries i am getting these results.but actually i need in the following format   Month/Year      TotalHours-----------------    ----------------Mar 2010            0400Apr 2010             0450-----------------------------------to get month/Year i am using this querySELECT right(convert(varchar, fromdate, 106), 8) from tbl_x where id='11101' for the sum of hours SELECT SUM(CAST(hours AS INT))AS TOTALHRS FROM tbl_x WHERE id='11101'

View Complete Post

More Related Resource Links

I need to display and group by month/year


I have this date format from



(CHAR(10),(INVC_DT),112) = 20090102 

but I just need the month and year in order to do the aggregations with...

sql query for month and year of date

I have a table with smallint columns for day,month,year. Now I want to select data lets say for month 10,year 2009 to month 2,year 2010. Can anyone help me in writing this query?

Need Help in a query... How to Display Last Record For a Given Month


Hi All,

I have a table DBGROWTHTABLE with below structure and data.

USE [ibmdba]


/****** Object: Table [dbo].[DBGROWTHTABLE] Script Date: 10/22/2010 11:54:02 ******/








	[TableEntryID] [int] IDENTITY(1,1) NOT NULL,

	[EntryDate] [smalldatetime] NOT NULL,

	[MachineName] [varchar](100) NULL,

	[InstanceName] [varchar](100) NULL,

	[DBNAME] [varchar](100) NULL,

	[DBsizeMB] [bigint] NULL,

	[unallocMB] [bigint] NULL,

	[reservedMB] [bigint] NULL,

	[dataMB] [bigint] NULL,

	[indexMB] [bigint] 

query - generate month, year (count) for blog archive


hi guys.

from my sql blogs table, I want to generate blog archive section on my master page. like;

january, 2010 (20)

fabruary, 2010 (35)

I cannot come up with a linq to sql query on this. do you have any idea how it can be done ?

Matrix Report With Month/Year Columns - Display date even when there are no records for that month


I've created a matrix report that displays the quantity of different products  set to expire by month/year. The stored procedure returns records for a variety of products and each record contains an expiration date. If there are no products/records that contain an expiration date of lets say 6/2010 then 6/2010 will not appear as a column in the report.

I need a way to force these month/year columns to appear in the report.


Does anyone have a suggestion on how I can make that happen?


Rick Dowdall


Date format for Year, Year-Month, and Date (Year-Month-Day) where input is not known

I have a requirement to capture historical events in a table. Now some events will have only Year, some Year/Month, and remaining where Date is known. We should be able to store them in the column and be able to sort etc. When publishing the information, we should be able to publish as was the input, Year only, Year-Month only, or Date. I looked at the newer Date data type. I can insert a 4 digit year, but on retrieving it is YYYY-01-01. I do not know if it has input YYYY or YYYY-MM-DD and is indeed a Date or a Year. I am trying to avoid saving the format information in another column or something. For sorting, when records have same Year, I would have another column to do relative sorting... So question is - what are my best options with SQL/Entity Framework combo. And what others have done, when encountering similar - if any. Thanks in advance. --Sharad 

report expression for month and year

Hi , Can anyone help me how to write a report expression to get the following out put? Actually this value is the month of previous month. I want to set default month name of the parameter to previous month. This is sep.So it should display like this; Aug - 10 Thanks shamen

Calender control to display dates of one month only

How to make calender control to display dates of one month only like for month of June it shows 25 to 31 of May and 1 to 5 of July. like this-   25 26 27 28 29 30 31 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 1 2 3 4 5   I want it to be displayed like this- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30   Any ideas

List of Month and Year in SSRS report parameter?

Hi, I have two parameters in my SSRS Report i.e. Month and Year. I want to show the list of all Months in dropdown and years for a particular range say 1995-Current Year.
Can any one suggest me the query for how to get it to work.
Thanks!! MCP

Query acting weird.. larger date range works in 1min 26sec and a smaller range say 1 month - 3 month


I have no clue why my query is acting weird

If i try to run it for 1/1/2010 9/30/2010 the query takes around 1min 26 sec and  return around a million rows

and If i run for 8/1/2010 to 8/31/2010 it takes forever to it...

basically i am getting data from 5 tables and putting it in a temp table and then updating that temp table 2-3 times with some information and then displaying it.

I am stumped as to why it works fast for a longer date range and runs the  snail for a small period of time..

I am in the verge of pulling my hair and going crazy..

any help will be appreciated.




Display a message if query returns no data


I added a text box to my report reading: "No Data Matches Search Criteria".  I tried to add a visibility expression: =iif((Rownumber("SalesCube")=0),1,0)

I get "The Hidden expression usied...returned a data type that is not valid"

What am I doing wrong???


imagebutton shows "submit query" before image display


hi guys,

i have imagebutton's on a page with width/height's set and the imageurl pulls from a database

anyway theres 20'ish images on the same page so it takes a second or 2 for the images to load, unfortunately with the imagebutton, while it's in that limbo stage there's 20 big ugly grey SUBMIT QUERY textboxes in my face.


anyone know how to wkraround this? some sort of default already loaded image, or somehow to turn that off altogether? 

Ignore date and consider month and year to match


Hi there,

It is required to retrieve the records where ONLY Start Month, Year AND End Month, Year are passed. Trying below SQL to get these with no success. 

registrationdate BETWEEN(MONTH((registrationdate) = 01) AND (Year(registrationdate)= 2009)) AND ((Month(registrationdate) =03) AND (Year(registrationdate)=2010))


Help is appreciated.

want to display 24 hours time and date with datatype Datetime (Not String) in SSRS


Hi All,

I want to display 24 hours time and date in SSRS & for that I am using Format(now,"MM/dd/yyyy HH:mm:ss") but I am using this expression in Parameter, where I need to define datatype of that parameter as DateTime, not string. For Expression Format(now,"MM/dd/yyyy HH:mm:ss"), If I use DateTime datatype, It is showing error but working with String datatype.

For example:

I have parameter To Date  and value of this parameter should be 10/11/2010 13:09:16 (To Date: 10/11/2010 23:09:16) , not in AP or PM format and datatype of this parameter, want to keep DateTime that I can select value from Calender.

Please suggest me how I can achieve this?. I don't want to do it in stored Procedure side, want to do it in report side.

Thanks Shiven:)

month / year formatted dates


I need to convert an invc_date from 2009-01-01 00:00:00.000 to MM-YYYY


I have tried some of the stuff but not quite working for me yet

Query to Return Rolling 12 Month Totals



I have a table that looks like this:

[Date], [Person], [Value]

22/03/2008, Person A, 3.1

22/03/2008, Person B, 4.2

03/04/2008, Person B, 5.1

04/04/2008, Person B, 3.0

06/04/2008, Person C, 3.0

I need a query to return a table that shows the totals of each person's value for each month of the proceding 12 months from Date X.  So if Date X is 30/04/2008 then my result table would look like this:

[Month], [Total]

March, 7.3

April, 11.1

Thanks in advance.





Get the month and year?


How do I write my SQL so I get this result?

... and so on
... and so on

I'm building a blog and want to have this as links. I have a field in the database "CreateDate".

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend