A Journey 2 Eternity

Archive for the ‘SQL’ Category


DECLARE @startdate SMALLDATETIME = '2011-01-01'
DECLARE @stopdate SMALLDATETIME = '2011-12-31'

-- Create a Table
DECLARE @weekTable TABLE (
Id INT IDENTITY (1, 1),
StartDate SMALLDATETIME,
EndDate SMALLDATETIME
)

-- Find Monday at that week
DECLARE @currentDate SMALLDATETIME = DATEADD(d, 2 - DATEPART(dw, @startdate), @startdate)

WHILE @currentDate <= @stopdate BEGIN
INSERT INTO @weekTable VALUES (@currentDate, DATEADD(d, 6, @currentDate))
SET @currentDate = DATEADD(ww, 1, @currentDate)
END

-- Return the results
SELECT Id 'Week #', StartDate 'Start Date', EndDate 'End Date'
FROM @weekTable

Tags: ,
DECLARE @Date datetime
SET @Date = '2011/11/16'
SELECT  DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week',
DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week',
DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Total day of the month'
Tags: ,

The following query retrieving the table column details.

SELECT COLUMN_NAME [Field],DATA_TYPE + '(' + ISNULL(CONVERT(NVARCHAR,CHARACTER_MAXIMUM_LENGTH),'') + ')' + '    ' + CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT ' ELSE '' END + 'NULL' [Type] 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'account' 
ORDER BY ORDINAL_POSITION ASC
Tags: ,

Pages

Categories

April 2017
M T W T F S S
« Aug    
 12
3456789
10111213141516
17181920212223
24252627282930

Blog Stats

  • 26,880 hits