MS SQL: Date portion from DateTime

To return only the date portion of a DateTime variable, you need to use a combination of the dateadd and datediff functions in Transact-SQL.

-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.

CREATE  function [dbo].[sudf_Common_DateOnly]

(

@dtDateTime DateTime

)

returns datetime

as

begin

      -- Get the date only

    return dateadd(dd, 0, datediff(dd, 0, @dtDateTime))

end

MS SQL: Create Date

There is no straight forward function in Transact-SQL to create a simple date based on year, month and day. Below we have created a userdefined function to handle this. Notice that the base date in Microsoft SQL Server is January 1st 1900. We generate the date by adding the year, month and day to the base date.

CREATE function [dbo].[sudf_Common_Date]

(

      @intYear int,

      @intMonth int,

      @intDay int

)

-- returns a datetime value for the specified year, month and day

returns datetime

as

begin

      -- Returns a date later than the base date (1.1.1900)

    return dateadd(month,((@intYear-1900)*12)+@intMonth-1,@intDay-1)

end

MS SQL: Time portion from DateTime

To get only the Time portion of a DateTime variable in Transact-SQL, you can use the following function:

-- Returns only the time portion of a DateTime, at the "base" date (1/1/1900)

CREATE function [dbo].[sudf_Common_TimeOnly]

(

      @dtDateTime datetime

)

returns datetime

as

begin

      -- Get the time only

    return dateadd(day, -datediff(day, 0, @dtDateTime), @dtDateTime)

end

Please notice that the base date is January 1st 1900. Any dates/times prior to this day won't work,

MS SQL: Constants

Transact-SQL does not offer a good way to support constants in your stored procedures or userdefined functions. This means that you either can choose to hardcode your constants or, the more elegant way, define a userdefined function for each "constant" value. To ensure optimal performance, the schemabinding keyword should be used.

Here is an example:

CREATE FUNCTION [dbo].[sudf_Security_Privilege_User]

(

)

RETURNS INT

with schemabinding

AS

BEGIN

      -- Return the access level

      RETURN (1);

END

In your stored procedure you can call the function the normal way, i.e.

set x = dbo.sudf_Security_Privilege_User();

MS SQL: MD5 Hash

The non-reversable hashing algorithm MD5 is supported in Microsoft SQL Server, but is not directly accessable though a simple transact-SQL function. Normally you would store the MD5 hash value as a hexadecimal string in your database. The HashBytes function returns a binary array of hash data. To convert the binary data to hex we need to use the function fn_varbintohexstr.


To make your code easier to read, I've made a wrapper in a custom userdefined function:

CREATE FUNCTION [dbo].[sudf_Common_Md5Hash]

(

      -- Add the parameters for the function here

      @strValue nvarchar(max)

)

RETURNS nvarchar(32)

AS

BEGIN

      -- Declare the return variable here

      declare @strResult nvarchar(32)

      -- Generate the MD5

      set @strResult = SubString(master.dbo.fn_varbintohexstr(HashBytes('MD5', @strValue)), 3, 32)

     

      -- Return the result of the function

      RETURN @strResult

END

MS SQL: Url Encode

There is no built-in function in Microsoft SQL Server to to support URL encoding. If you want to generate URLs with arguments on the fly in stored procedures, you would have to do the URL encoding yourself. URL encoding can be implemented in several ways. Either you can create your own custom userdefined function in MS SQL or create a custom library in .NET. The library can then be called from a stored procedure.

Here I will show how you can encode your URLs using a userdefined function:

CREATE FUNCTION [dbo].[audf_Common_UrlEncode](@strUrl varchar(max))

returns varchar(max)

AS

 begin

    -- Declare variables

    declare @intCount int,

            @strChar char(1),

            @i int,

            @strUrlReturn varchar(max)

    -- Initialize variables

    set @intCount       = Len(@strUrl);

    set @i              = 1;

    set @strUrlReturn   = '';  

    -- Loop through all characters

    while (@i <= @intCount)

    begin

        -- Get the character

        set @strChar = substring(@strUrl, @i, 1)

        -- Is ASCII character?

        if @strChar LIKE '[A-Za-z0-9()''*-._! ]'

         begin

            -- Just append character

            set @strUrlReturn = @strUrlReturn + @strChar

         end

        else

         begin

            -- Encode and append character

            set @strUrlReturn =

                   @strUrlReturn +

                   '%' +

                   SUBSTRING(sys.fn_varbintohexstr(CAST(@strChar as varbinary(max))),3,2)

         end

        -- Next character

        set @i = @i +1

     end

    -- Return the encoded URL

    return @strUrlReturn

 end

