First and Last Date of Last,Current and Next month using sqlserver 2008

DECLARE @Date DATETIME
SELECT @Date = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date)),@Date),101) ,
'LastDayof PreviousMonth'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date)-1),@Date),101) AS Date_Value,
'FirstDayofCurrentMonth' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@Date,101) AS Date_Value,
'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))),DATEADD(mm,1,@Date)),101) ,
'LastDayofCurrentMonth'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))-1),DATEADD(mm,1,@Date)),101) ,
'FirstDayofNextMonth'
GO

Select values with monthly based and order by month with case method

SELECT CAST(DATENAME(MONTH, TimeOut)AS VARCHAR(50))+'-'+CAST(DATENAME(YEAR, TimeOut)AS VARCHAR(50))[Time],
R.[Researcher] ,
'10k' [Project],
CONVERT(VARCHAR(10),B.[Filingdate],101) [FilingDate],
'10k'[Form],
COUNT(*)[NormalFiles],
COUNT(CASE RED_FLAG WHEN 'type1' THEN 1 END)[Type1],
COUNT(CASE RED_FLAG WHEN 'type2' THEN 1 END)[Type2],
COUNT(CASE RED_FLAG WHEN 'type3' THEN 1 END)[Type3],
COUNT(CASE RED_FLAG WHEN 'type4' THEN 1 END)[Type4],
COUNT(CASE RED_FLAG WHEN 'type5' THEN 1 END)[Type5],
COUNT(CASE RED_FLAG WHEN 'type6' THEN 1 END)[Type6],
COUNT(CASE A.Status WHEN 'RecordNotFound' THEN 1 END)[NewCompanies],

COUNT(*)+
COUNT(CASE RED_FLAG WHEN 'type1' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type2' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type3' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type4' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type5' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type6' THEN 1 END)+
COUNT(CASE A.Status WHEN 'RecordNotFound' THEN 1 END)[Total]

FROM [10K2K9] A JOIN T_Allocation B
ON A.Record_ID = B.Record_ID
LEFT JOIN T_Tracking E on E.Enterprise_number = B.Enterprise_Number
join T_Accounts R on R.EmployeeID= A.Researcher
WHERE TimeOut IS NOT NULL
GROUP BY DATENAME(MONTH, TimeOut),R.[Researcher],CONVERT(VARCHAR(10),B.[Filingdate],101) ,DATENAME(YEAR, TimeOut)
ORDER BY CASE DATENAME(MONTH, TimeOut) WHEN 'January' THEN'01'
WHEN 'February'THEN'02'
WHEN 'March'THEN'03'
WHEN 'April'THEN'04'
WHEN 'May'THEN'05'
WHEN 'June'THEN'06'
WHEN 'July'THEN'07'
WHEN 'August'THEN'08'
WHEN 'September'THEN'09'
WHEN 'October'THEN'10'
WHEN 'November'THEN'11'
WHEN 'December'THEN '12'
ELSE '1' END

Finding week of the month in sqlserver 2005

DECLARE @DATE VARCHAR(50)
SET @DATE ='04/03/2009'
select cast(datename(week,@DATE) as int)- cast( datename(week,dateadd(dd,1-day(@DATE),@DATE)) as int)+1
---------------------------


SELECT DATEPART(DAY, GETDATE()-10) / 7 AS WeekofMonth

Select Date between two different date values

DECLARE @FromDate VARCHAR(50)
DECLARE @ToDate VARCHAR(50)
SET @FromDate='2009/02/20'
SET @ToDate ='2009/03/25'

DECLARE @TEMPDATE VARCHAR(50)
--DROP TABLE #TEMP
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[#TEMP]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[#TEMP]
CREATE TABLE #TEMP(TEMPEDATE datetime)

IF @FromDate IS NOT NULL
BEGIN
WHILE CAST(CONVERT(VARCHAR(10),@FromDate,101) AS DATETIME)
<=CAST(CONVERT(VARCHAR(10),@ToDate,101)AS DATETIME)
BEGIN
INSERT INTO #TEMP (TEMPEDATE)
SELECT CONVERT(VARCHAR(10),@FromDate,101)
SET @FromDate=CONVERT(VARCHAR(10),CAST(@FromDate AS DATETIME)+1,101)
END
END
select CONVERT(VARCHAR(10),TEMPEDATE,101) from #TEMP
select CONVERT(VARCHAR(10),TEMPEDATE,102) from #TEMP
select CONVERT(VARCHAR(10),TEMPEDATE,103) from #TEMP
SELECT * FROM #TEMP

select column names and table name in sqlserver

SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS


SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='T_Tracking'