Creating Table, Populating the Dates, Retrieving the Weekends in SQL Server 2005

Below Script Create the DateLookup Table:

CREATE TABLE DateLookup
(
DateKey INT PRIMARY KEY,
DateFull DATETIME,
CharacterDate VARCHAR(10),
FullYear CHAR(4),
QuarterNumber TINYINT,
WeekNumber TINYINT,
WeekDayName VARCHAR(10),
MonthDay TINYINT,
MonthName VARCHAR(12),
YearDay SMALLINT,
DateDefinition VARCHAR(30),
WeekDay TINYINT,
MonthNumber TINYINT
)

The below Script Populate the Dates in to the lookup table:

DECLARE @Date DATETIME
SET @Date = '1/1/2009'

WHILE @Date < '1/1/2011'
BEGIN
INSERT INTO DateLookup
(
DateKey, DateFull, FullYear,
QuarterNumber, WeekNumber, WeekDayName,
MonthDay, MonthName, YearDay,
DateDefinition,
CharacterDate,
WeekDay,
MonthNumber
)
SELECT
CONVERT(VARCHAR(8), @Date, 112), @Date, YEAR(@Date),
DATEPART(qq, @Date), DATEPART(ww, @Date), DATENAME(dw, @Date),
DATEPART(dd, @Date), DATENAME(mm, @Date), DATEPART(dy,@Date),
DATENAME(mm, @Date) + ' \' + CAST(DATEPART(dd, @Date) AS CHAR(2)) + ',\'
+ CAST(DATEPART(yy, @Date) AS CHAR(4)),
CONVERT(VARCHAR(10), @Date, 101),
DATEPART(dw, @Date),
DATEPART(mm, @Date)

SET @Date = DATEADD(dd, 1, @Date)
END


To Get the WeekEnds(Saturdays & Sundays) Use the Below Query:

select * from DateLookup where datepart(w,DateFull) in (1) or datepart(w,DateFull) in (7)

SQL Server: Date Formats

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
Result : 2009/03/05

SELECT CONVERT(VARCHAR(10), GETDATE(), 11) AS [YY/MM/DD]
Result : 09/03/05

SELECT CONVERT(VARCHAR(30), GETDATE(), 131)as Datetime
Result : 9/03/1430 2:05:32:957PM

SELECT CONVERT(VARCHAR(22), GETDATE(), 100)as datetime
Result : Mar 5 2009 2:06PM

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
Result :03/05/2009

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
Result :03-05-09

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
03-05-2009

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
Result :Mar 05, 09

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
Result :Mar 05, 2009

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
Result :05 Mar 09

SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
Result :05 Mar 2009


SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
Result :05.03.09

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
Result :05.03.2009

SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
Result :05-03-09


SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
Result :05-03-2009

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
Result :05/03/09

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
Result :05/03/2009

SQL Server 2005 : Split Function

CREATE FUNCTION dbo.Splitter(@strList VARCHAR(8000),@strSeperator VARCHAR(10))
Returns @tblSplit TABLE (splitValue VARCHAR(255))
AS
BEGIN
DECLARE @tmp_FnCommaSplitter TABLE (splitValue VARCHAR(255))
DECLARE @strTmp VARCHAR(5000)
DECLARE @pos INT
DECLARE @lenSeperator INT
SELECT @lenSeperator = LEN(@strSeperator)
SELECT @strTmp = @strList
SELECT @pos = PATINDEX('%'+@strSeperator+'%',@strTmp)
WHILE (@pos <> 0)
BEGIN
IF SUBSTRING(@strTmp,1,@pos-1) <> ''
INSERT @tmp_FnCommaSplitter VALUES (SUBSTRING(@strTmp,1,@pos-1))

SELECT @strTmp = SUBSTRING(@strTmp,@pos+@lenSeperator,5000)
SELECT @pos = PATINDEX('%'+@strSeperator+'%',@strTmp)
END
IF @strTmp <> ''
INSERT @tmp_FnCommaSplitter VALUES (LTRIM(RTRIM(@strTmp)))
INSERT @tblSplit SELECT * FROM @tmp_FnCommaSplitter
RETURN
END

SQL Datechecking

DECLARE @datestring varchar(8)
SET @datestring = '12/21/98'
SELECT ISDATE(@datestring)