Monthly Archives: March 2011

C# ternary operator (?:) and Null Coalescing Operator (??)

Using if statement

if (value != null){    litCurrentSurveyName.Text = value;}else{    litCurrentSurveyName.Text = string.Empty;}

Using ternary operator (?:)

litCurrentSurveyName.Text = value != null ? value : string.Empty;

null-coalescing operator (??) which is a shortcut for the ternary operator (?:)

litCurrentSurveyName.Text = value ?? string.Empty;

SQL Multiple REPLACE

ALTER FUNCTION [dbo].[ReplacePattern](     @P_Pattern VARCHAR(50)) RETURNS NVARCHAR(255)AS BEGIN                         SELECT                     @P_Pattern = REPLACE(@P_Pattern,  '1', 'Sundays'),                    @P_Pattern = REPLACE(@P_Pattern,  '2', 'Mondays'),                    @P_Pattern = REPLACE(@P_Pattern,  '3', 'Tuesdays'),                    @P_Pattern = REPLACE(@P_Pattern,  '4', 'Wednesdays'),                    @P_Pattern = REPLACE(@P_Pattern,  '5', 'Thursdays'),                    @P_Pattern = REPLACE(@P_Pattern,  '6', 'Fridays'),                    @P_Pattern = REPLACE(@P_Pattern,  '7', 'Saturdays')    RETURN          @P_PatternEND

Usage:

SELECT dbo.ReplacePattern('1,2,3,4')

Output:

C# How to round fraction to 15

private double RoundOffset(double offset){    double value = Math.Truncate(offset);    double fraction = offset - Math.Truncate(offset);    if (fraction < 0.25) {        return value + 0.0;    } else if (fraction >= 0.25 && fraction < 0.5) {        return value + 0.25;    } else if (fraction >= 0.5 && fraction < 0.75) {        return value + 0.5;    } else if (fraction >= 0.75 && fraction < 1) {        return value + 0.75;    }    return fraction;}

Usage:

double test = 1.17; Console.WriteLine(test + ": " + RoundOffset(test));

Output:

1.17: 1.15

 

SQL Select rows with date within another date range

CREATE TABLE TestTable(   ID           INT PRIMARY KEY,   StartDate    SMALLDATETIME,   EndDate      SMALLDATETIME);insert into TestTable(ID,StartDate,EndDate) values (1,'2000-11-11 10:11:11', '2000-11-11 13:11:11');insert into TestTable(ID,StartDate,EndDate) values (2,'2000-11-11 10:22:22', '2000-11-11 14:11:11');insert into TestTable(ID,StartDate,EndDate) values (3,'2000-11-11 10:32:22', '2000-11-11 14:11:11');insert into TestTable(ID,StartDate,EndDate) values (4,'2000-11-11 11:01:11', '2000-11-11 15:11:11');insert into TestTable(ID,StartDate,EndDate) values (5,'2011-02-02 22:22:22', '2011-02-22 22:22:22');insert into TestTable(ID,StartDate,EndDate) values (6,'2000-11-11 11:32:22', '2000-11-11 14:11:11');SELECT * FROM TestTable

SQL Update using JOIN

DECLARE @SampleDataTable TABLE (    ID INT IDENTITY(1,1) PRIMARY KEY,    Date SMALLDATETIME,     EndDate SMALLDATETIME)INSERT INTO @SampleDataTable     SELECT '2011-03-01', NULL    UNION ALL    SELECT '2011-03-02', NULL    UNION ALL    SELECT '2011-03-03', NULL    UNION ALL    SELECT '2011-03-04', NULL    UNION ALL    SELECT '2011-03-05', NULL    SELECT * FROM @SampleDataTable

SQL Variable TABLE quick setup and insert

DECLARE @SampleDataTable TABLE (    ID INT IDENTITY(1,1) PRIMARY KEY,    Date SMALLDATETIME,     EndDate SMALLDATETIME)INSERT INTO @SampleDataTable     SELECT '2011-03-01', NULL    UNION ALL    SELECT '2011-03-02', NULL    UNION ALL    SELECT '2011-03-03', NULL    UNION ALL    SELECT '2011-03-04', NULL    UNION ALL    SELECT '2011-03-05', NULL    SELECT * FROM @SampleDataTable

OR

DECLARE @SampleDataTable TABLE (    ID INT IDENTITY(1,1) PRIMARY KEY,    Date SMALLDATETIME,     EndDate SMALLDATETIME)INSERT INTO @SampleDataTable (     Date, EndDate )(    SELECT '2011-03-01', NULL    UNION ALL    SELECT '2011-03-02', NULL    UNION ALL    SELECT '2011-03-03', NULL    UNION ALL    SELECT '2011-03-04', NULL    UNION ALL    SELECT '2011-03-05', NULL)SELECT * FROM @SampleDataTable

SQL Add Random Number of Days to a Date

DECLARE @MinValue INT, @MaxValue INT, @StartDateTime SMALLDATETIME SET @MinValue = 1 SET @MaxValue = 360 SET @StartDateTime = CONVERT(Varchar(10), GETDATE(), 120) -- 2011-03-01 00:00:00

Method 1

SELECT @StartDateTime AS CurrentDate, DATEADD(DAY, ROUND(((@MaxValue - @MinValue -1) * RAND() + @MinValue), 0), @StartDateTime) AS RandomFutureDate

SQL Generate Random DateTime

DECLARE @MinDateValue SMALLDATETIME, @MaxDateValue SMALLDATETIMESET @MinDateValue = '2011-01-01'SET @MaxDateValue = '2012-12-30'SELECT(                @MinDateValue +    (                                        ABS        (            CAST                (                    CAST( NewID() AS BINARY(8) ) AS INT                )        )    %    CAST        (            (@MaxDateValue - @MinDateValue) AS INT        )            ))