Viewing 15 posts - 106 through 120 (of 7,618 total)
If you really want to remove the date from the column, then change the column type to "time".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 30, 2024 at 4:13 pm
Dates, datetimes and times are stored in a special binary format in SQL Server. You don't change the column format, you just change the display format.
SELECT CONVERT(varchar(8), datetime_column, 8)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 30, 2024 at 4:11 pm
Yes, Oracle is extremely difficult to learn. Their syntax is overall an odd mix of SQL style and earlier language styles.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 28, 2024 at 2:26 pm
No directly usable sample data, so I can't test it, but maybe this:
DECLARE @first_week_ending_date date;
SET @first_week_ending_date = '20240707';
SELECT
ClientID,
DATEADD(DAY,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 26, 2024 at 5:04 pm
Hmm, are you using SSMS / gui to see the tables? Maybe there is a filter set for schema=dbo?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 25, 2024 at 5:57 pm
Good point. I was in too much of a hurry when I wrote the other code:
DECLARE @x varchar(500) = 'xxx. #1234 has been replaced by #014521...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 23, 2024 at 10:11 pm
An alternative (maybe slightly less overhead?, esp. for long strings):
SELECT LEFT(item, CHARINDEX(' ', item + ' ')) AS value
FROM dbo.DelimitedSplit8K (@x, '#') /*or STRING_SPLIT(), if available to...SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 23, 2024 at 6:03 pm
I suggest removing ALL non-numeric values in the query itself rather than trying to go thru the data. For example, by making this mod to the query:
...
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 21, 2024 at 7:42 pm
No DDL provided, but my best guess is that one of these columns:
t1.invoicenum; t2.invoicenum
is integer and the other is not, and that one contains a value of 'VZ34-031'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 18, 2024 at 6:19 pm
Since they are "or" conditions, SQL should be able to stop evaluating at the "first" one that is true. In theory, though, SQL could re-arrange the checks and make a...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 18, 2024 at 1:33 pm
I think this will match what you need, if I understand correctly:
SELECT ...
FROM dbo.baseTable bt
LEFT OUTER JOIN dbo.sentTable st ON st.sent_rowid = bt.rowid
WHERE ((@vendor IS NULL AND st.sent_rowid IS NULL)...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 16, 2024 at 5:35 pm
If you only want to replace leading and/or trailing commas, then this:
UPDATE tn
SET oppo_SCRMcompetitor = CASE WHEN LEFT(oppo_SCRMcompetitor_ca1, 1) = ','
THEN STUFF(oppo_SCRMcompetitor_ca1,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 15, 2024 at 6:50 pm
If you always want to replace every comma, you can do this:
UPDATE dbo.table_name
SET oppo_SCRMcompetitor = REPLACE(oppo_SCRMcompetitor, ',', '')
WHERE oppo_SCRMcompetitor LIKE '%,%'
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 15, 2024 at 6:46 pm
That is SQL's method for handling insufficient space on a CAST.
The only safe method would be to use 11 chars. 10 max digits plus the prefix char (assuming you don't...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 15, 2024 at 4:20 pm
Definitely focus on the clustered index, first and foremost!
The best way to choose the keys is to look at the missing index stats and current index usage stats and decide...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 15, 2024 at 1:57 pm
Viewing 15 posts - 106 through 120 (of 7,618 total)