- CONCAT function : CONCAT()
- Format function : FORMAT()
- Logical Functions : IIF & CHOOSE()
- Conversion functions: TRY_CONVERT(), TRY_PARSE()
- Date Functions: DATEFROMPARTS(), TIMEFORMPARTS(), EOMONTH(), PARSE()
CONCAT
CONCAT function will ignore the NULL value.
SELECT CONCAT ( 'Happy ', 'Birthday ', 'Skye', 9, '/', '24' ) AS Result;
Create table #temp
( name nvarchar(200) NOT NULL,
middle_name nvarchar(200) NULL,
last_name nvarchar(200) NOT NULL );
INSERT INTO #temp VALUES('Summer','Tiger','Chen');
INSERT INTO #temp VALUES('Sunny','Dragon','Chen');
INSERT INTO #temp VALUES('Skye',NULL,'Chen');
select CONCAT( name,middle_name,last_name) AS RESULT from #temp;
DROP table #temp;
FORMAT
DECLARE @d DATETIME = '01/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS Result;
SET @d = GETDATE();
SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS Result;
IIF & CHOOSE
IIF is the quicker way for CASE statement.
--- CHOOSE & IIF
SELECT CHOOSE ( 2, 'MD', 'ED', 'VP', 'Associate' ) AS Result;
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;
TRY_CONVERT
--- TRY_CONVERT
--- If convert fail, it will return NULL
SELECT
CASE WHEN TRY_CONVERT(float,'test') IS NULL
THEN 'Convert failed'
ELSE 'Convert succeeded'
END AS Result
GO
SET DATEFORMAT dmy;
SELECT TRY_CONVERT(datetime2,'12/31/2010') AS Result;
GO
SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2,'12/31/2010') AS Result;
GO
TRY_PARSE
SELECT
CASE WHEN TRY_PARSE('test' AS float) IS NULL
THEN 'Parse failed'
ELSE 'Parse succeeded'
END AS Result
GO
SELECT TRY_PARSE('12/31/2010' AS datetime2 USING 'en-US') AS Result;
GO
DATEFROMPARTS
DATEFROMPARTS use to format the date. If the input string is not the valid date. It will split the error.
Msg 289, Level 16, State 1, Line 46
Cannot construct data type date, some of the arguments have values which are not valid.
--- DATEFROMPARTS
SELECT DATEFROMPARTS ( 2014, 9, 31 ) AS Result;
GO
SELECT DATEFROMPARTS ( 2014, 9, 30 ) AS Result;
GO
TIMEFORMPARTS()
TIMEFORMPARTS() use to format the time. If the input string is not the valid time. It will split the error.
Msg 289, Level 16, State 2, Line 51
Cannot construct data type time, some of the arguments have values which are not valid.
--- TIMEFORMPARTS()
SELECT TIMEFROMPARTS ( 23, 61, 59, 0, 0 ) AS Result
GO
SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS Result
GO
EOMONTH()
EOMONTH() used to find the last day for the month .
--- EOMONTH() : last day for the month
select EOMONTH(GETDATE());
PARSE()
PARESE() can use to parse the time and currency.
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US') AS Result;
GO
SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'zh-TW') AS Result
GO
SELECT PARSE('€34,598' AS money USING 'de-DE') AS Result;
GO
SELECT PARSE('34,598' AS money USING 'zh-TW') AS Result;
I preview this blog. Most of the points are very interesting to read. its help me to study also. Thanks for your help.
ReplyDeletecore java training in chennai
core java training
core java Training in Porur
clinical sas course
Spring Training in Chennai
QTP Training in Chennai
Manual Testing Training in Chennai
IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes. IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble.Final Year Projects for CSE
DeleteSpring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining .
Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai
The Angular Training covers a wide range of topics including Angular Directives, Angular Services, and Angular programmability.Angular Training
Thanks for your efforts in sharing this post with us. This was really awesome. kindly keep continuing the great work.
ReplyDeleteSpoken English Classes in Chennai Anna Nagar
Spoken English Class in Porur
Spoken English Class in Vadapalani
Spoken English Class in Thiruvanmiyur
Spoken English Class in Chennai
Best English Speaking Classes in Mumbai
English Speaking Course in Mumbai
IELTS Training in Chennai
IELTS Coaching in Chennai
IELTS Mumbai
The article is so informative. This is more helpful for our
ReplyDeleteselenium training in chennai
selenium online courses best selenium online training
selenium testing training
selenium classes
Thanks for sharing.
This is the first & best article to make me satisfied by presenting good content. I feel so happy and delighted.By Learn Digital Marketing Course Training in Chennai it will help to get Digital Marketing Course Training in Chennai.Learn Best Digital Marketing Course Training in Chennai's professional institute to get reputed job.
ReplyDeleteThanks for sharing such a most informative blog.... Waiting for the new updates.
ReplyDeleteDigital Marketing Course In Kolkata
Web Design Course In Kolkata
SEO Course In Kolkata