.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 records based on current date?

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

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

View Complete Post

More Related Resource Links

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)

Return Last 3 Months data based on the DATE


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?

Move up a time hierarchy from current date to return a set of quarters


I have this calculated set to return the previous 3 months.  How do I rewrite to get the last 3 quarters? 



(3,strtomember("[Date].[Month].&[" + Format(Now(),"yyyy-MM") + "-01T00:00:00]").prevmember)


Need to select records based on todays date


I need to select records in my database based on todays date. I have a date field that saves a future scheduled ship date for orders and need to use a select statement that would use todays date as a parameter. I am able to do this by manualy entering the date in the select statement but when I use todays date with inline code in the select statement it does not work. Please look at the code segment below:

Dim strTodaysDate As String
strTodaysDate = "'" + Date.Now.AddDays(9).ToString("MM/dd/yyyy") + "'"

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:DAVINCIORDERTRACKINGConnectionString %>"
        SelectCommand="SELECT * FROM [OrderTracker] WHERE (CONVERT(char(10), ScheduledShipDate, 101) <> '<% =strTodaysDate %>')">


The segment above returns all records. And if I just enter the date manually in the select statement like below it returns the correct records:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:DAVINCIORDERTRACKINGConnectionString %>"

dropdownbox which selects records based on criteria (TODAY, THIS WEEK, THIS MONTH, etc)


I have a dropdownbox which I want to use to search records based on a timescale. The gridview populates results based from that search.

So here is how I want to work this;

If a user selects "today" - a list of records input today are populated. If "this week" is selected, a list of all records added within that week from todays date is populated.

I created a forum on this issue previously but to no avail whatsoever. How do I work around this problem?

Help would be much appreciated

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'?&#

Latest Records depending on Last Modified Date

Hi there,             I have to get the latest records from the table depending on Last Modified date. Table: ID  Function   Apply        Approved    Withdrawn   LastModifiedDate 1    Coding    1/1/2010   15/1/2010   21/1/2010     21/1/2010 3:58:23 1    Coding    1/1/2010    NULL           NULL           21/1/2010 4:58:23 2    Testing    1/1/2010   15/1/2010   21/1/2010    21/1/2010 3:58:23 3    Analysis  1/1/2010    NULL           NULL           21/1/2010 4:58:23   Output is needed on the basis of last modified date if same fucntion comes more than 1 time. else all other functions need which appears once in table. ID  Function  Apply      Approved    Withdrawn        LastModifiedDate 1    Coding   1/1/2010    

AjaxControlToolkit.CalendarExtender - highlight current date

I have used AjaxControlToolkit.CalendarExtender for calendar in my project.but anyhow I am unable to highlight current date  in calendar control.(Note: Current date I mean is today's date and not the selected date.)e.g.( If today is 15th August 2010, and I have selected 24 August 2010. then when I press calendar icon, I should see 15 august highlighted with different look or same as selected date 24 highlighted in August month of 2010 page in calendar popup.)Please guide me to accomplish the taskThanks in advance for support.

How to insert the Current date and time in to SQL Table..

Hello Members,              I have create the table as per the following ..create table company(  empname varchar(30),  empid int,  joindate smalldatetime)I tried,insert into table values('Kumar',202, ??????)I want to insert Current date and time into table Company.....Please give me the solution...Thanks.. 

How to get "Current Date" on SharePoint Designer for Invoice Ageing Data View Report??

Hi Experts, I would like to know how can i get/show "Current Date" in SharePoint Designer ?? Any good tutorial ? FYI, I need this "Current Date" for my Ageing Data View Report, Purpose To offset with "Invoice Receive Date" to get total days of payment payable for the invoice. Please assists, Thanks

How to create a calcuated member, which will always return the date one month before the currentmemb

I need to create a calculated member in the cube script, which will be subsequently used in various other script calculations.  It needs to return the date, which is 1 month before the currentmember of the Date dimension. I tried something like this: CREATE MEMBER CURRENTCUBE.[Measures].[LastPD]   AS           PARALLELPERIOD([Date].[Calendar].[Month], 1, [Date].[Calendar].currentMember) ,VISIBLE = 1; However when I then try to check the values with this query: SELECT [Measures].[LastPD] on 0, [Date].[Calendar].[Date].members on 1 FROM [TravCSAT] ; I only get NULLs for [LastPD].  Any idea what I am doing wrong?

Left Outer Join fails to return all records

I have a table tblDrop that contains 1067 records for 8/31/2010 and a table tblSoftcount that has 739 records, some with bad serial numbers. I need to match all of the records in tblDrop with those in tblSoftcount. I have a query using Left outer join that has worked for the duration of the application, but has recently stopped returing the full set of records from tblDrop. We have not changed the SQL versions on the server (compatibility level 2005). Here is the query: SELECT   v.SlotNum_ID, s.SlotNum_ID, v.PTNumber, s.PTNumber, v.DropBillAmount AS [Drop], v.ReportDate, v.DateValue     FROM tbl_Drop v LEFT OUTER JOIN tblSoftCount s ON v.SlotNum_ID = s.SlotNum_ID AND v.ReportDate = s.ReportDate WHERE   s.ReportDate = '8/31/2010' AND v.DropBillAmount <> 0 AND s.Slotnum_ID IS NULL;  

how to Update a row with Current system date and add a Prefix to it

Hi Could you please help me out, How to write an update Query to update the row with the current system date and I need to add a prefix like "ABC_" The Column is in VARCHAR datatype. I need it like  ABC_System date update Table1 set column1= getdate() updates the row when i add prefix,it throws Syntax error converting datetime from character string.   Thanks in advance Shan

More than one records return error

Hi Below is my query used in procedure. This query can return no records or more than one records. If this is the case then I wanted to return error message. How to do this? Select Band, SimpleBand into <complexband>, <simpleband> from HHSRS_HazardBand where lowerbound <= <hazardscore> and upperbound > <hazardscore>  

Alert based on date

I have a list of SSL certificates and their expiration dates.  I created a column that calculates the date 45 days before the expiration and then a view that only shows those items where the (expiration - 45) > Today. I then set an alert for any changes to items in that view.  However, I don't get an alert for items that pop into the view...I guess because the item itself did not change.  Is there a way to send an alert based on date?  My SharePoint enviroment is locked down so I can't create custom workflows or connect via Designer.

Search based on creation date

Hello All, I would like to ask which managed property I should use for searching based on creation date. It seems I can't find a managed property to do this. I created my own managed property with the right crawled properties, which works. But I think this should be something out of the box.  Please help, thank you  Anna

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
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