If You are using SQL Server 2008 select VersionNo from Versions order by cast(“https://stackoverflow.com/” + replace(VersionNo , ‘.’, “https://stackoverflow.com/”) + “https://stackoverflow.com/” as hierarchyid); What is hierarchyid Edit: Solutions for 2000, 2005, 2008: Solutions to T-SQL Sorting Challenge here. The challenge
There are a few different ways that you can create a linked server in SQL Server you can use the GUI in SQL Server Management Studio or via a script. Using the instructions on MSDN you can do the following: Click Start, click All Programs, click Microsoft SQL Server 2005 or Microsoft SQL Server 2008, … Read more
I needed something similar, but all DAYS instead of all MONTHS. Using the code from MatBailie as a starting point, here’s the SQL for creating a permanent table with all dates from 2000-01-01 to 2099-12-31: CREATE TABLE _Dates ( d DATE, PRIMARY KEY (d) ) DECLARE @dIncr DATE = ‘2000-01-01’ DECLARE @dEnd DATE = ‘2100-01-01’ … Read more
@Chris, you almost have it. ORDER BY (CASE WHEN [Submission Date] IS NULL THEN 1 ELSE 0 END) DESC, [Submission Date] DESC [Edit: #Eppz asked me to tweak the code above as currently shown] I personally prefer this a lot better than creating “magic numbers”. Magic numbers are almost always a problem waiting to happen.
SELECT DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS EndOfYear The above query gives a datetime value for midnight at the beginning of December 31. This is about 24 hours short of the last moment of the year. If you want to include time that might occur on … Read more
For SQL Server 2000 (and any other 32 bit edition of SQL Server), there is xp_pcre, which introduces Perl compatible regular expressions as a set of extended stored procedures. I’ve used it, it works. The more recent versions give you direct access to the .NET integrated regular expressions (this link seems to be dead, here … Read more
Your code in SSMS is not the same code you run in your application. This line in your application adds a NVARCHAR parameter: ada.SelectCommand.Parameters.AddWithValue(“@clientID”, ClientID); while in the SSMS script you declare it as VARCHAR: declare @clientID varchar(200) Due to the rules of Data Type Precedence the Where client_id = @clientID expression in your query … Read more
I realize this is two years old, but it bugs me that the accepted answer calls for using dynamic SQL and the most upvoted answer won’t work: Select P.ProductId, P.Name , Min( Case When PM.MetaKey = ‘A’ Then PM.MetaValue End ) As A , Min( Case When PM.MetaKey = ‘B’ Then PM.MetaValue End ) As … Read more
update TCalendar set = WeekNumberOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, FullDateAlternateKey), 0), FullDateAlternateKey) +1
Straightforward way? No, but I’ve used the reverse. Literally. In prior routines, to find the last occurence of a given string, I used the REVERSE() function, followed CHARINDEX, followed again by REVERSE to restore the original order. For instance: SELECT mf.name ,mf.physical_name ,reverse(left(reverse(physical_name), charindex(‘\’, reverse(physical_name)) -1)) from sys.master_files mf shows how to extract the actual … Read more