MS SQL: Atomic Insert or Update (UPSERT)

It is not straight forward to create an atomic insert or update statement in SQL Server, i.e. we update the row if it exists. Otherwise we insert a new row.

The following statement (notice the locks) will create a more or less atomic statement:


-- Start transaction
begin tran


-- Row Exists?
if not exists (select * from <table> with (updlock, rowlock, holdlock) where <PK = ...>    
begin
        <insert>
end
else
begin
        <update>
end

-- End Transaction
commit

Facebook Dev: Free SSL Certificates

Since the announcement of the new Facebook requirement regarding the support of secure canvas for both Facebook Apps and Facebook Pages (from October 1st 2011), numerous articles have been written to explain how you can set up your webserver using either costly SSL certificates or free shared SSL certificates.

Interested readers may refer to the following articles for more information:

• Facebook Pages SSL Deadline Approaches
• How to make your Facebook Fan Page SSL Secure – For Free (Hostgator)

Free SSL certificates from StartCom
One option they have left out is the installation of free SSL certificates on your webserver to comply with the new requirements. Various certificate providers offer free for a year certificates, limited certificates, and even totally free SSL certificates to you. The catch is that some of the free certificates are only trusted in new web browsers or they only cover the lowest level of security. For more advanced features you still have to pay. However, for new Facebook Apps the free SSL certificates might very well cover your needs in your production environment, or simply as work as temporary developer certificates.

Back in 2009 Microsoft included StartCom Ltd. in their trusted root certificates. StartCom offers free digital certificates which you can install on your webserver.

For instructions on how to configure your Apache server, check out the good guide by Jason Weathered:

• A free SSL certificate for your web server

Free webhosting
If you are not familiar with chained certificates or the Apache webserver (or any other webserver) you should consider finding a webhost for your website. For students or test-applications it is also worth cheking out the free webhosting offered for ASP.NET and PHP applications:

• Free webhosting for your Facebook App

MS SQL: Store Color (ARGB) as integer

The most common way is to store colors in a SQL database as a string, i.e. the HTML color code (ex. #FFFFFF). The colors can also be stored as 32 bit integers. A 32 bit color value (including alpha channel) can be calculated in the following way:

@Alpha * 16777216 + @Red * 65536 + @Green * 256 + @Blue


The full userdefined function is as follows:


-- Description: Generate ARGB Color value
-- =============================================
CREATE FUNCTION [dbo].[sudf_Color_FromArgb]
(
 @intAlpha tinyint,
 @intRed  tinyint,
 @intGreen tinyint,
 @intBlue tinyint
)
RETURNS int
AS
BEGIN

declare @lngColor bigint;
declare @intColor int;

 -- Generate the color
 set @lngColor = (
      (cast(@intAlpha as bigint) * 16777216) +
      (cast(@intRed as bigint) * 65536) +
      (cast(@intGreen as bigint) * 256)  +
      (cast(@intBlue as bigint))
     );

 -- Get the color
 set @intColor = cast(cast(@lngColor as binary(4)) as int);

 -- Return the color
 return @intColor;
END

MS SQL: First Day of Week

In Transact-SQL the first day of week can be calculated based on the @@FirstDay system variable.
The variable will return different values depending of configured language of the server. For US English the first day of week will be set to 7, i.e. Sunday. In .NET a different day range is used.
We can use the following calculation to ensure that Sunday is 0, Monday = 1, etc.

 @@datefirst % 7


The full user defined function is as follows:

 --   Description: Returns the first weekday of the week
--    based on the system settings on the DB server:
--                              0 - Sunday
--                              1 - Monday
--                              2 - Tuesday
--                              3 - Wednesday
--                              4 - Thursday
--                              5 - Friday
--                              6 - Saturday
--                             
-- =============================================
ALTER FUNCTION [dbo].[sudf_Calendar_FirstDayOfWeek]
(      
)
RETURNS int
AS
BEGIN
        -- Variables
        declare @intFirstDayOfWeek  int;

        -- The first day of week
        set @intFirstDayOfWeek = (@@datefirst % 7);
       
        -- Return the first day of the week
        return @intFirstDayOfWeek;
END