I'm using the .NET Framework 1.1 together with SQL Server 2005 Beta.
I have a search page where you can search a db using a number of fields or combination thereof. Three are text fields (patient id, surname, forename) and I can do any search using one criteria or any combination without any problem.
However, I'd also like to search on a dob (datetime field in SQL Server) and I'm having real problems in making this work. The dates are stored in the following way in SQL Server: '1938-05-31 00:00:00.000', whilst they are displayed as 'dd/mm/yyyy' on a web page, which I guess corresponds to my regional settings.
My code is shown below. I 've tried any number of combinations for the dob string with no success. I do not get any error, just no records are returned.
Does anyone have any idea what I'm doing wrong and how to correct it before this drives me crazy?!
I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)
SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'
I would like to be able to search by a single date, @StartDate, or by a date range , between @StartDate and @EndDate. I am having a hard time with the logic on this for a report in SSRS.
I have a very big database with me. This database has large number of tables and each table has n number of fields.
My trouble now is that I desire to search for a field name called 'empname'. Is there any query possible for me to search all tables and return me the table names which contain fields which match with 'empname'. Adding essence to the porridge I would like to know if I specify something like '%pnam%' it should match any field name which contains 'pnam' as a portion of its name. Is all these possible ??
Im building up a query in my code behind. When I execute this query it returns a list of users who match the criteria.BUT, I want to add something extra. In my DB in the tblUserData there's a field "interests" datatype nvarchar(30) which contains the numbers (comma-delimited) of the interestsID in my tblInterests. For example, my interestsfield may contain something like: 1,4,8Now if someone wants to find someone who has number 4 and 8 as interests, how can I search in this textfield?!?I wrote this in a SP, but I'd really like it to be possible from code-behind and build the query myself...declare @s varchar(20)set @s='4,8'EXEC('SELECT * FROM tblUserData WHERE Interests in ('+@s+')')
I have a varchar column which containd comma delimited values like Rec# Fruits 1 Apple, Peach, Strawberry 2 Orange, Mango 3 Banana, Grape ...........
Now i have to add search facility so that a user could search for more than 1 fruit at a time. I have a Stored Procedure which returns records from this table. that SP has a Parameter @SearchFruit Varchar(500) and the user could pass in values like 'Apple, Mango' to this parameter.
Now how should i write the SQL so that i get back the records Rec# 1 & 2 since apple is there in 1st record and mango is there in the 2nd ??
I know if a put the comma delimited values as individual records in a temporary table and also do the same for the parameter values then i can get the desired results. But i want to avoid doing that. Any other way ?
Hi, I am building a website in ASP.net C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is; //Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); "; SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"]))); //If UserName is null, display confirmation, else display errorif (UserName == null) ; {UserNameCheckLabel.Text = "Username okay"; String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"]))); }else; {UserNameCheckLabel.Text = "That username is in use"; } I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice! Many thanks, Chima
I have the time of an event stored on each record as a datetime field.Itincludes the year,month,day, etc. Suppose my user wants to search the tablefor all events over the lunch hour, say between11am and 1pm. How do Iconstruct the SELECT query to peek into each datetime field and return onlythose records that satify the specified time range?Many thanks.
I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.
We have a application running on Sql server 2005, which require to browse/search text field. Does anyone know if Sql server's search/browse performance on text field is better than oracle?
The table the application will search on is a customer table that has a 10000 records in it, does this size of table casue a performance problem for sql server 2005 if I index the text field?
I am doing a search on a column of type varchar, the columns all contain dates in the future. When i perform a query
SELECT [CIFPan]
,[CIFMemNum]
,[CIFLName]
,[CIFFName]
,[CIFExpDate]
FROM [FutureSoft].[dbo].[zCIFRecord]
WHERE CAST([CIFExpDate] AS smalldatetime) between '12/01/2000' and '01/30/2015'
I get error:
Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.
I can use this same query on an exactly same other field that contains dates in the past. Is there some kind of limitation on the dates as far as them being in the future or past?
Ok, so I have a Select statement that looks like this:
Select * from FilteredOpportunity where (OpportunityID LIKE @opportunityid) and (Name LIKE @name) and (CustomerIDName LIKE @customeridname) and isNull(EstimatedCloseDate, '1900-01-01') LIKE @estimatedclosedate and isNull(Description, '') LIKE @description and isNull(AccountIdName, '') LIKE @AccountIDName and isNull(ACCU_OppTypeName, '') LIKE @ACCU_OppTypeName and isNull(SalesStageCodeName, '') LIKE @SalesStageCodeName and isNull(ACCU_CustomerTypeName, '') LIKE @ACCU_CustomerTypeName and (CreatedByName LIKE @CreatedByName) and (CreatedOn LIKE @CreatedOn) and isNull(ACCU_OppSourceName, '') LIKE @ACCU_OppSourceName and (StateCodeName LIKE @StateCodeName) and (StatusCodeName LIKE @StatusCodeName)
This chunk of code is used in an XML file which, once this XML is imported (using sharepoint by the way if you haven't noticed), I can then search through tables with different filters. Well, my date filter isn't working the same way as my other ones. I have a couple different date filters, but none of them work.
Before I ask my question, I only pass in one variable at a time through my code.... the rest of the variables get a default value passed to them if the user doesn't pass it. That value is simply '%'. Anyways, so the different ways a user can type in a date is by typing in the whole date, part of the date starting with the beginning of it, typing in the end of the date, or typing something within the date. For a string it's like 'Example', 'Exam%', '%ple', and '%amp% respectively. How do I accomplish this with a date?
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x ----------------------------------------------------------- Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.
[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or
[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>
In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.
How do I achieve both # 1 & 2 above in a code? Does this explain better.
hi, i have problem while searching date in datebase using like operator ,i write the below query but this will not give any result and i have the date 13/1/2008 in my table meeting please any one help me.
select meetingdate from meeting where meetingdate like '%13/1/2008%'
I have a SQL DB with a column called time_occurred that is formatted like ( 7/28/2004 7:10:30 AM ).
What I need to do is run a report based on just the month day and year portion. I am using the calendar control so mins, sec and milliseconds are not available nor do I really need them.
I am running the Query with this Stored Procedure
ALTER PROCEDURE dbo.Prodecure1 ( @WhereClause varchar(8000) )
AS
-- Create a variable @SQLStatement DECLARE @SQLStatement varchar(8000)
-- Enter the dynamic SQL statement into the variable @SQLStatement SELECT @SQLStatement = 'SELECT column1, column2, etc...' + @WhereClause
> This is what I want to do with the data table below.[color=blue]>> I only want it to show one id for each issue. ( ie, 4001 only once,> 4002 only once, etc. But only the one with the latest date and time)>> Ie. 4001 should only be in the table once, with the latest date -> which should be :> 400109/12/200317:12:09 (as I only want to show the last time> the id was updated - do you get me now )>> Ie. 4002 should show only 400211/12/200315:25:13>[/color][color=blue]>>> idhs_change_datehs_change_time> 400127/10/200310:38:27> 400109/12/200314:43:58> 400109/12/200317:12:09> 400227/10/200310:56:28> 400209/12/200314:44:11> 400211/12/200315:25:13> 400327/10/200311:13:12> 400309/12/200314:44:21> 400310/12/200310:48:02> 400310/12/200313:25:09> 400427/10/200311:28:09> 400409/12/200314:44:29> 400409/12/200317:18:28> 400527/10/200315:55:40> 400528/10/200310:18:24> 400627/10/200315:59:47> 400628/10/200310:18:38> 400609/12/200314:44:40> 400609/12/200316:47:15>[/color]
In my asp.net web application I used two textboxes for date1 and date2 to search records I have used two variable in my stored procedure @OrderDate1 datetime,@OrderDate2 datetime,for todays record (values for date1 and Date2)set @OrderDate1='09/20/2007 12:00 AM';set @OrderDate2='09/20/2007 11:59 PM';for records between (values for date1 and Date2)set @OrderDate1='09/20/2007 12:00 AM';set @OrderDate2='09/24/2007 11:59 PM';I have to search the todays records OR records between these two days (OrderDate >= @OrderDate1 ) AND (OrderDate <= @OrderDate2) It works fine in my local server>>It finds the todays records by ginving same date (@OrderDate1 and @OrderDate2) and records between two dates but it does not work for sql server which is outside india ...if it is problem of date format how i can solve that.. Thanks
I've a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]
@ClientCode VARCHAR(7) = '',@UserID INT = 0 ,@OrderID INT =0 ,@StartDate datetime,@EndDate datetime
IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name FROM dbo.customer c WHERE c.Customer = @ClientCode) ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1) FROM dbo.master m WHERE phase=1 AND m.Customer = @ClientCode AND M.Status <> 'PIE') ,@AverageAge = ISNULL((select avg(age) from (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age from dbo. master M WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed.
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
Brand new to this, so please bear with me.I'm using the following code fragment to update a datetime field on a SQL Server 2005 database table:cmd.CommandText = "Update Projects Set EntryDate = " & Convert.ToDateTime(txtEntryDate.Text)cmd.ExecuteNonQuery()The result of the update operation is the the database field contains the value "1900-01-01 00:00:00:000". This probably means that I passed nulls to SQL; however, I see a valid date in the txtEntryDate field on my web form (i.e., "06/18/2007"). I also did a "Response.write" to display the txtEntryDate and it looks Okay.Can someone tell me what I'm doing wrong?Thanks!Using Visual Web Developer 2005 Express.
I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?
The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.
My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario
ID StartDate 1 2014-01-01 1 2014-02-01 1 2014-05-01-------After Gap Restarted 1 2014-06-01 1 2014-09-01---------After last gap restarted 1 2014-10-01 1 2014-11-01 2 2014-01-01 2 2014-02-01 2 2014-03-01 2 2014-04-01 2 2014-05-01 2 2014-06-01 2 2014-07-01
For Id 1 the start date after the latest gap is 2014-10-01 and for id=2 there is no gap so i need the minimum date 2014-01-01
My Expected Output id Startdate 1 2014-10-01 2 2014-01-01
