.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

SQL Statement to pull Date Effective Record

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :ASP.Net

I have a set of requirments that require me to pull relative supporting data pulled based upon a date effectiveness.  For example, if I where to pull a record for dated 9/1, then I would want to pull the most recent supporting data.  I have accomplished this by using a Case Statement for three scenerios.  The first is where a date is found, the second is where no date is found but records exist, and the third is where no records are found.  The first two work perfectly, the last where where I am having some issue.  The statement reads like this:

    FROM   SupportTable AS Detail
    WHERE ([Master].ID = [Detail].MasterID)
    AND   (Date < @Date)) IS NOT NULL --This parameter can be pased in to the Stored Procedure,
     THEN ((SELECT MAX(Date) AS              --Retrived from the Master Table, or just using GetDate()
    FROM   SupportTable AS Detail
    WHERE ([Master].ID = [Detail].MasterID)
    AND   (Date < @Date))

View Complete Post

More Related Resource Links

select record based on / match selected date

with the sql statement below, i try to select the records based on the selected date, it return no rows from adapter filled datatable. 
however, when i run the query in sql query in sql server studio management, it returns records. 
date format = 09/28/2010

string[] splitDT = txtSelectedDate.Text.Split('/');

            string strRearrangeDT = splitDT[0] + "/" + splitDT[1] + "/" + splitDT[2];

using (SqlConnection sqlconn = new SqlConnection(connstring))
                DataTable dt = new DataTable();
                string strSQL = "SELECT userclaim_details3.id, userclaim_details3.userclaimid, userclaim_details3.userid, userclaim_details3.childcategorytableid, userclaim_details3.statusid, " +
                         "userclaim_details3.amount AS amountname, userclaim_details3.remark AS remarkname, userclaim_details3.datecreated AS datecreatedname, " +
                         "userclaim_details3.datemodified, approvaltable.id AS Expr1, approvaltable.typename AS statusname, usertable2.id AS Expr2, usertable2.name AS uname, " +
                         "childcategorytable.id AS Expr3, childcategorytable.categor

MDX IIF Statement to compare Moday's Date


Hi, I basically want to caluate the previous week


SELECT Tail(NonEmptyCrossJoin([Dim Date].[Year-Week].[Week Of Year])).Item(0).Item(0).lag(1) ON 0

However, if it's a Sunday then it should be:

SELECT Tail(NonEmptyCrossJoin([Dim Date].[Year-Week].[Week Of Year])).Item(0).Item(0) ON 0

So what I did was the following:

    SELECT IIF(Tail(NonEmptyCrossJoin([Dim Date].[Year-Quarter-Month-Day].[Day Of Week])).&[7]
                ,Tail(NonEmptyCrossJoin([Dim Date].[Year-Week].[Week Of Year])).Item(0).Item(0).lag(1)
                ,Tail(NonEmptyCrossJoin([Dim Date].[Year-Week].[Week Of Year])).Item(0).Item(0)
                ) ON 0
    FROM OLAP_Cube

However, I get The syntax for '&' is incorrect.

Any ideas?









Correct Syntax for a Select statement with a date in VB.net 2008 web developer


What is the correct syntax in VB.net 2008 web developer in SQL to do a WHERE clause where the data is >= to the current date? I can't figure out how you pass now into the SQL statement.

Returning record from History Table as it would have existed at a specific date/time


I have a table that uses a trigger to save changes (old values) to a history table. This process is working fine, and I have reports that detail the history of a record for the end user. My problem is that the end user now wants a report that will retrieve how the record looked at a specific date/time.

Source Table:

Id	First	Last	Active	Date
1	John	Doe	1	10/25/2010
2	Jane	Smith	1	10/25/2010

History Table:

Id	SourceId	First		Last	Active	Date
1	1		Jonathon	NULL	NULL	5/1/2000
2	2		NULL		Gray	NULL	10/1/2000
3	2		NULL		Smith	NULL	6/15/2003
4	2		NULL		Doe	NULL	11/23/2008

If the user queries for SourceId 2 on 1/1/2009, they need to get:

Id	First	Last	Active
2	Jane	Smith	1

I've used COALESCE searching multiple fields, but I need to figure out how to

Record Count on MERGE Statement on Insert,Update,Delete


HI All,

How can I get the numbers of records affected in the Merge statement, INSERT,UPDATE,DELETE  separately and store it in a variable so I can get it in the application side? 



khrizz tell

What is the code to say if the select statement of sqldatasource1 control has no record, then..


What is the code to say:-

 if the select statement of sqldatasource1 control has no record, then..

Pls help... thanks




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:

GridView inline Master/Detail record display

This code drop is part of a smash and grab series. If you're in a rush, you can just grab this code and insert it into your application, no understanding is required. When you have some time (ya, right) you may want to review the source code.

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.

Built-in Functions - Date and Time Functions

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

Essential SQL Server Date, Time and DateTime Functions

The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.

Date/Time Conversions Using SQL Server

There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

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