Date Name

 -- Each datepart and its abbreviations return the same value.

-- Year Name

     

      -- Datepart

            SELECT DATENAME(YEAR,GETDATE())

     

      -- Abbreviations

            SELECT DATENAME(YYYY,GETDATE())

            SELECT DATENAME(YY,GETDATE())

-- Day of Year

     

      -- Datepart

            SELECT DATENAME(DAYOFYEAR,GETDATE())

     

      -- Abbreviations

            SELECT DATENAME(y,GETDATE())

            SELECT DATENAME(DY,GETDATE())

-- Quarter

      -- Datepart

            SELECT DATENAME(QUARTER,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(Q,GETDATE())

            SELECT DATENAME(QQ,GETDATE())

-- Month Name

      -- Datepart

            SELECT DATENAME(MONTH,GETDATE())

     

      -- Abbreviations

            SELECT DATENAME(M,GETDATE())

            SELECT DATENAME(MM,GETDATE())

-- Week

            -- Week Day Name

           

                        -- Datepart

                        SELECT DATENAME(WEEKDAY,GETDATE())

                       

                        -- Abbreviations

                        SELECT DATENAME(DW,GETDATE())

                        SELECT DATENAME(W,GETDATE())

           

            -- Week Number in year

           

                        -- Date Part

                        SELECT DATENAME(WEEK,GETDATE())

                       

                        -- Abbreviations

                        SELECT DATENAME(WK,GETDATE())

            -- Week Number in year as per ISO

           

                        -- Date Part

                        SELECT DATENAME(ISO_WEEK,GETDATE())

                       

                        -- Abbreviations

                        SELECT DATENAME(ISOWK,GETDATE())

                        SELECT DATENAME(ISOWW,GETDATE())

           

-- Day Number of Month

     

      -- Datepart

            SELECT DATENAME(DAY,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(DD,GETDATE())

           

-- Hour

     

      -- Datepart

      SELECT DATENAME(HOUR,GETDATE())

     

      -- Abbreviations

      SELECT DATENAME(HH,GETDATE())

-- Minute

      -- Datepart

            SELECT DATENAME(MINUTE,GETDATE())

     

      -- Abbreviations

            SELECT DATENAME(MI,GETDATE())

            SELECT DATENAME(N,GETDATE())

-- Seconds

     

      -- Datepart

            SELECT DATENAME(SECOND,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(S,GETDATE())

            SELECT DATENAME(SS,GETDATE())

-- Milli seconds

     

      -- Datepart

            SELECT DATENAME(MILLISECOND,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(MS,GETDATE())

-- Microseconds

      -- Datepart

            SELECT DATENAME(MICROSECOND,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(MCS,GETDATE())

-- NanoSeconds

     

      -- Datepart

            SELECT DATENAME(NANOSECOND,GETDATE())

           

      -- Abbreviations

            SELECT DATENAME(NS,GETDATE())

Get week date range from date

Query 

SELECT

CONVERT(varchar(50), (DATEADD(DD, @@DATEFIRST - DATEPART(DW, GETDATE()) - 6, GETDATE())), 106) as [First Date]

,CONVERT(varchar(50), (DATEADD(DD, @@DATEFIRST - DATEPART(DW, GETDATE()), GETDATE())), 106) as [Last Date]

 Screen Shot

 

ReIndexing and Update Statistics

-- Database_StatisticUpdate 1

ALTER PROCEDURE Database_StatisticUpdate

(

@IsRecommandedUpdate BIT

)

AS

BEGIN

-- ReIndexing

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"

IF(@IsRecommandedUpdate = 1)

BEGIN

-- Update Stats

EXEC sp_updatestats

END

ELSE

BEGIN

DECLARE @TableName VARCHAR(MAX)

SET @TableName = ''

DECLARE @CurrentTableName NVARCHAR(500)

SET @CurrentTableName = ''

DECLARE @sDelimiter VARCHAR(2)

SET @sDelimiter = ','

DECLARE @SqlQuery NVARCHAR(MAX)

SET @SqlQuery = ''

SELECT @TableName = COALESCE(@TableName+@sDelimiter ,'')+ name from sys.tables

where sys.tables.type = 'U'

SET @TableName = LTRIM(RTRIM(@TableName))

WHILE (CHARINDEX(@sDelimiter,@TableName,0) <> 0)

BEGIN

SET @CurrentTableName = ''

SELECT @CurrentTableName=RTRIM(LTRIM(SUBSTRING(@TableName,1,CHARINDEX(@sDelimiter,@TableName,0)-1))),

@TableName=RTRIM(LTRIM(SUBSTRING(@TableName,CHARINDEX(@sDelimiter,@TableName,0)+LEN(@sDelimiter),LEN(@TableName))))

SET @CurrentTableName = LTRIM(RTRIM(@CurrentTableName))

IF(@CurrentTableName <> '')

BEGIN

SET @SqlQuery = ''

SET @SqlQuery = 'UPDATE STATISTICS '+@CurrentTableName+ ' WITH FULLSCAN'

PRINT @SqlQuery

EXEC SP_EXECUTESQL @SqlQuery

END

END

END

END

Create a Comma Delimited List Using SELECT Clause From Table Column

USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr
GO

Update Statistics in database

select * from (

SELECT name
AS index_name,type, sys.indexes.type_desc,sys.indexes.object_id,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated,
datediff(day, STATS_DATE(OBJECT_ID, index_id) ,getdate()) AS DaysOld

FROM sys.indexes
WHERE OBJECT_ID in (SELECT sys.tables.object_id from sys.tables
where sys.tables.type = 'U')
) as t1
where DaysOld is not NULL --AND DaysOld <> 0
order by DaysOld desc


EXEC sp_updatestats (to update Statistic at Database level)