I use DATEDIFF function to filter records added this week only:
DATEDIFF(week, DateCreated, GETDATE()) = 0
and I noticed what it's assumed what week starts on Sunday. But in my case I would prefer to set start of week on Monday. Is it possible somehow in T-SQL?
Thanks!
Update:
Below is an example showing what DATEDIFF doesn't che...
i have been working on query which uses compute by clause for avg and sum operators .
As Microsoft declared this will be a discontinued feature ,what will be the replacement for this feature
...
Hi i was wondering if anyone knows how i can calculate the difference between two tables in tsql. I dont mean finding which cells are different - i mean calulcating the numerical difference. eg - Table A has column1, column 2 and only one 1 row. A1 = 40, B1 = 30. Table B has column1, column 2 and only one 1 row. A1 = 25, B1 = 10. So how ...
I have a table users which has a primary key userid and a datetime column pay_date.
I've also got a table user_actions which references users via the column userid, and a datetime column action_date.
I want to join the two tables together, fetching only the earliest action from the user_actions table which has an action_date later than...
I'm dealing with a legacy system where I need to identify some bad records based on a column with a data type of Float.
Good records have a value of...
1
2
1.01
2.01
Bad records are anything such as..
1.009999999999999
2.003423785643000
3.009999990463260
I've tried a number of select statements where I Convert to Decimal and cast ...
I have setup multiple SQL Service Broker Queues in a database but have not seen this problem before. A message containing XML is being converted to what appears to be mostly Chinese Characters. When I check the variable that is storing the XML prior to putting it in the message queue I can see that is in English and is well XML formed. W...
Hi,
I have a section of code that runs on a SQL 2005 server as:
SELECT e.[ElementName] ,
ISNULL(ElementValue, ElementDefaultValue) AS Value
FROM [cx_Element] e WITH ( NOLOCK )
LEFT JOIN [cx_ReportStyleElements] rse WITH ( NOLOCK ) ON rse.[ElementId] = e. [ElementId]
...
I am new to Microsoft SQL Server and have been frustrated by a GROUP BY query that won't do what I want it to. The table is the following:
make model distancefrom distanceto driverid
toyota yaris 358.2 368.2 401
toyota yaris 368.2 378.7 103
toyota yaris 378.7 382.2 103
toyota yaris 382.2 392.2 103
to...
I have found out how to determine what columns are are primary key column of a given table by using this query:
SELECT CONSTRAINT_NAME, COLUMN_NAME
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME='tablename_here' AND
CONSTRAINT_NAME LIKE 'PK_%'
I can find out what the identity seed and increment is by using this ...
How do I create an index on a table that exist in a remote SQL Server database using the openquery syntax?
...
I have a view which was working fine when I was joining my main table:
LEFT OUTER JOIN OFFICE ON CLIENT.CASE_OFFICE = OFFICE.TABLE_CODE.
However I needed to add the following join:
LEFT OUTER JOIN OFFICE_MIS ON CLIENT.REFERRAL_OFFICE = OFFICE_MIS.TABLE_CODE
Although I added DISTINCT, I still get a "duplicate" row. I say "duplicat...
I have a table called Stock and another called Listed, within the Stock Table is a Status Code that indicates when something is at the front of the queue of items of stock - I want to be able to find the most recently added item and set this to be the "front of queue" status.
For example to get all the items listed and then order them by...
I have a bitmasked int field in my database.
Usually I manage it through c# code, but now I need to flip a bit in the mask using T-SQL
How do I accomplish the following:
The bit i want to flip: 1 << 8 (256)
The mask value before i flip: 143
The mask value after i flip: 399
I'm not exactly a bit-flipping wizard but this c...
I believe the answer is no. And am looking for a counter example to show that order of output is not guaranteed, absent an order by clause.
consider:
create table #order (orderId int primary key clustered
, customerId int not null -- references customer(customerId)
, orderDateTIme datetime not null)
insert into #order values ...
I need to understand and clarify some T-SQL stored procedures that contain important business logic. Does anyone know of any tools, tips, tricks, or hints that can be used for unit testing so I can refactor this sql without breaking it?
...
I have an Orders table that stores the Ordernumber as NVarChar. We manually increment the order number by querying for the biggest order number ordering in descending order and returning the top 1 and then adding 1. We have this implemented in Microsoft CRM 4.0.
e.g Order Numbers (NVarchar)
99
456
32
When I query the above values i...
Will Try-Catch capture all errors that @@ERROR can? In the following code fragment, is it worthwhile to check for @@ERROR? Will RETURN 1111 ever occur?
SET XACT_ABORT ON
BEGIN TRANSACTION
BEGIN TRY
--do sql command here <<<<<<<<<<<
SELECT @Error=@@ERROR
IF @Error!=0
BEGIN
IF XACT_STATE()!=0
BEGIN
...
I am creating a script that for "merging" and deleting duplicate rows from a table. The table contains address information, and uses an integer field for storing information about the email as bit flags (column name lngValue). For example, lngValue & 1 == 1 means its the primary address.
There are instances of the same email being e...
Normaly i would do a delete * from XXX but on this table thats very slow, it normaly has about 500k to 1m rows in it ( one is a varbinary(MAX) if that mathers ).
Basicly im wondering if there is a quick way to emty the table of all content, its actualy quicker to drop and recreate it then to delete the content via the delete sql statem...
When you perform a left join in TSQL (MSSQL SERVER) is there any guarantee which row will return with your query if there are multiple rows on the right?
I'm trying to use this to exploit an ordering on the right table.
so
Select ColA, ColB, ColC
from T
Left Outer Join
(Select ColA, ColB, ColC
from T--CLARIFIED, this is a sel...