.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

Return Last 3 Months data based on the DATE

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server

Using the getdate() function, how do I select the last WHOLE 3 months,

So for example, for todays date (29/09/2010)

I want to return all records with the date between 01/06/2010 and 31/08/2010

Is this possible?

View Complete Post

More Related Resource Links

Export Data to Excel Based on Date Range

I have data from multiple tables that I would like to display in a gridview via a stored procedure.  I need to select the data based on a date range and display it.  Once displayed I need to include a button that would allow the user to export the gridview data to an excel spreadsheet.  Does anyone have an out of the box solution or know of a tutorial I can use to accomplish this feat?  I am currently using 2.0 for this project. I am newish to .net. Thanks in advance. Here is my stored procedure that calls the data from multiple tables.USE [DATABASENAME] GO /****** Object: StoredProcedure [dbo].[GetUsersAll] Script Date: 09/03/2010 08:20:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUsersAll] AS SELECT aspnet_Users.UserName, aspnet_Membership.Email, Team.Name, MemberTeamRole.InsertDate, MemberTeamRole.ManagerApprovedDate, MemberTeamRole.RegistrarApprovedDate, MemberTeamRole.RoleName, MemberInfo.firstname, MemberInfo.lastname FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN MemberInfo ON aspnet_Users.UserId = MemberInfo.memberid LEFT OUTER JOIN MemberTeamRole ON aspnet_Membership.UserId = MemberTeamRole

return records based on current date?


I tried finding a QA which already addressed this idea, but with no luck. Sorry if it's been addressed before.


I have a table where sales pricing is managed; it contains history of pricing where every price record has an 'effective date'. Currently when a user needs the 'active' price for a customer/item combination, the table is queried by the record that has the most recent 'EffectiveDate' before GETDATE(). Here is the def for table 'tim_PriceSheet'

ItemKey int, not null

EffectiveDate datetime, notnull

CurrID varchar(3)

ListPrice decimal (15,5)

The primary key is set on ItemKey, EffectiveDate

Sample Data:


ItemKey	EffectiveDate	    CurrID	ListPrice
45	2010-07-01 00:00:00.000	CAD	100.00000
45	2010-09-14 00:00:00.000	CAD	110.00000
45	2010-10-01 00:00:00.000	CAD	112.00000
63	2010-09-01 00:00:00.000	CAD	45.00000
63	2010-09-01 00:00:00.000	USD	45.00000
63	2010-09-18 00:00:00.000	CAD	40.00000
63	2010-10-01 00:00:00.000	USD	40.00000
63	2010-10-15 00:00:00.000	CAD	41.00000
63	2010-10-21 00:00:00.000	USD	41.00000

My challenge is this:

I want to return a record set which shows the current price for each distinct item and currency code; in the example below the result would be based on the date being September 16, 2010

Not sure of the correct logic to return records based on date in my WHERE clause


I have a table that contains records with a start and end date.. within our application we have another table that contains a retention number.. its a number 1 thru 100 or -1 indicating retain forever.

Now i need to pull back all records that are in the table that have a deviceID = 231 and that have a startdate < the retention period.. So what i was expecting was records that have a startdate less than the retention time from todays date..

I have the following 2 queries that return the same results.. and not really sure which logic would be correct.. for this example, there are 15 records for that ID.. and 2 of which are older than 20days ago. So which logic should i use?

DECLARE @retention_time int
DECLARE @id bigint

SET @retention_time = '20'
SET @id = '231'

SELECT * FROM DeviceInfo WHERE Id = @id AND DATEDIFF(day, startDate,GETDATE()) > @retention_time
SELECT * FROM DeviceInfo WHERE Id = @id AND startDate < (GETDATE() - @retention_time)

Data Types - Date and Time in SqlServer

Date and time values can be stored with either the DATETIME or SMALLDATETIME data type. The difference between the two is that SMALLDATETIME supports a smaller range of dates and does not give the same level of precision when accounting for time. The DATETIME data type can hold values from January 1st of 1753 to December 31st of 9999. The time is stored to the 1 three hundredths of a second and each value takes up 8 bytes of storage. The SMALLDATETIME data type can hold values between January 1st 1900 and June 6th of 2079. The time is tracked to the minute and each value takes up 4 bytes of storage. The majority of business applications can live happily with SMALLDATETIME, however, if you are in an environment where each second matters or you need to make estimates to the distant future (or past) then you have to resort to DATETIME. If you fail to specify the time when inserting a value into a DATETIME or SMALLDATETIME column, a default of midnight is used. If you fail to specify the date portion the default of January 1, 1900 is used.

Date and Time Data Types and Functions

The following sections in this topic provide an overview of all Transact-SQL date and time data types and functions. For information and examples that are common to date and time data types and functions

ListView DataPager with SQL-Statements that already return only paged data.



As far as I understand the ListView-Control and the DataPager-Control, they work like this: The ListView gets its datarows from its data source, then the DataPager causes the ListView to display only a subset of those rows.

So what, when I want to use custom databinding for the ListView, that *already* gets the certain subset from SQL-Server (for better performance). Can I and how can I use the DataPager then?

Thanx, S.

How to get data from table based on four table



I need to know how to show top 30 records from four table

with fastest speed.. in ms sql server 2005..

hope You do the needfull


Posting data to ASMX and return Object for jQuery


I am using fluent NHibernate with WebForms and I am trying to build a page where I allow a user to post a status update on their profile page and I a using a .asmx WebService to post the data to the database and then returnig a StatusUpdate instance to the page to be used by jQuery. I have a couple problems.

1) First off when I return a string from the WebService(i was testing) the textbox where the user enters their status doesnt empty the contents. And since the page doesnt refresh even if I manally clear out the textbox and put in something else, it still posts the previous status to the database again. How do I fix this?

2) Secondly, when I return the StatusUpdate Object from the Webservice I cant the results to display anything. Like I sad, Im using jQuery to make an AJX call to the WebService.

Here is my code:

User Profile Page:

Status: <asp:Label ID="status" runat="server"></asp:Label><span style="font-size:.7em;"><asp:Label ID="statustime" runat="server"></asp:Label></span><br />
<textarea id="statusBox" rows="3" cols="40"></textarea><br />
<input type="button" value="Update Status" onclick="SetStatus(); return false;

Beyond ASP: XML and XSL-based Solutions Simplify Your Data Presentation Layer


The combination of XML and XSL can provide a powerful alternative to ASP development. This article presents arguments for building even small-scale Internet applications on the XML model. An example written with traditional ASP programming is compared to the same example written with XML and XSL in order to show the benefits of this approach. The example is followed by nine good reasons to make the switch. These reasons include separation of presentation and data, reusability, extensibility, division of labor, enhanced testing, and legacy integration. The XML/XSL solutions described hold the promise of greater simplicity, flexibility, and durability than ASP solutions built the traditional way.

Scott Howlett and Jeff Dunmall

MSDN Magazine November 2000

change calendar date backColor base on data from db


I want to  change calendar date backColor base on data from db,  I only have vistual 2003 !

on mean time I have

<asp:DataGrid id="DataGrid1" style="Z-INDEX: 102; LEFT: 23px; POSITION: absolute; TOP: 271px"
		    runat="server" Font-Size="XX-Small" Font-Names="Verdana" AutoGenerateColumns="False" Visible="False">
           <asp:BoundColumn DataField="subject" HeaderText="Title"></asp:BoundColumn>
           <asp:BoundColumn DataField="posttimestart" HeaderText="Post date"></asp:BoundColumn>


Sub DayRender(ByVal source As Object, ByVal e As DayRenderEventArgs)
        Dim i As Integer
        For i = 0 To DataGrid1.Items.Count - 1
            If (DataGrid1.Items(i).Cells(1).Text = e.Day.Date) Then
                e.Cell.BackColor = Color.DarkOrange

            End If

    End Sub

when I click the day wich have data in db it change  color , but I want the page load it change color !

Thank you!

Insert data based on gridview user value on row.. how to



I have three column comes from database table(Table1) and one column is for entering data for the user let us consider it look like this 

ItemNo ItemName Qty  uservalue

001       A               50       20

002       A               20       5

003       B               50  

004       C               60      10

005       D               40 

006       E               90      15 


If its 300 row from database to gridview it will show 50 record with page navigation..

What i need is i need to insert data in to database table which gridview row has user value..

The output of the another table(Table2) will look like this after insert

ItemNo ItemName   uservalue

001       A                    20

002       A                      5  

Linq join fields for return data


  how would I join fields together?

           return (from c in storedb.Product_Categories

                         where c.Category_Name.Contains(searchText)
                         orderby c.Category_Name
                         select new {
                                    c.Category_Key && " ;" && c.Category_Name // HOW CAN I DO THIS.....

[newbie] How to return a data record from a webservice (wsdl)?



vs2010, .NET 4.0

I have a webmethod which I want to return a data record (for example: name, surename, address, age, zip etc)

I wonder how to accomplish this. I think the solution is about creating an object which contains all the records serialized. And then the webmethod returns this serialized object. I'm not 100% sure how to accomplish this, if some of you good provide me with some tips, tutorial or link to an article explaining it, that would be great

Best practice? Accessing subset of data based on user


I have a DB and associated Asp.net 3.5 web app. The database holds information on our clients. We're adding client access functionality and I need to know how best to restrict user access to data only pertaining to their client.

The clients are stored in a hierarchy and each user can be associated with multiple clients. The user should only be able to access the data for their associated clients and any sub clients thereof.

My initial thought is to add a collection of clients to their profile, then use that to filter all queries; however, my gut instinct tells me that using the profile for such a security function is inadvisable.

Could anyone provide any insight on a best practice for this? I'm sure it must be a pretty common need.


Filtering/selecting rows based on date


I have a gridview, based on thsi datasource:


<asp:SqlDataSource ID="SqlDataSourceAllLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>"               
        SelectCommand="SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?') AS Expr1 FROM tblDelivered FULL OUTER JOIN tblDeliveredInfo ON tblDelivered.delivered_LoadNo = tblDeliveredInfo.deliveryInfo_LoadNr ORDER BY delivered_Date DESC">

Using a dropdownlist, I can filter (by changing datasource) the results based on customer names in a dropdownlist. This datasource, working perfectly, looks like this:


<asp:SqlDataSource ID="sqldataSourceKundeLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>" 
        SelectCommand="SELECT  DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?&#

Binding xml data (which is return from Url) to gridview


I have used Yahoo BOSS API for internal search. This API return string and i want to bind it with gridview. How can i achieve this following is the code

string AppId = "sRP_q9bV34HI_zNyuahPFBGnf1SHyBaIKCtvEjB89EuiNJI6fcfSwAFbwAFSsCV_";
//string Query = "site:way2k.com engineering colleges in pune";
string Query = "colleges in pune"; int NumResults = 10;
     int NumResults = 10;
System.Net.WebClient webClient = new System.Net.WebClient(); request = string.Format( "http://boss.yahooapis.com/ysearch/web/v1/{1}?appid={0}&format=xml&results={2}", AppId, Query, NumResults); byte[] response = webClient.DownloadData(request); string responseXML = System.Text.UTF8Encoding.UTF8.GetString(response); return responseXML;

this code returns result in xml format, how can i bind this XML to gridview so that it looks like search result

SELECT statement to return NULL by matching data from another table.

Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks
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