SQL datetime & UTC

A datetime nem tárol DateTimeKind-ot ezert jobb megoldás lehet a datetimeoffset, de ha ez nem megoldható viszont tudjuk a Kind-ot (itt UTC) akkor miután kiolvastuk a DateTime-ot az SQLDataReader-rel annak kétféleképpen is megadhatjuk a típusát:

• DateTime unspecified = reader.GetDateTime(index);
  DateTime d = new DateTime(unspecified.Ticks, DateTimeKind.UTC);
• DateTime unspecified = reader.GetDateTime(index);
  DateTime d2= DateTime.SpecifyKind(unspecified, DateTimeKind.UTC);

Persze jobb ezt egy extensionbe tenni:

public static class SqlDataReaderExtensions {

    public static DateTime GetDateTimeUtc(this SqlDataReader reader, int columnOrdinal) {

        DateTime dt = new DateTime(reader.GetDateTime(0).Ticks, DateTimeKind.Utc);

        return dt;

    }

}

és utána ezt használni:

DateTime d = reader.GetDateTimeUTC(index);

Datalength - NText, Text, Image mezok hosszanak lekerkdezesere

Itt irjak, hogy ha NText, Text vagy Image mezok hosszat szeretnenk megtudni arra nem fog mukodni a LEN() fv, de a DATALENGTH() mar igen.

Jeff Smith: Conditional Joins in SQL Server

Itt van reszletesen, de a lenyeg:

Sometimes, when writing SELECTs, we come across situations in which we we need to write a join containing a condition of some sort. You might think to do this with either a CASE expression or with some OR boolean logic in your JOIN expression. Often, you might encounter syntax errors, performance problems, or results not being returned the way you expect when trying this. There’s a much better way to approach the problem.

Instead of trying to alter a single INNER JOIN’s relation from row to row, instead use multiple LEFT OUTER JOINS in your SELECT, one for each table or condition that you need to consider.

Transact-SQL: ISNULL(), COALESCE()

COALESCE(Transact-SQL): Returns the first nonnull expression among its arguments.

ISNULL(Transact-SQL): Replaces NULL with the specified replacement value.