SqlDataSource.FilterExpression: Can't Get It Work With Dates?
Mar 18, 2008
I am using the FilterExpression of an SqlDataSource object to filter the rows of a gridview. It works with searching text values but I cannot for the life of me figure out to get it to work with dates. The following is what I do for character searches: SomeDataSource.FilterExpression = fieldToSearch + " LIKE '%" + SearchTextBox.Text + "%'";
This works. However when I try to search for dates using the following it doesn't work (startDate and endDate are DateTime objects):
SomeDataSource.FilterExpression = fieldToSearch + " BETWEEN #" + startDate.ToString("yyyy-MM-dd") + "# AND #" + endDate.ToString("yyyy-MM-dd") + "#";
Hi on my page: two sqldatasources one has the sqlexperession: SELECT DISTINCT [Skil] FROM [UsersView]UNIONSELECT 'Alles' the other one : SELECT [UserName], [Afdeling], [Skil], [RoepNaam] FROM [UsersView] the first source is bound to a dropdown list. witch I want to use for filtering. the second to a gridview. the code for the dropdownlist selectedindexchanged is:protected void SkillDDL_SelectedIndexChanged(object sender, EventArgs e) { if (SkillDDL.SelectedValue.ToString() == "Alles") { SqlDataSource3.FilterExpression = ""; } else { SqlDataSource3.FilterExpression = "Skil = '" + SkillDDL.SelectedValue + "'"; } } The strange thing is that initially 'Alles' is selected in the dropdown list and I see all the records. then I select an other value in the dropdownlist. and I get the filtered records, like I would expect. then when I select 'Alles' again I keep my last selected records in view. I would expect that the filter is now an empty string and I would get all the records again. what do I do wrong? thanks
I'm not sure exactly how the FilterExpression works. I have a sqldatasource, stored procedure, and GridView. My stored procedure basicly a select statement to populate my gridview, it included the fields I want to filter on. In my codebehind file I build a WHERE Clause based on the entries a user makes. Then I add the my FilterExpr variable to the SqlDataSource1.FilterExpression = FilterExpr. My SqlDataSource has a number of control parameters that match the textboxes a users enters into. My question, I guess is does my stored procedure need the variables matching my controlparameters for my sqldatasource? Or how does this work? My GridView is returning all rows no matter what I enter into the filter textboxes (first, last, etc...) MY SQLSDATASOURCE <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="ClientSearch" SelectCommandType="StoredProcedure"> <FilterParameters> <asp:ControlParameter ControlID="SearchLastName" Name="LastName" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchFirstName" Name="FirstName" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchEmail" Name="Email" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchAddress" Name="Address" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchComment" Name="Comment" PropertyName="Text" ConvertEmptyStringToNull="true" /> </FilterParameters> </asp:SqlDataSource>MY CODEBEHIND Dim FilterExpr As String If SearchLastName.Text = "" And _ SearchFirstName.Text = "" And _ SearchEmail.Text = "" And _ SearchComment.Text = "" And _ SearchAddress.Text = "" Then lblMessage.Text = "You didn't enter any search parameters. Please try Again." Me.GridViewSearch.DataSourceID = "" Exit Sub Else Me.GridViewSearch.DataSourceID = "SqlDataSource1" End If FilterExpr = "" If SearchLastName.Text <> "" Then FilterExpr = FilterExpr & "LastName Like '" & _ SearchLastName.Text & "%" & "' AND " End If If SearchFirstName.Text <> "" Then FilterExpr = FilterExpr & "FirstName Like '" & _ SearchFirstName.Text & "%" & "' AND " End If If SearchEmail.Text <> "" Then FilterExpr = FilterExpr & " Like '" & _ SearchEmail.Text & "%" & "' AND " End If If SearchComment.Text <> "" Then FilterExpr = FilterExpr & "[Comments] Like '" & "%" & _ SearchComment.Text & "%" & "' AND " End If If SearchAddress.Text <> "" Then FilterExpr = FilterExpr & "[Address] Like '" & "%" & _ SearchAddress.Text & "%" & "' AND " End If If Right(FilterExpr, 4) = "AND " Then FilterExpr = Left(FilterExpr, Len(FilterExpr) - 4) End If Try Me.SqlDataSource1.FilterExpression = FilterExpr Me.SqlDataSource1.DataBind() Me.GridViewSearch.DataBind() Me.lblMessage.Text = Me.SqlDataSource1.FilterExpression Catch objException As SqlException Dim objError As SqlError For Each objError In objException.Errors Response.Write(objError.Message) Next End Try End Sub MY SPROCGO ALTER PROCEDURE [dbo].[ClientSearch]
AS SELECT C.ClientID, C.FirstName, C.LastName, A.Address, C.Comments, C.EMail FROM tblClient C INNER JOIN tblClientAddresses A ON C.ClientID = A.ClientID
I have a SQLDatasource control on a web page. It is the datasource for a gridview control.I want the gridview to show all jones if the FirstName textbox is left blank or empty. Right now I have to put a % in the FirstName textbox to get what I want. If I make the FirstNameTextBox empty or remove the % from the FirstNameTextbox it returns all the names in the database no matter what the last name is.How do I get it to work without having to use the % in the FirstName Textbox? THANKS!FilterExpression="LastName LIKE '%{0}%' and FirstName LIKE '%{1}%'"><FilterParameters> <asp:ControlParameter ControlID="LastNameTextBox" Name="LastName" PropertyName="Text" DefaultValue="" /> <asp:ControlParameter ControlID="FirstNameTextBox" Name="FirstName" PropertyName="Text" DefaultValue="" /></FilterParameters> Last Name: Jones___________First Name: %_____________FILTERBUTTON GridviewLast Name First NameBob JonesBill Jones
How does one programmatically retrieve the results from a sqldatasource that has had a filterexpression applied to it? Let me elaborate, here is my code: SqlDataSource1.FilterExpression = "MenuType = 'myfilteredvalue'" Dim _dv As DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView) _dv.Table.TableName = "Menu" Dim ds As New DataSet("Menus") ds.Tables.Add(_dv.Table.Copy()) 'Add relation: ParentID under MenuID Dim relation As New DataRelation("ParentChild", ds.Tables!Menu.Columns!MenuID, ds.Tables!Menu.Columns!ParentID, True) relation.Nested = True ds.Relations.Add(relation) What I'm doing is, I have a sqlDataSource that pulls all the data from my DB. With this data in the sqlDataSourceSource, I want to then populate an XMLDatasource (via a dataset) with only the records I need, i.e. the filter. However, after I apply my filter above, the sqlDataSoruce still contains all the records. I thought maybe if I did a sqlDataSource.Bind() after my SqlDataSource.FilterExpression, but that didn't do anything either.In laymans terms:I want to say something like: dataset = sqldatasource.filter(my filter expression).
I have wrote some codes as follows: Dim sqldatasource3 As New SqlDataSource sqldatasource3.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("aspnetConnectionString").ConnectionString sqldatasource3.SelectCommand = "select stepno,steppersontype,stepname,steptype,fchoice,fforward,schoice,sforward from flow " sqldatasource3.FilterExpression = "stepname = '" & GridView1.SelectedRow.Cells(4).Text.Trim & "'" sqldatasource3.DataSourceMode = SqlDataSourceMode.DataSet Dim dv As DataView = sqldatasource3.Select(DataSourceSelectArguments.Empty()) dv.RowFilter = "stepname = '" & GridView1.SelectedRow.Cells(4).Text.Trim & "'" but i found the filterexpression didn't work. No matter what value GridView1.SelectedRow.Cells(4).Text.Trim be,sqldatasource always return the whole dataset.I wanna know where these code are wrong? Can anyone help?
I facing a problem when i want to modified the sqldatasource.filterExpression while trigger sqldatasource.selecting event. 1 Protected Sub SqlDsProduct_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) 2 Dim SqlDsProduct As SqlDataSource = CType(CompleteGridView1.DetailRows(0).FindControl("SqlDsProduct"), SqlDataSource) 3 SqlDsProduct.FilterExpression = Session("filter") ' filter i add up after user press search button 4 End subActually i'm using the CompleteGridview, which i downloaded from the web. the "SqlDsProduct" i reffering to is inside the CompleteGridView1. I'm using complet grid view because i want the hierarchy look for the gridview. So please help me how i gonna change the filter expression of the 2nd sqldatasource inside detailsTemplate of the completeGridview. Thank you. best regardvince
HiI have a table in my database called 'tblUsers'It contains usernames and a few columns of data.One of them in 'birthday'it is in the format dd/mm (ie 28/04 is 28th of april)it is always 5 characters longIts data type is char in my db.Now I try this<asp:SqlDataSource ID="SqlDataSource8" runat="server" ConnectionString="<%$ ConnectionStrings:IntranetConnectionString %>" SelectCommand="SELECT DisplayName FROM [tblUsers] WHERE ([Birthday] = @ShortDate)"> <SelectParameters> <asp:ControlParameter ControlID="Calendar1" Name="ShortDate" PropertyName="SelectedDate" Type="string" /> </SelectParameters> </asp:SqlDataSource>where the selected date from a calendar control is used.Now it works when the table has a Date column which is off type DateTime but how would I modify it to work like I want?Thanks!
Hello. I need to create a page with asp 3 and sql server 2000 that lists all the records in a table that match a date criterion, but when doing so I get an error "Error Converting datetime from character string"... I've tried this versions of the query:
Code:
SELECT (...) CONVERT(DATETIME,PAYMENTS_RECEIVED.PYR_DATE,103), (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE BETWEEN '20/03/2006' AND '21/03/2006')
and...
Code:
SELECT (...) PAYMENTS_RECEIVED.PYR_DATE, (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE >= CONVERT(DATETIME,'20/03/2006',103)) AND (PAYMENTS_RECEIVED.PYR_DATE < CONVERT(DATETIME,'21/03/2006',103))
I also tried adding " 00:00:00 a.m." to the dates to be converted in the second piece of code. If I recall correctly, "103" is the convertion code meaning "dd/mm/yyyy". That is, I expect people to type in a date like "27/03/2006" and get the table records from that date.
Weirdest thing of all is that, when using the query builder of a sqlsource control in Visual Studio Web Developer Express 2005 this following query works just fine as I expect:
Code:
SELECT (...) PAYMENTS_RECEIVED.PYR_DATE, (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE >= CONVERT(DATETIME, '20/03/2006', 103)) AND (PAYMENTS_RECEIVED.PYR_DATE < CONVERT(DATETIME, ' 21 / 03 / 2006 ', 103))
Both the server and the DB are the same in all cases, but in the first two I run the query from a (working of other queries) vbscript asp 3 page.
I know that similar posts exist already (I've searched for this before posting), but I can't fix it, at least not with the usual answer on this one (that seems to be using the convert function as in the first piece of code). Thanks in advance!!!
Hi all: I have a list of items (actually a relation in which a user has selected an item, along with a rating for the item) in an Access database table, connected to my app with a SqlDataSource and bound to a repeater. The repeater displays the items to the user along with a dropdown box to show the rating, and allow the user to update it. The page connects and displays correctly. My problem is that when the user submits the page and I iterate through the repeater items to update each rating, the updates are not being completed in the database. The update works if I hard-code a value for the rating into the query itself, but not when using an updateparameter (pTaskRating below). In other words if I replace pTaskRating with '5', all the correct records will be found and have their ratings updated to 5. That means that the mySurveyId and pTaskId(DefaultValue) parameters have to be working, because the right records are found, but I can't seem to update records based on the DefaultValue of the pTaskRating parameter, even though I can verify that the DefaultValue is correct by placing a watch on it. It seems that my problem must be in my use of that particular parameter in the query, either in properties of the parameter or in the value assigned to it. I am extremely frustrated - any ideas would be greatly, greatly appreciated. Thanks! Bruck The table I'm pulling from and updating looks like this: SURVEY_ID (Text 50), TASK_ID (Long Int), RATING_ID (Long Int) Here's my ASPX for the main data source: <asp:SqlDataSource ID="sqlTaskSelections" runat="server" ConnectionString='Provider=Microsoft.Jet.OLEDB.4.0;Data Source="abc.mdb";Persist Security Info=True;Jet OLEDB:Database Password=xyz' ProviderName="System.Data.OleDb" SelectCommand="SELECT [SURVEY_ID], [TASK_ID], [RATING_ID] FROM [TBL_TASK_SELECTION] WHERE [SURVEY_ID] = mySurveyId" UpdateCommand="UPDATE [TBL_TASK_SELECTION] SET [RATING_ID] = pTaskRating WHERE ([SURVEY_ID] = mySurveyId) AND ([TASK_ID] = pTaskId)"> <UpdateParameters>
<asp:SessionParameter Name="mySurveyId" SessionField="SurveyId" DefaultValue="" /><asp:Parameter Name="pTaskId" DefaultValue="" /><asp:Parameter Name="pTaskRating" DefaultValue="" /> </UpdateParameters> And here's the repeater (the Task ID and Rating are stored in hidden fields for easy access later): <asp:Repeater ID="rptTaskSelections" runat="server">
<FooterTemplate></td></tr></table></FooterTemplate> </asp:Repeater> And here's the page load and submit VB: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
'BIND / LOAD RATINGS TO DROPDOWN BOXES HEREDim i As IntegerDim cbCurrentRating As DropDownListDim hCurrentRating As HiddenFieldrptTaskSelections.DataSource = sqlTaskSelectionsrptTaskSelections.DataBind()
When I handle a event for selecting to change the value of a parameter it does not use the new value and the selected event is never called. What am I doing wrong?
SELECT .... FROM temp LEFT OUTER JOIN anothertable ON ...
This stored procedure works fine in Management Studio.
I then use this stored procedure in an ASP.NET page via a SQLDataSource object. The ASP.NET code compiles without error, but the result returned is empty (my bounded GridView object has zero rows). From Web Developer, if I try to Refresh Schema on the SQLDATASource object, I get an error: "Invalid object name '#t1'. The error is at the red #1 as I tried putting something else at that location to confirm it.
What does the error message mean and what have I done wrong?
I have a CheckBoxList which is bound to a SQLDataSource and populated with a list of courses. Under this I have a calendar control which is bound to another SQLDataSource control. When the page is first loaded this is empty because no courses are selected. I'd like it so that the user can select one or more course from the CheckBoxList and the appropraite events are loaded into the calendar control bound to the second SQLDataSource. I've tried using FilterExpression but this seems to work on the basis of a complete resultset being loaded and then filtered-out using the FilterExpression. I'm starting with no records and would like to Filter-In data from the database. Is this possible using FilterExpression?
I have a some search components to filter a GridView follows: <asp:DropDownList ID="ddFilterType" runat="server" ToolTip="Select field to search"> <asp:ListItem Selected="True">(ALL)</asp:ListItem> <asp:ListItem>SKU</asp:ListItem> <asp:ListItem>Vendor Name</asp:ListItem> <asp:ListItem>Item Name</asp:ListItem></asp:DropDownList><asp:TextBox ID="txtSearchFilter" runat="server"></asp:TextBox><asp:LinkButton ID="btnSearchFilter" runat="server">Filter</asp:LinkButton> and C# code as follows: protected void Page_Load(object sender, EventArgs e){ if (IsPostBack) { if (ddFilterType.SelectedValue != "ALL") { dsItems.FilterExpression = (ddFilterType.SelectedValue = txtSearchFilter.Text); gvItems.DataBind(); } else { gvItems.DataBind(); } }} But when I run the search, I get:Exception Details: System.Data.EvaluateException: Cannot find column [the value in txtSearchFilter.Text]. How should I do my C# syntax to get this to work? Also, I would like to add further logic to check if the field is not the SKU field and have my expression do a LIKE expression instead of checking for equality. Any help with this is much appreciated. Marc
I have a filter expression and I have 2 data types one is an int and the other is a date format. I want to setup 2 textboxes to search for either the int or the date. I have the FilterExpression setup as follows: FilterExpression="PROJECT_CODE = '{0}' or PROJECT_DATE L= '{0}'"<FilterParameters> <asp:ControlParameter ControlID="TextBoxProjectCode" Name="PROJECT_CODE" PropertyName="Text" Type="Int16" /> <asp:ControlParameter ControlID="TextBoxProjectDate" Name="PROJECT_DATE" PropertyName="Text" /></FilterParameters> I can take out one or the other and run the search and return results, but can not do it with both. Is there a way to do this and what do I need to do to make it work?
I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview. Something like this example... http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx Here is some of my code..Private Sub ApplyFilter() Dim _filterExpression As String = "" If (Not DropDownList1.SelectedIndex = 0) And (DropDownList2.SelectedIndex = 0) And (DropDownList3.SelectedIndex = 0) Then _filterExpression = "CategoryName = '{0}'" End If Me.SqlDataSource1.FilterExpression = _filterExpression End Sub Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged ApplyFilter() End Sub But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.
Hi. Is there a way to convert a smalldatetime value to a year value (1/1/2001 -> 2001) in this case? I tried year(thisIsSmalldatetimeField) and datepart(yyyy, thisIsSmalldatetimeField) Dim FilterExpression As String = String.Concat("thisIsSmalldatetimeField=" & DropDownList.SelectedValue & "") mySqlDataSource.FilterParameters.Clear() mySqlDataSource.FilterExpression = FilterExpression
Hello, I have a Repeater control getting data from a SqlDataSource.The SqlDataSource uses a stored procedure which accepts a input parameter with the name "sort" (@sort). How do I use the FilterExpression with FilterParameter for defining the input value for the stored procedure ?The input value comes from an asp:QueryStringParameter.Or can the FilterExpression only be used with SQL Statements defined directly (as for defining the where clause) ? I guess the best way is to use the FilterExpression soo the data is cached in the Dataset which is needed/required when using FilterExpression.Alternativly I could use the SelectParameter instead FilterParameters, but I guess the data wont be cached then.Anyone who can help me and guide me in the right direction ? Best regardsMartin
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
What is the C# code I use to do this? I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
i am using visual web developer 2005 with SQL Express 2005 with VB as the code behindi have one database and three tables in itfor manipulating each table i am using separate SqlDataSource() is it sufficient to use one SqlDataSource() for manipulating all the three tables ? i am manipulating all the tables in the same page only please help me
I have a table which records employees'time-off records. There are 6 columns in this TimeOff table. They are RequestID, EmpName, StartDate, EndDate, Type, NumofDays. I have another table which has all the dates from 01/01/1950 to 01/01/2056.
I am trying write a query which lists all the dates between the timeoff startdate and enddate, including the the start and end dates, but my query so far only lists the start and end date in a timeoff record:
SELECT D.[Date], Datename(dw,D.[Date]) AS Weekday FROM Dates D LEFT JOIN TimeOff T ON D.[Date] = T.OffStartDate OR D.[Date] = T.OffEndDate WHERE (OffType = 'Sick Day' AND EmpName = 'Cat White') AND (D.[Date] BETWEEN T.StartDate AND T.EndDate)
Has anyone ever written a function to retrieve all individual dates between two given dates? Using DATEDIFF I can get the number of days between two dates. However I need to iterate through the days to identify weekend and holiday dates. Has anyone ever written a function to do this?
So, if select datediff(d,'07/01/2007','07/15/2007') as NumOfDays returns 14, I'd need to iterate through the 14 days and get the weekends and holidays. Would I have to use a cursor to iterate through the days?
I'm trying to generate this query, that displays Budget Current Year , Actual Current Year and Prior Year Revenue. When It comes to the Budget and Actual everything works fine, however when I try to add the query for the Prior Year I get an error, and I realized that the leap date is causing the error
Here is what I'm trying to generate
InnCodeID Quarterly Monthly Days Period Year BARmRev AARmRev PYRmRev
ADDIS Q1 Jan 1 1 2008 NULL NULL
ADDIS Q1 Jan 1 1 2008 3462.14 5107.65
ADDIS Q1 Jan 1 1 2008 NULL NULL
ADDIS Q1 Jan 1 1 2008 NULL NULL
Here is the error that I'm getting:
Code Snippet
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
(4834 row(s) affected)
Here is my Transact-SQL Syntax (summarized because I Couldn't post it):
SELECT
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01'
AND Year(dbo.Trans.TR_Date) = Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1'
THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT Trans1.TR_Amount
FROM dbo.Trans Trans1
WHERE Trans1.TR_Dept = '10' AND TR_Main = '5120' AND TR_Sub = '01' AND trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date)
AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Year(dbo.CurrentDate.CurrDate) AS varchar(4))) AS datetime)
AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND trans1.datatype = dbo.Trans.DataType) ELSE NULL END) * - 1 AS BARmRev,
--AA Script Here AS AARmRev,
(CASE WHEN (CASE WHEN dbo.Trans.Tr_Dept = '10' AND dbo.Trans.TR_Main = '5120' AND dbo.Trans.tR_sub = '01' AND Year(dbo.Trans.TR_Date)
= Year(dbo.CurrentDate.CurrDate) AND dbo.trans.Datatype = '1' THEN dbo.trans.Tr_Amount ELSE NULL END) IS NOT NULL THEN
(SELECT SUM(Trans1.TR_Amount)
FROM dbo.Trans Trans1
WHERE RIGHT(RTRIM(Trans1.TR_Dept), 2) = '10' AND Trans1.TR_Main = '5120' AND Trans1.TR_Sub NOT BETWEEN '04' AND '05' AND
trans1.TR_Date = CAST((CAST(Month(dbo.Trans.TR_Date) AS varchar(2)) + '/' + CAST(Day(dbo.Trans.TR_Date) AS varchar(2))
+ '/' + CAST(Year(dbo.CurrentDate.CurrDate)-1 AS varchar(4))) AS datetime) AND Trans1.TR_Entity = dbo.Trans.TR_Entity AND
Hello All, I am trying to Add certain number of days to a particular date. and my requirement is that it need to exclude all saturdays and sundays and then give me the resultant date in Sqlserver. Please can anyone help me in achieving it. thanks Shiva Kumar
Hi can anyone help me , or am I on the wrong track.
Is there any easy way to create a stored procedure that inserts into a table the relevant months dates into a table based on the month and year as parameters. I.e say the parameters passed are 01/2001 hence based on this all of the month of January 2001 dates are inserted into a table in this format : 'Jan 01 2001 12:00:00'
