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

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

how can i stop to add records if date is....

Posted By:      Posted Date: October 05, 2010    Points: 0   Category :Sql Server

I have two table

column name: Userid, courseno,roomname


column name: coursestartdate,courseenddate,courseno etc...

I want that, User cannot register to course if he already registered in between dates of other course

I want like,if user registers for course,
for example coursename: sql server

startdate: 10/10/2010
end date:10/11/2010

after registration for this course
if hwe tries to register for another course which startdate or end date must not be inbetween 10/10/2010 and 10/11/2010


View Complete Post

More Related Resource Links

How to stop unauthorized users from sending documents to a Records Center site?


I can't find a way to stop unauthorized users from sending documents to a Records Center site. Can someone please help me? Do i need to configure anything to restrict it to the members of the web submitters group?

I'm still using MOSS2007


The impossibility of doing things depends on your will to do it.

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    

How to stop Page loading after selecting a date in Calendar in aspx page



can anybody plz tell me how to stop re-loading of page after selecting a date from a  calendar control in a aspx page.

I want to show the selected date in a Textbox control but i don't want to re-load the page.




Getting records that 7 days pass today's date


I want to get all records that are 7 days pass today's date and not equal to today's date.  Don't know how to write it so I can get records 7 days old but with this procedure I'm still getting records that are due today.  Hope this makes sense.  Can someone assist me.

select * from libraryrequest
where  duedate > getdate() and duedate != getdate()

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

query wich returns records for each day between start date from table 1 and mutation dates in table



I have to tables with dates, table Employments with all employements of all employees, with start date and end date, and a table Mutations with mutations (e.g in salary) with start dates.

Now I try to write a query which returns a record for each day an employee is in employment, with the correct salary. SO at first, it should return all days between the employment start date and the first mutation date, then the number of days between the first mutation date and the second mutation date etcetc, and at last the number of days between the last mutation date and the employment end date. The number of mutations varies for each employment, and employees cna have multiple employments (history, so not at the same time) in the employments table.

How to do this?

Searching for Records using a Date Value in Textbox


I have two pages, the first is datepicker.aspx and it contains a textbox where I enter in a date in YYYY-MM-DD format and a button that performs a postback to results.aspx.  Results.aspx has a gridview where I'd like to see records from the journal table in my SQL database that have the date I entered back in datepicker.aspx only the gridview never produces results.

I'm using the following query in the datasource, if I test it in the datasource wizard it works and it also works when I run it directly against the database.


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)

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


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

Using a CompareValidator to check input is a valid date

The CompareValidator can do more than just compare two controls. You can also compare it against several of the main .net data types such as Date, Integer, Double and Currency.

To do this you would set Operator="DataTypeCheck" and instead of setting the ControlToCompare or ValueToCompare attributes as you normally would you use the Type="Date" (or any of the data types I have listed above).

How To Set a Date Format In GridView Using ASP.NET 2.0

A very common desire is to set a column of a gridview to display just the month, day and year of a DateTime type. The problem is the by default, the HtmlEncode property of the boundfield attribute (
The problem is that if this field is enabled, you can not pass format information to the boundfield control. That is, if you try the following code, you will not get the desired result.

jQuery Date picker Implementation in ASP.NET

I've posted a wrapper ASP.NET around the jQuery.UI Datepickercontrol. This small client side calendar control is compact, looks nice and is very easy to use and I've added it some time back to my control library.

This is primarily an update for the jQuery.ui version, and so I spend a few hours or so cleaning it up which wasn't as easy as it could have been since the API has changed quite drastically from Marc's original implementation. The biggest changes have to do with the theming integration and the resulting explosion of related resources.

If you want to use this component you can check it out a sample and the code here:

ASP.NET GridView - edit records using JQuery Dialog

There are many ways in which you could modify modular data in Grid View:
1. Inline by making rows editable when u click on Edit
2. By using separate pages for Edit
3. By using popups/dialogs for adding/editing

How to format datetime & date with century?

Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available and the application of date / datetime functions.


Date and Time Functions in SQLSERVER

Date and time functions allow you to manipulate columns and variables with DATETIME and SMALLDATETIME data types.

1 DATEPART Function
2 DATENAME Function
3 DAY, MONTH, and YEAR Functions
5 DATEADD Functions
6 DATEDIFF Function
7 More SQL Server Functions

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