T-SQL: CONCAT Built-In Function
- Details
- Written by ranjeet
CONCAT is a string function which use to concatenate two or more strings. This function is applied in SQL server 2012 to current version available. It takes at least two parameters. It would through an error in case of single parameter passed.
I was always thinking why Microsoft has not introduced concatenate function which is very common through all the tools. Like in Excel there is CONCATENATE function which does similar task. Anyway now we do have same in SQL Server 2012 and in further edition.
Since it a string function, it implicitly converts it’s all the input parameters in to string and then combine/concatenate it. Please see below example queries:
Query 1:
SELECT CONCAT('ABC',' and','XYZ is brother')as ConcateOuput
T-SQL: FILEGROUPPROPERTY Built-In Function
- Details
- Written by ranjeet
FILEGROUPPROPERTY built-in function is another important one which give us complete list of property value on database file group level. We get the file properties value in one line command. We can get almost all the property’s value by using this function. Since properly list is very limited here and all important. I am listing all of those as below:
-->> FILEGROUPPROPERTY
SELECT CASE WHEN FILEGROUPPROPERTY('PRIMARY','IsReadOnly')= 1 THEN 'ENABLED' ELSE 'DISABLED' END as IsReadOnly
SELECT CASE WHEN FILEGROUPPROPERTY('PRIMARY','IsUserDefinedFG')= 1 THEN 'ENABLED' ELSE 'DISABLED' END as IsUserDefinedFG
SELECT CASE WHEN FILEGROUPPROPERTY('PRIMARY','IsDefault')= 1 THEN 'ENABLED' ELSE 'DISABLED' END as IsDefault
T-SQL: FILEPROPERTY Built-In Function
- Details
- Written by ranjeet
FILEPROPERTY built-in function is another important one which give us complete list of property value on database file level. We get the file property value in one line command. We can get almost all the property’s value by using this function. Since properly list is very limited here and all important. I am listing all of those as below:
-->> FILEPROPERTY
SELECT CASE WHENFILEPROPERTY('DBFileName','IsReadOnly')= 1 THEN 'ENABLED' ELSE 'DISABLED' END as IsReadOnly
SELECT CASE WHEN FILEPROPERTY('DBFileName','IsPrimaryFile')= 1 THEN 'ENABLED' ELSE 'DISABLED' END as IsPrimaryFile
SELECT CASE WHEN FILEPROPERTY('DBFileName','IsLogFile')= 1 THEN 'ENABLED' ELSE 'DISABLED' END as IsLogFile
SELECT FILEPROPERTY('DBFileName','SpaceUsed')*8/1024 as SpaceUsedMB
T-SQL: How to get Only Hour Minute and Second from datetime column
- Details
- Written by ranjeet
Convert built-In conversion function has very rich number of code to convert datatime into different format. Here we will convert from datetime to only Hour Minute and Second.
Below code will give you Hour Minute and Second from datetime column. To get complete list of code, please refer MSDN site.
Query to get only month and date together:
To get Hour and Minute in 12 hour date format from datetime column
Query 1:
select substring(convert(char(20),getdate(),109), 14, 7) as HourMinute
Output:
T-SQL: How to get Only Hour and Minute from datetime column
- Details
- Written by ranjeet
Convert built-In conversion function has very rich number of code to convert datatime into different format. Here we will convert from datetime to only Hour and Minute.
Below code will give you Hour and Minute from datetime column. To get complete list of code, please refer MSDN site.
Query to get only month and date together:
To get Hour and Minute in 12 hour date format from datetime column
Query 1:
select substring(convert(char(19),getdate(),100), 14, 6) as YearMonthDay
Output:
T-SQL: How to get Only Year Month and Day from datetime column
- Details
- Written by ranjeet
convert built-In conversion function has very rich number of code to convert datatime into different format.
Below code will give you Year, Month and Day from datetime column. To get complete list of code, please refer MSDN site.
Query to get only month and date together:
To get output in Year, Month and Day format
Query 1:
select substring(convert(char(10),getdate(),102), 1, 10) as YearMonthDay
Output:
T-SQL: How to get Only Month and Day from datetime column
- Details
- Written by ranjeet
convert built-In conversion function has very rich number of code to convert datatime into different format.
Code 101 fetch only month and day from datetime column
To get complete list of code, please refer MSDN site.
Query to get only month and day together:
Query 1:
select convert(char(5),getdate(),101) as YearMonth
select substring(convert(char(6),getdate(),101), 1, 5)as DayMonth
T-SQL: How to get Only Year and Month from datetime column
- Details
- Written by ranjeet
convert built-In conversion function has very rich number of code to convert datatime into different format.
Code 120 fetch only year from the datetime column
Code 100 fetch only month from datetime column
To get complete list of code, please refer MSDN site.
Query to get only year and month together:
T-SQL: DATABASEPROPERTYEX Built-In Function
- Details
- Written by ranjeet
DATABASEPROPERTYEX built-in function is another important one which give us complete list of property value on database level in one line command. Personally I always use this function. We can get almost all the property’s value by using this function but I am going to list limited one which is actually being used in day to day activities. Below is the list of property which I usually execute. You can refer MSDN to get full list.
-->> DATABASEPROPERTYEX
SELECT DATABASEPROPERTYEX('DBName','Collation')as BuildClrVersion
SELECT DATABASEPROPERTYEX('DBName','Edition')as Edition
T-SQL: What is SQL Server Built-In-Functions
- Details
- Written by ranjeet
SQL Server provide us many built in functions which can used to get the general or regular work done. We can define a function as a program which take some parameter and return us a value or set of values. We can use function in an expression.
SQL server has categorized its built in function in four different types:
-
Rowset Functions
-
Aggregate Functions
-
Ranking Functions
-
Scalar Functions