I have data that is timestamped with a DATETIME column and I want to filter it down to the set of records where the DATETIME is between 9:30am and 5:30pm for any day. What's the best way to do this? UPDATE: Changed because I need precision to the minute, not just hour. Sorry about that. ...
I have a three tables Results: TestID TestCode Value Tests: TestID TestType SysCodeID SystemCodes SysCodeID ParentSysCodeID Description The question I have is for when the user is entering data into the results table. The formatting code when the row gets the focus changes the value field to a dropdown combobox if the testCode is of...
Suppose I had a set of alpha-character identifiers of a set length, e.g. always five letters, and they are assigned in such a way that they are always incremented sequentially (GGGGZ --> GGGHA, etc.). Now, if I get to ZZZZZ, since the length is fixed, I must "roll over" to AAAAA. I might have a contiguous block from ZZZAA through AAAA...
I asked this question previously but the answers weren't what I was looking for. I created a table in Asp.net without using code. It contains two columns. YourUserId and FriendUserId This is a many to many relationship. Heres what I want: There can be multiple records with your name as the UserId, there can also be multiple record...
Say you have a table layout like the following: couses: id (INT), courseName (VARCHAR) instructors: id (INT), courseId(INT), instructor(VARCHAR) Create a query which will print the all the courses and if one instructor is present display their name, if two instructors are present print both of their names on the row in sorted ord...
I am building an ASP.Net C# web application that will be using lots of sound files and image files. Considering performance, would it be best to store all files in SQL as image data type and retrieve from the database or store/archive the hard file on the server and store the path in sql? Im curious about the pros and cons - other than t...
I'm working with an old SQL 2000 database and I don't have a whole lot of SQL experience under my belt. When a new row is added to one of my tables I need to assign a default time value based off of a column for work category. For example, work category A would assign a time value of 1 hour, category B would be 2 hours, etc... It shoul...
We have a database with a generous use of the CLOB/TEXT datatype all over the schema. After running lots of queries yesterday on multiple tables having a CLOB type, I came to the conclusion that as soon as there is a CLOB in the SELECT clause (even if the value if NULL/empty), the query takes a huge performance hit. We have a client-ser...
I just moved my site to a new web host, and after changing the database-login file, it still can't connect. I get the following error: Warning: mysql_query() [function.mysql-query]: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) in /usr/www/users/simpleof/index.php on line 91 I changed the password and tried a...
I have two tables: Application applicationid (int) applicationname (varchar) isavailable (bit) and Holidays applicationid (int) holidaydate (datetime) I need to get the isavailable flag for any given applicationname but it should only return if the day if is not a holiday. The isavailable flag is independent of ...
I have a header table that has a list of web pages in it. Each record has a page URL field which I am looking to parse out into a hierarchy (TreeView). I am not sure of the best way to go about it and I am having a problem getting it to work. Here's an example of URLS: /file1.aspx /file2.aspx /aFolder/file.aspx /aFolder/file2.aspx /b...
Assuming I have a table foo where I have something like this: id, user_id, timestamp, some_value What I want to do is remove all rows that aren't the newest N per user. The deletion itself could be handled by a: DELETE FROM foo WHERE id NOT IN (...) so you could rephrase the problem into: How do I get the newest N(there might be le...
I am using a data grid view to display information from a database and I can't get it to update correctly. I can see the data from the database but when I change information and save it it changes the entire column's information. My rows need to have different values in the same column so this is a problem. -Here is my code so far, what...
I am having a problem giving a LINQ predicate the knowledge it needs to sort either alphabetically or numerically. I am working with a multi-value pivoted set of data with has a sortable column. The column is always stored as varchar in the DB, however, it has lookup knowledge of what that data type actually is. To simplify, in additio...
I am wondering what a .sql file looks like. How do I create one from mysqldump? ...
I want to perform this date check: The date entered by the user should not be less than 12 months ago from the current date, e.g. if date_value > '03-apr-2009' How do I write .NET code to do this? ...
I have a database by name database1 and it contain a table by name of employee In employee table have many fields like employee, name, salary etc etc. by mistake i am change salary value 0 for all employee. I am restore database from backup by name database2 Now i need assistance to update my current database which is database1 I wan...
What is the complete list of all special characters for a SQL (I'm interested in SQL Server but other's would be good too) LIKE clause? E.g. SELECT Name FROM Person WHERE Name LIKE '%Jon%' SQL Server: % _ [specifier] E.g. [a-z] [^specifier] ESCAPE clause E.g. %30!%%' ESCAPE '!' will evaluate 30% as true ' characters need to be esca...
Why does SQL Server insist that the temp table already exists! one or the other will happen!! , so it will never be the case. declare @checkvar varchar(10) declare @tbl TABLE( colx varchar(10) ) set @checkvar ='a' INSERT INTO @tbl (colx) VALUES('a') INSERT INTO @tbl (colx) VALUES('b') INSERT INTO @tbl (colx) VALUES('c') INSERT IN...
Hi to all. What are the best (cleaner, less resource expensive) ways to pass one-to-many relationships from a db server to a client? Imagine that I have a Author table and a Book table. I want to to retrieve all the authors whose name starts with "a" and all the books they've written. Then, client-side, generate an array of objects "Aut...