SQL date comparison
How do I check if the timestamp date of a record is before midnight today? datediff is driving me nuts... ...
sql
C# Connecting to a SQL server with a named instance??
Hi A client of mine has told me the program I made for them won't connect to a SQL server named instance, I have a standard SQL server with no named instance so I'm wondering how I can test this. A named instance connection string look like the one below, could the backslash be were my code fails? Driver={SQL Native Client};Server=myS...
Is this a good way to model address information in a relational database?
I'm wondering if this is a good design. I have a number of tables that require address information (e.g. street, post code/zip, country, fax, email). Sometimes the same address will be repeated multiple times. For example, an address may be stored against a supplier, and then on each purchase order sent to them. The supplier may then...
Can you have a WITH statement in a tabular user defined function
I have the following code for a UDF but it errors with the message: Msg 156, Level 15, State 1, Procedure CalendarTable, Line 39 Incorrect syntax near the keyword 'OPTION'. is it because of my WITH statement as I can run the same code fine in a stored procedure? SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- =============...
What do I gain by adding a timestamp column called recordversion to a table in ms-sql?
What do I gain by adding a timestamp column called recordversion to a table in ms-sql? ...
How can I tell when a MySQL table was last updated?
In the footer of my page, I would like to add something like "last updated the xx/xx/200x"; with this date being the last time a certain mySQL table has been updated. What is the best way to do that ? Is there a function to retrieve the date ? Should I make an access to the database everytime my footer is displayed ? ...
How do you setup a linked server to an Oracle database on SQL 2000/2005?
I am able to create and execute a DTS package that copies tables from a remote Oracle database to a local SQL server, but want to setup the connection to the Oracle database as a linked server. The DTS package currently uses the Microsoft OLE DB Provider for Oracle with the following properties: Data Source: SERVER=(DESCRIPTION=(ADDRE...
News feed database design as in Facebook
How would make a news feed "friendly" database design, so that it wouldn't be extremely expensive to get all of the items (query) to put in the news feed? The only way I can think of would involve UNIONing nearly every table (representing groups, notes, friends, etc) and getting the dates and such, that just seems like it'd be a really ...
Regex for parsing SQL parameters
If I have a query such as SELECT * from authors where name = @name_param, is there a regex to parse out the parameter names (specifically the "name_param")? Thanks ...
What SQL query shows me the tables and indexes used by a view on Informix?
What SQL query shows me the tables & indexes used by a view on Informix? I know how to find the "original create statement" for a view in SYS_VIEWS, but that requires a human brain scanning/grokking that select. I believe I can find if the tables are indexed, once they have been identified. Background: I need to make certain that some...
How to Query A DataGridView Using Linq
I have a DataGridView that I want to query using Linq (C# WinForm). I want to "count" rows where a certain criteria is met. For example, variable1 = "count rows where ColumnBoxAge > 3 || < 5" label1.Text = variable1 How to do this in C# WinForm using Linq? ...
SQL grouping
I have a table with the following columns: A B C --------- 1 10 X 1 11 X 2 15 X 3 20 Y 4 15 Y 4 20 Y I want to group the data based on the B and C columns and count the distinct values of the A column. But if there are two ore more rows where the value on the A column is the same I want to get the maximum valu...
Are inline queries a bad idea?
I have a table containing the runtimes for generators on different sites, and I want to select the most recent entry for each site. Each generator is run once or twice a week. I have a query that will do this, but I wonder if it's the best option. I can't help thinking that using WHERE x IN (SELECT ...) is lazy and not the best way to...
Select X Most Recent Non-Consecutive Days Worth of Data
Anyone got any insight as to select x number of non-consecutive days worth of data? Dates are standard sql datetime. So for example I'd like to select 5 most recent days worth of data, but there could be many days gap between records, so just selecting records from 5 days ago and more recent will not do. ...
How to tranform an Oracle SQL into a Stored Procedure that should iterate through some tables fetching a certain data field?
I need to transform an Oracle SQL statement into a Stored Procedure therefore users with less privileges can access certain data field: SELECT info_field, data_field FROM table_one WHERE some_id = '<id>' -- I need this <id> to be the procedure's parameter UNION ALL SELECT info_field, data_field FROM table_two WHERE ...
How do I convert an int to a zero padded string in T-SQL?
Let's say I have an int with the value of 1. How can I convert that int to a zero padded string, such as '00000001'? ...
SQL Server 2000, "FOR XML AUTO" Query via http, Need "Content Length = 12345" in return XML Header
SQL Server 2000 Guru's, I've setup SQL 2000 to accept HTTP Queries i.e. http://74.22.98.66/MYDATABASE?sql={CALL+sp_XMLDATA}+&root=root (ficticious url) It works great and returns the following XML via I.E.7 url - <?xml version="1.0" encoding="utf-8" ?> <root> <g c="15" /> <g c="8" /> <g c="19" /> </root> However I a...
User-Defined Functions SQL Server 2005 flagged incorrectly as non-deterministic?
Related to this question, I decided to check the UDFs in my data warehouse (which should largely have been deterministic), and I found several which aren't which should be. For instance: CREATE FUNCTION [udf_YearFromDataDtID] ( @DATA_DT_ID int ) RETURNS int AS BEGIN RETURN @DATA_DT_ID / 10000 END Shows up in this query: SELE...
SQL Server: Floor a date in SQL server, but stay deterministic
(This is related to Floor a date in SQL server.) Does a deterministic expression exist to floor a DATETIME? When I use this as a computed column formula: DATEADD(dd, DATEDIFF(dd, 0, [datetime_column]), 0) the I get an error when I place an index on that column: Cannot create index because the key column 'EffectiveDate' is non-det...
How do I force Postgres to use a particular index?
How do I force Postgres to use an index when it would otherwise insist on doing a sequential scan? ...