Mareo Code

//Tested on .NET 2.0
ListItem li = yourDropDownlist.Items.FindByValue("yourValue");
if (li != null)
    yourDropDownlist.SelectedIndex = yourDropDownlist.Items.IndexOf(li);

You can match the characters not within a range by complementing the set. This is indicated by including a “^” as the first character of the class; “^” elsewhere will simply match the “^” character. For example, [^,] will match any character except “,”.

-- How to get shordate from full date format
-- Input: date (datetime) Output: yyyy-mm-dd (varchar)

SELECT
    CONVERT(VARCHAR(10),GETDATE(),120) as Date

Microsoft SQL Server 2005

I almost finished my current C# project where I have created a lot of stored procedures. By a lot I mean almost 200 :) (I know it is a personal feeling :)) Few days ago I was just curious how many lines I wrote in my stored procedures. To satisfy my curiosity I wrote T-SQL code which show me the truth ;)

Blank lines with just Enter pressed are not counted.

Score for my project database is: 142 stored procedures, 9419 lines (most of all 537 lines in one SP)

@ResultType = 1

@ResultType = 0

So what is your best score? ;) How many SPs and lines did you write in your project? :)

SQL code below.
Read more…

This ISO 8601 weeks standard is mostly in use in corporations and business.

The International Organization for Standardisation, based in Switzerland, issued Standard 8601 — Representation Of Dates And Times, in 1988. This provides some standardization for “week numbers”. Of course, compliance with these standards is entirely voluntary, so your business may or may not use the ISO definitions.

While this provides some standardization, it can lead to unexpected results — namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec.

Under the ISO standard, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

-- Here it is example from SQL Book Online by Microsoft
CREATE FUNCTION [dbo].[ISOweek]  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0)
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END

Read this article if you want to learn more about week number calculations.
http://www.cpearson.com/excel/weeknum.htm

Remember to add always:

SET DATEFIRST 1

before any date calculation in this standard

Example of use above User Definied Function:

SET DATEFIRST 1

SELECT dbo.ISOweek(getdate())

The function determines if it is a leap year or not.  Basically, to determine if it is a leap year, either of the following conditions must be met:

• The year must be divisible by 4 and must NOT be divisible by 100.
• The year must be divisible by 400.

Original article: http://www.sql-server-helper.com/functions/is-leap-year.aspx

--User definied function to get number of days

CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @pDate    DATETIME )
RETURNS BIT
AS
BEGIN

IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
RETURN 1

RETURN 0

END
GO

Example of use:

DECLARE @DaysInYear INT

SET @DaysInYear = 365 + [dbo].[ufn_IsLeapYear] (GETDATE())

--For example to find in Users the LastName which occurs more then 1 time:
SELECT
    LastName
    , COUNT(LastName) AS NumberOccurrences
FROM
    Users
GROUP BY
    LastName
HAVING
    ( COUNT(LastName) > 1 )

--Same technique to find rows that occur exactly once:
SELECT
    LastName
FROM
    Users
GROUP BY
    LastName
HAVING
    ( COUNT(LastName) = 1 )

SELECT
    DATEADD(mm, 1 + DATEDIFF(mm, 0, GETDATE()), 0) - 1

SELECT
    DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

//Tested on .NET 2.0

public static bool IsValueInDropdownList(DropDownList controlName,
string strValue)
{
    if (controlName.Items.FindByValue(strValue) != null)
        return true;

    return false;
}