In case you need an overview which T-SQL type maps to which C# type, here’s a nice page I found:
Sometimes you need to round DateTimes in T-SQL to a certain DateTime component. For example, you need to truncate the minutes and seconds and only keep the date and the hour. To do so, you can do this:
SELECT DATEADD(hh, DATEDIFF(hh, '20000101', getdate()), '20000101')
This turns 20120101 13:59:59 into 20120101 13:00:00. Please note that the comparison date 20000101 is totally arbitrary. You can use any day before the date you’re truncating.
Sometimes you need to return from a stored procedure the results of a SELECT as a comma-separated string.
Example: The SELECT returns:
But you want the stored procedure to return:
Hello, World, It's, Me
Instead of looping with a cursor you can solve this pretty elegantly like the following:
The AND Field IS NOT NULL at the end of the WHERE-clause is important, because otherwise the result would always be null if one of the values was null (because something + NULL = NULL).
In MySQL you can use LIMIT to page results. There’s nothing like that in T-SQL, but with a little effort you get the same result:
This returns 15 entries starting at entry 45 in table tCustomer. Please note that the page number is zero-based here, that’s why we use ROW_NUMBER() OVER (ORDER BY LastName, FirstName)) – 1.
I used to think that the TOP-clause could not be parameterized like the LIMIT-clause in MySQL. Well, I was wrong. All you need to do is use parentheses:
You can use the following T-SQL code to select the n-th row from the end of a table: