Hey all,
I want to set a filter in my statement. Normally it is no problem, but this time I want to set a filter only on a part of the field.
For example:
In my table with ordernumbers, I have orders with are starting with 'FO', VO' (FO0001, FOO0002, VO0001, VO0002, ect etc)
I want to set a filter in my statement that I only wish to see the ordernumbers starting with 'FO'.
Any suggestions?
thx for helping me out again....
Hi,
I got a problem which looks like the same with a futher topic of me (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1156355&SiteID=1), but this one is a little bit more difficult.
I want to set a filter on a field, but the it must set a filter on a part of the field. I think it will be more clear with an example:
I have a list with ItemNumbers and ItemDescriptions.
ItemNo ItemDescription
1 Pineapple yellow
2 Cauliflower size 6
3 Orange sweet yellow
etc. etc.
Now I want that the user of this report can set a filter just by entering a part of the description. So, if he enters 'flower' he only see the cauliflower. If he enters 'yellow' he only see the pineapple and the orange, etc. etc. If a blank value is left, all the item must be shown.
Can some one help me out by solving this problem??
Thx a lot
Hello,
new question about an old topic (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1232451&SiteID=1):
I want to set a filter on a field, but the it must set a filter on a part of the field. I think it will be more clear with an example:
I have a list with ItemNumbers and ItemDescriptions.
ItemNo ItemDescription
1 Pineapple yellow
2 Cauliflower size 6
3 Orange sweet yellow
etc. etc.
Now I want that the user of this report can set a filter just by entering a part of the description. So, if he enters 'flower' he only see the cauliflower. If he enters 'yellow' he only see the pineapple and the orange, etc. etc. If a blank value is left, all the items must be shown.
After this, Jarret, suggest to me to insert the next code into my query:
where ItemDescription like '%' + @ItemDescParam + '%'
This helped, but I want to have 2 options extra:
1) The part of the description which the user inserts must be independent of capitals
2) It also have to be possible that the user inserts more than one part. For example "pine" and "fellow"
Can some one help me out?
Thx again
Our phone system keeps a list of events, such as login and logout, which I'm doing a self join on to get the start and end times so I can calculate the time between the start and end of each event during a time period.
Then I stuffed the self joins into a couple views for the events I'm interested in...
The problem is that this makes the "end time" a calculated field, so when I use it as part of the filter it takes forever. I need to account for events ending within the time period, and events starting within the time period... so I need to filter on both the start and end time in the initial select. Filtering the start time first in a subselect, then the end time in the main select is quick but can lose things that started before the start time.
So how can I speed up the filtering on that calculated column?
Or am I going to have to get really creative with unions?
Hello,
I am looking at writing a SP without much success which enables multiple filtering on one field. Something like below:
Input field: Product Description
So if the user enters: "Large Drill" OR "Drill Large" the same resultset will be returned.
SELECT * FROM products WHERE products.prod_desc contains both "Large" AND "Drill"
I guess there'll need to be a nested Select and loop to parse the space separated input field.
Any pointers would be appreciated.
Thank you
Lee
I have a field that is stored as a smalldatetime but I want to filter on that field only for the date. How do I ignore the time stamp and only go by the date?
View 3 Replies View RelatedHi,I have a date/time field in a SQL2000 database, and what I would liketo do is to filter on a specific part of the field, for example thetime or hour.Supposing I have a set of data for the last 5 years and would like tofilter out any records which are outside working hours i.e. I wouldlike to show records where the time is between 9am and 5pm.Does anyone know if there is a simple way to do this in SQL? If yousimply don't specify the date part in the where clause (e.g. WHEREissuedatetime between '09:00:00' and '17:00:00') it defaults it to1900-01-01 so basically no data is returned.The only way I can see to do this is by using the DATEPART function,converting it to a varchar, appending 1900-01-01 on to it andconverting it to a datetime, and then using the where clause as statedabove. This is quite a long-winded way, however. Any other suggestions?Thanks,Matt
View 3 Replies View RelatedI am currently writing a report that will use as one of the filters the date (in this case it is the date of service of a physician office visit).
however the DB developers included the date and the time of the visit in one field so my resulting data set contains 4/13/2007 4:30pm for example, so using an @date parameter as a filter on the data field doesn't work because when you run the report and enter the date in the parameter it doesn't return anything.
I am an SQL Report writer amateur so I am sure there is an easy fix, any and all help will be appreciated.
Mark
hi friends..... i want to get part of the database field value in stored procedure.. ex : select reason from reasontable where rid=1 output : reason-------------------------------telephone is not working properly. but i want the output as : reason--------------------------------telephone is n.............
View 3 Replies View RelatedI have a tabkle that I want to use for NT authnetication. It was a list of domain lan usernames. I want to use NT authentication in ASP but in order to do so I need to add the domain to each of the lan ds. So my username, kssensalo needs to be DOMAINkssensalo. I need to do this for 5,000+ plus records. What SQL fucntion would I use? I know I can use LTRIM to remove, but I need to add or append.
Any suggesttions?
Trying to update part of a field. Currently using ColdFusion 4.0 and SQL Server 7.0.
My field looks something like this: ABC.DEF.GHI and I just want to update the last 3 characters, GHI. The length of the field may change so it's not going to be 11 characters long.
Any help would be appreciated.
How can I make my search button have the "Any part of field" match as a default? with a simple query...
I have a field in MS access with hundreds of words (cv)... I want to be able to find a word in "Any part of field"
my try:
WHERE ((([cv].[detail cv])=[detail]));
detail is nowhere to find... i am prompt to give a value. fine.but it
equals whole field; detail must be the sole value of the field detail cv...
help!
mchel
Hi,
I have a very beginer's question. I am trying to compare datetime field with time only. how can i just compare with time?
Select start_time,end_time from tblvouchers as sv,tblvoucherlines as svl
where sv.voucher_no = svl.voucher_no and
sv.equip_no = '01325' and svl.line_date ='03/14/2004'
and svl.start_time = '18:00' and svl.end_time = '00:00'
this query is not displaying any data.
Any help will be highly appreciated.
Hello all,What does it mean when a field is part of an index? Thank you.
View 1 Replies View RelatedI have an MS Access 2002 application that is distributed to a number of PCs around our office. The data for this application is stored on a central SQL Server that is linked in through ODBC.
This application has been in place for two years and working fine. We recently formatted and restored a PC, and now that particular PC has issues with the Access application.
Every time it tries to filter one of the linked SQL tables on a date field, Access goes unresponsive and GPFs out. If it's in a query that is behind a report, I get the old standard 'Catastrophic Failure'. If I open the table and right-click filter or run a query manually, Access GPFs.
I've tried recreating the ODBC, linking the tables through TCP/IP as well as Named Pipes. Nothing fixes it. All Windows and Office updates have been applied. This is not the first time we've reformatted a PC in the office, but we've never had this issue.
Has anyone run across this before?
Thanks!
-Ben
I have about 200 records with a field (fileName) that have an ampersand(&) somewhere in them. I need to update the fileNames and remove all occurances of "&" and replace it with "-"; without modifying any other part of the file name.
Is there a SQL command I can use to update only part of a field?
I'm trying to extract the time part of a smalldatetime field in a usprocedure to return just the time. I've used Datepart() to get the hours or minutes but you can't get both without getting messy. Anyone know a way to keep it neat & simple?
Thanks, archie
I would like to know the best way of returning just the Date part (dd/mm/yyyy) of a DateTime field using T-SQL. I know that some methods mean that you lose the indexing if the field is indexed. Which is the best way to do this in T-SQL whilst preserving the indexing? I'm using SQL Server 2005.
View 14 Replies View RelatedI have a gridview connected to a sqldatasource, and it works pretty good. It gives me the subsets of the information that I need. But, I really want to let them choose all the companies and/or any status. What's the best way to get all the values in the gridview...besides removing the filters :)
I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working. And, for the boolean, I have no idea to get the value without having a separate query.
(tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server">
2 <asp:ListItem Value="0">Open</asp:ListItem>
3 <asp:ListItem Value="1">Completed</asp:ListItem>
4 </asp:DropDownList>
5
6
7 <asp:DropDownList ID="drpdwnCompany" runat="server">
8 <asp:ListItem Value="">All</asp:ListItem>
9 <asp:ListItem Value="cur">Cur District</asp:ListItem>
10 <asp:ListItem Value="jho">Jho District</asp:ListItem>
11 <asp:ListItem Value="sea">Sea District</asp:ListItem>
12 <asp:ListItem Value="san">Net District</asp:ListItem>
13 <asp:ListItem Value="sr">Research District</asp:ListItem>
14 </asp:DropDownList>
15
16
17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>"
18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')">
19 <SelectParameters>
20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" />
21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" />
22 </SelectParameters>
23 </asp:SqlDataSource>
24
hi, I have a field named city_state that contains city and state together (Warren, OH) in the same field. I need to create two seperate columns one city, another state. how can I extract the state out of the city_state column and have two different column.
thanks
Ahmed
I have a table called 'wp_postmeta' (Wordpress) which contains a column called 'meta_value'. A typical field in this column looks like this:
Code:
a:22:{s:12:"productimage";s:104:"http://www.disobeyclothing.com/wp-content/themes/eCommerce3/images/tshirts/SurveillanceSociety-Black.png";s:13:"productimage1";s:104:"http://www.disobeyclothing.com/wp-content/themes/eCommerce3/images/tshirts/SurveillanceSociety-White.png";s:13:"productimage2";s:0:"";s:13:"productimage3";s:0:"";s:13:"productimage4";s:0:"";
s:13:"productimage5";s:0:"";s:13:"productimage6";s:0:"";s:5:"pr
[code]...
I've tried to run the following query but each time I run it, it wipes all data in the field:
Code:
UPDATE wp_postmeta set meta_value=replace(meta_value, 'URL...')
I've also tried this query, but it has the same effect:
Code:
UPDATE wp_postmeta
SET meta_value = replace(LTRIM(RTRIM(meta_value)), URL...
Dear all,
I have an application where I need to implement a Team Shift management.
The team shift table need to be defined as follow :
Shift table
=========
Id DateTime IsCurrent
1 08:00:00 True
2 16:00:00 False
3 00:00:00 False
From the table above the current running shift is Id = 1
Based on that I have following question:
1 - Is there a way to specified for a DateTime field that it will recieved only Time part ?
2 - When a shift change occurs, I need to compare the Current time with the time shift in the table. Then according to that I set the IsCurrent flag to proper new Shift ID.. how to perform this ?
Thnaks for your help
regards
serge
Hi all I was wondering whether it was possible to remove or delete part of a String in a Field? Lets take for example I have:
- A Table called: Table_1
- A Field called: MyField
- MyField contains the value: Hello I am on the msdn forums
Is it possible to perform an UPDATE Query which deletes part of that sentence? If this was hard coded it would be rather simple but if the phrase was changing which would lead to the sentence also changing is it possible to do this?
Lets say I wanted to remove the part which said msdn forums. Then UPDATE the field again which should leave out the bit msdn forums.
Appreciate the responses, Onam.
I have a very large CSV file containing name-and-address information which I am reading in a Visual Basic project using the Microsoft.JetOLEDB.4.0 provider.
The key field on which the CSV file is to be filtered is the PostCode field. This is a UK-format PostCode "XXnn Nxx" where "XX" is one or more letters denoting a geographical area within the UK and "nn" is one or more characters (starting with at least one numeric digit) which when combined with the area code denotes a specific district within the geographical area. My aim is to identify all the unique UK postal districts held within my address CSV file.
Because I do not know how to use SQL to filter on the partial contents of a database field I am presently reduced to extracting unique full PostCodes using "SELECT DISTINCT PostCode,City,County FROM [ADDRESSES.csv]" into a DataTable object, then sequentially reading that DataTable using the operation of a dictionary object to identify unique PostCode areas, to finally construct the DataTable I need.
Is it possible in SQL to select records where the value of a varying number of characters before a space character in a given (PostCode) field is unique?
Hello all,
I have a table that holds a large amount of text in a field that is the body of the email. For example, it might say something like:
Quote: Email tech support at thisemail@email.com if you have any questions about the results of this test.
I need to change the email address in this field. Using this example I need to change thisemail@email.com to thatemail@email.com; however I do not want to change the other text in that field.
It is also important to note that the rest of the body of the emails stored here is different depending on the email.
So basically what I need is a statement that would look at a particular field, search for an email address, and replace that email address with another one without disturbing the rest of the text in that field. I already checked the w3 update tutorial and the update there is for the entire field.
Thanks for the help in advance!
I have a table with a datetime field (named "lnDateDone") and an integer field (named "PlannedHour"). I want to build a new datetime field with the same value as lnDateDone and replacing the hour part by the PlannedHour field. Example:
lnDateDone = '2014-04-09 13:22:31.544'
HourPlanned = 14
new datetime field = '2014-04-09 14:00:00'
I tried the following:
CAST(SUBSTRING(CAST(lnDateDone AS VARCHAR(25)),1,12) + CAST(PlannedHour AS VARCHAR(2)) + SUBSTRING(CAST(lnDateDone AS VARCHAR(25)), 15, LEN(lnDateHeureFin)) AS SMALLDATETIME) AS DatePrevue
but it won't work when casted in DateTime format, only in varchar, but I want it in datetime as I need to do compute some values using date manipulation functions.
How can I do that?
from this, circdate being a datetime field:SQLQuery = "select distinct circdate from circdata order by circdate"I need the distinct date portion excluding the time part.this has come about when I discoveredI am inserting and updating some datetime values with the same value,but for some reason, the values are always off by a few seconds. I seta variable called SetNow assigned to NOW and then set the datetimefields to this SetNow variable. Then when I collect the distinct datetime I am assuming they will have the same values recorded incircdate, but no, they are off by several seconds. Makes no sense to meat all. I tried renaming the variable several times but it makes nodifference at all.any help appreciated, thanks.
View 5 Replies View RelatedHi, Im trying to run this query
UPDATE DataModif
SET t.Ind_des = Replace (t.Tit_Des,"'",'"')
FROM Tit_Modificables t
WHERE
t.Ind_num in
(SELECT CAST (t2.Ind_num AS VARCHAR(10))
FROM Tit_Modificables t2
WHERE t2.Emp_id ='1100004' AND t2.Ejercicio_fiscal = 2003 AND t2.Nom_tabla = CAST (10 AS NUMERIC))
but ir sends this message error
The multi-part identifier "t.Ind_num" could not be bound.
I don't know if there is a problem because I'm using an Update.. FROM
Any help?
Hi,
When I try execute one query in SQL 2k5, with alias in order by clausule, I retrieve the follow message:
Server: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "alias.fields" could not be bound.
Where alias is a any alias and, fields is a field of the table with alias.
Already exists one fix to patch this?
Thanks
Im trying to execute following update SQL:
UPDATE Property SET ImageList = U.ImageList
FROM Property M
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID
= U.FeedID
WHERE M.FeedID = ?
But following error:
[Execute SQL Task] Error: Executing the query " UPDATE Property SET
ImageList = U.ImageList FROM Property
M INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID
AND M.FeedID = U.FeedID WHERE M.FeedID = ?" failed with
the following error: "The multi-part identifier "M.FeedID" could
not be bound.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
ByPassPrepare is set to TRUE and ParameterName = 0 to variable User::Feed_ID
HOWEVER - following query executes fine:
UPDATE Property SET
ImageList = U.ImageList
FROM Property M
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID
= U.FeedID
WHERE M.FeedID = 11
Beats me - any help with explaining this to me
please?
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
convert(char(15), process_date,112) as process_dt
Hi Everyone
This is the query and I am getting follwoing error message
"The multi-part identifier "InvDate.Account Reference" could not be bound."
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC
By the way its SQL Server 2005 Environment.
Mitesh
Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?
View 2 Replies View Related