SQL Server – Calender details

DECLARE @StartDate DATE = ‘2014-01-01′, @EndDate DATE = ‘2014-12-31′ ;With CTEAS(SELECT @StartDate dt UNION ALL SELECT DATEADD(DAY, 1, dt) FROM CteWHERE dt < @EndDate)SELECT FORMAT ( dt, ‘D’, ‘en-US’ ) Calender,DATENAME(dayofyear, dt) DayNumber_Year,DATEPART(day, dt) DayNumber_Month,DATEPART(weekday, dt) DayNumber_Week,DATENAME(weekday, dt) DayName,IIF ( DATEPART(weekday, dt) IN (1,7) , ‘Weekend’, ‘Weekday’ ) Day,DATENAME(month, dt) MonthName,DATEPART(month, dt) MonthNumber,DATENAME(year, dt) Year,EOMONTH…

Details

SQL Server 2012 – PARSENAME function – To split delimited data

PARSENAME  –  Returns the specified part of an object name. Syntax : PARSENAME ( ‘object_name’ , object_piece ) Examples: DECLARE @ObjectDetail VARCHAR(100) = ‘SQLServer.AdventureWorks2012.Sales.Customer’ SELECT @ObjectDetail ObjectDetail SELECT PARSENAME(@ObjectDetail,4) [Server],PARSENAME(@ObjectDetail,3) [Database],PARSENAME(@ObjectDetail,2) [Schema],PARSENAME(@ObjectDetail,1) [Table] –usually Dot(.) is considered as the delimiter by PARSENAME function DECLARE @pipedelimited VARCHAR(50) = ‘Chennai|TamilNadu|India’ SELECT PARSENAME(REPLACE(@pipedelimited,‘|’,‘.’),3)City,PARSENAME(REPLACE(@pipedelimited,‘|’,‘.’),2) State,PARSENAME(REPLACE(@pipedelimited,‘|’,‘.’),1) Country DECLARE @DateTime DATETIME…

Details

SQL Server 2012 – IIF ( ) and CHOOSE ( )

IIF – Returns one of two values, depending on whether the Boolean expression evaluates to true or false. Syntax: IIF ( boolean_expression, true_value, false_value )   Examples : SELECT IIF(1>2,’TRUE’,’FALSE’)GO   output: FALSE        SELECT IIF(2>1,’TRUE’,’FALSE’)GO output: TRUE     SELECT IIF(1>2,NULL,NULL)GO   Msg 8133, Level 16, State 1, Line 1At least one…

Details

SQL Server 2012 – THROW – Enhancement in error handling using TRY CATCH

THROW  statement can be used to raise an exception,like RAISERROR Syntax :  THROW [ { error_number | @local_variable }, { message | @local_variable },    { state | @local_variable }] [ ; ] error_number  error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647. message message is nvarchar(2048).…

Details

SQL Server 2012 – CONCAT() – String function

CONCAT function, which is available in SQL Server 2012, returns a string that is the result of concatenating two or more string values. The CONCAT function requires a minimum of two input values ,else the concatenation fails. SYNTAX: –                     CONCAT (string_value1, string_value2 [,string_valueN]) In previous versions of SQL Server ,we had plus symbol ”…

Details