Tuesday, September 30, 2014

56 T-SQL Functions

This article demo some of the common T-SQL Functions.  Purple are new to SQL 2012 which will be cover in here.

  • NULL Functions
    • ISNULL
    • COALESCE
  • Date/Time Functions
    • GETDATE()
    • GETUTCDATE()
    • DATEPART()
    • DATEDIFF()
    • DATEADD()
    • ISDATE()
    • DATEROMPARTS()
    • TIMEFORPARTS()
    • EOMONTH()
    • PARSE()
  • String Functions
    • CHARINDEX()
    • PATINDEX()
    • LEFT()
    • RIGHT()
    • LTRIM()
    • RTRIM()
    • LEN()
    • CONCAT()
    • FORMAT()
  • Data Type Conversions
    • CONVERT()
    • CAST()
    • TRY_PARSE()
    • TRY_CONVERT()
  • Logical Functions
    • CHOOSE()
    • IIF()

NULL FUNCTIONs

Please see here.

Date/Time Functions

Use [AdventureWorks2014]
GO
-- DATE Related functions
SELECT GETDATE()
GO
SELECT GETUTCDATE()
GO
SELECT 
    DATEPART(year, GETDATE() ) AS 'YEAR',
    DATEPART(month,  GETDATE()) AS 'MONTH',
    DATEPART(day,  GETDATE()) AS 'DAY',
    DATEPART(dayofyear,  GETDATE()) 'DAT OF THE YEAR',
    DATEPART(weekday,  GETDATE()) AS 'WEEK DAY'
GO
 
SELECT DATEDIFF(day, '2010/6/11', GETDATE());
GO
SELECT DATEADD(month, 1, GETDATE());
GO
IF ISDATE('2009-05-12 10:19:41.177') = 1
    PRINT 'VALID'
ELSE
    PRINT 'INVALID';
GO
IF ISDATE('I AM STRING') = 1
    PRINT 'VALID'
ELSE
    PRINT 'INVALID';

image

ISDATE() is used to validate whether input string is valid date or not.

image

String Functions

  • CHARINDEX(): Returns the starting position of the first occurrence of a string in a specified expression, or zeros if the string is not found, on all valid text and character data types.
  • PATINDEX(): Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
  • LEFT(): Returns the left part of a character string with the specified number of characters.
  • RIGHT(): Returns the right part of a character string with the specified number of characters.
  • LTRIM(): Remove the leading blank
  • RTRIM(): Remove the blank in the end of string
  • LEN(): show the length of the sting
DECLARE @document varchar(64);
SELECT @document = 'Happy Birthday everyone.';
SELECT CHARINDEX('Birthday', @document);
SELECT PATINDEX('%Bir%', @document);
SELECT LEFT(@document, 6)
SELECT RIGHT(@document, 6)
SELECT LTRIM(@document)
SELECT RTRIM(@document)
SELECT LEN(@document)
GO

image

Data Type Conversions

  • CONVERT()
  • CAST()
  • PARSE()
SELECT CONVERT(datetime2,'12/31/2010') AS Result;
GO
SELECT CAST('12/31/2010' AS datetime2) AS Result;
GO
SELECT PARSE('12/31/2010' AS datetime2 USING 'en-US') AS Result;
GO
image

For CONVERT, CAST and PARSE, if converting data type fail, it will generate the error message. In SQL 2012, new functions TRY_CONVERT(), TRY_PARSE() has been introduce, if convention fail, it will return NULL .

56 comments:

  1. Great blog created by you. I read your blog, its best and useful information. You have done a great work. Super blogging and keep it up.php jobs in hyderabad.

    ReplyDelete
  2. I was surfing the Internet for information and came across your blog. I am impressed by the information you have on this blog. It shows how well you understand this subject. campingplatz holland mit hund

    ReplyDelete
  3. Such a very useful article. Very interesting to read this article. I would like to thank you for the efforts you had made for writing this awesome article.
    Data Science Course in Pune
    Data Science Training in Pune

    ReplyDelete
  4. Nice blog. I finally found great post here Very interesting to read this article and very pleased to find this site. Great work!
    Data Science Training in Pune
    Data Science Course in Pune

    ReplyDelete
  5. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it.
    Data Analytics Course in Pune
    Data Analytics Training in Pune

    ReplyDelete
  6. Thumbs up guys your doing a really good job. It is the intent to provide valuable information and best practices, including an understanding of the regulatory process.
    Cyber Security Course in Bangalore

    ReplyDelete
  7. Very nice blog and articles. I am really very happy to visit your blog. Now I am found which I actually want. I check your blog everyday and try to learn something from your blog. Thank you and waiting for your new post.
    Cyber Security Training in Bangalore

    ReplyDelete
  8. I am impressed by the information that you have on this blog. It shows how well you understand this subject.
    Best Data Science Courses in Bangalore

    ReplyDelete
  9. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
    Ethical Hacking Course in Bangalore

    ReplyDelete
  10. Wow! Such an amazing and helpful post this is. I really really love it. I hope that you continue to do your work like this in the future also.
    Ethical Hacking Training in Bangalore

    ReplyDelete
  11. I am impressed by the information that you have on this blog. Thanks for Sharing
    Ethical Hacking in Bangalore

    ReplyDelete
  12. Here at this site really the fastidious material collection so that everybody can enjoy a lot.

    Data Science Course

    ReplyDelete
  13. Your work is very good and I appreciate you and hopping for some more informative posts.

    Data Science Training

    ReplyDelete
  14. Terrific post thoroughly enjoyed reading the blog and more over found to be the tremendous one. In fact, educating the participants with it's amazing content. Hope you share the similar content consecutively.

    artificial intelligence course in bhilai

    ReplyDelete
  15. Speaking honestly this blog is absolutely amazing in learning the subject that is building up the knowledge of every individual and enlarging to develop the skills which can be applied in to practical one. Finally, thanking the blogger to launch more further too.

    Data Science training in Bhilai

    ReplyDelete
  16. Stupendous blog huge applause to the blogger and hoping you to come up with such an extraordinary content in future. Surely, this post will inspire many aspirants who are very keen in gaining the knowledge. Expecting many more contents with lot more curiosity further.

    Digital Marketing Course in Bhilai

    ReplyDelete
  17. This website and I conceive this internet site is really informative ! Keep on putting up Water Softener System

    ReplyDelete
  18. Impressive blog to be honest definitely this post will inspire many more upcoming aspirants. Eventually, this makes the participants to experience and innovate themselves through knowledge wise by visiting this kind of a blog. Once again excellent job keep inspiring with your cool stuff.

    Digital Marketing training

    ReplyDelete
  19. Stupendous blog huge applause to the blogger and hoping you to come up with such an extraordinary content in future. Surely, this post will inspire many aspirants who are very keen in gaining the knowledge. Expecting many more contents with lot more curiosity further.

    Data Science training

    ReplyDelete
  20. Truly mind blowing blog went amazed with the subject they have developed the content. These kind of posts really helpful to gain the knowledge of unknown things which surely triggers to motivate and learn the new innovative contents. Hope you deliver the similar successive contents forthcoming as well.

    Digital Marketing training in Raipur

    ReplyDelete
  21. Nice post. I learn some thing tougher on different blogs everyday. Most commonly it is stimulating to read content off their writers and practice something at their store. I’d opt to use some with all the content in my weblog whether or not you do not mind. Natually I’ll provide a link with your internet weblog. Thank you for sharing 토토사이트

    ReplyDelete
  22. You completed a few fine points there. I did a search on the subject and found nearly all persons will go along with with your blog. 토토사이트

    ReplyDelete
  23. I really loved reading your blog. It was very well authored and easy to understand. Unlike other blogs I have read which are really not that good.Thanks alot! Sprin Jene

    ReplyDelete
  24. I truly like you're composing style, incredible data, thankyou for posting. windshield replacement san diego

    ReplyDelete
  25. Hey There. I found your blog using msn. This is a very well written article. I will be sure to bookmark it and return to read more of your useful information. Thanks for the post. I will definitely return. used motorcycles san diego

    ReplyDelete
  26. A decent blog dependably concocts new and energizing data and keeping in mind that understanding I have feel that this blog is truly have each one of those quality that qualify a blog to be a one. ktm motorcycles

    ReplyDelete
  27. Hey There. I found your blog using msn. This is a very well written article. I will be sure to bookmark it and return to read more of your useful information. Thanks for the post. I will definitely return. diseño grafico tijuana

    ReplyDelete
  28. Hey There. I found your blog using msn. This is a very well written article. I will be sure to bookmark it and return to read more of your useful information. Thanks for the post. I will definitely return. diseño grafico tijuana

    ReplyDelete
  29. I see the best substance on your blog and I incredibly love understanding them natural toothpaste without fluoride

    ReplyDelete
  30. You re in purpose of actuality a without flaw website admin. The site stacking speed is astonishing. It sort of feels that you're doing any unmistakable trap. Besides, The substance are perfect work of art. you have completed a fabulous movement regarding this matter real estate broker West Carleton

    ReplyDelete
  31. You re in purpose of actuality a without flaw website admin. The site stacking speed is astonishing. It sort of feels that you're doing any unmistakable trap. Besides, The substance are perfect work of art. you have completed a fabulous movement regarding this matter courtier immobilier West Carleton

    ReplyDelete
  32. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work. cybersécurité

    ReplyDelete
  33. Great post, and great website. Thanks for the information! schwank patio heaters

    ReplyDelete
  34. This is very useful, although it will be important to help simply click that web page link: avec un agent

    ReplyDelete
  35. Such a very useful article. Very interesting to read this article. I would like to thank you for the efforts you had made for writing this awesome article. yoga gatineau

    ReplyDelete
  36. "I am a normal visitor of your web site and appreciate you taking the time to maintain the excellent site. I will be a frequent visitor for a long time.

    " freelancer malta

    ReplyDelete
  37. This Post is providing valuable and unique information, I know that you take a time and effort to make a awesome article Bioclimatic Pergola Skylounge

    ReplyDelete
  38. Hello I am so delighted I located your blog, I really located you by mistake, while I was watching on google for something else, Anyways I am here now and could just like to say thank for a tremendous post and a all round entertaining website. Please do keep up the great work. shipping from china

    ReplyDelete
  39. The blog writings were so nice, I wished they neever ended. buy traffic

    ReplyDelete
  40. Great info! I recently came across your blog and have been reading along. corporate video production company in Baltimore

    ReplyDelete
  41. " '훌륭한 유용한 리소스를 무료로 제공하는 가격을 알 수있는 웹 사이트를 보는 것이 좋습니다. 귀하의 게시물을 읽는 것이 정말 마음에 들었습니다. 감사합니다! 훌륭한 읽기, 긍정적 인 사이트,이 게시물에 대한 정보를 어디서 얻었습니까? 지금 귀하의 웹 사이트에서 몇 가지 기사를 읽었으며 귀하의 스타일이 정말 마음에 듭니다. 백만명에게 감사하고 효과적인 작업을 계속하십시오. 출장

    ReplyDelete
  42. You have a good point here!I totally agree with what you have said!!Thanks for sharing your views...hope more people will read this article!! 메이저사이트

    ReplyDelete
  43. This Post is providing valuable and unique information; I know that you take a time and effort to make a awesome article 먹튀검증

    ReplyDelete
  44. Really wonderful blog completely enjoyed reading and learning to gain the vast knowledge. Eventually, this blog helps in developing certain skills which in turn helpful in implementing those skills. Thanking the blogger for delivering such a beautiful content and keep posting the contents in upcoming days.

    Data Science certification in Raipur

    ReplyDelete
  45. Truly incredible blog found to be very impressive due to which the learners who ever go through it will try to explore themselves with the content to develop the skills to an extreme level. Eventually, thanking the blogger to come up with such an phenomenal content. Hope you arrive with the similar content in future as well.

    Digital Marketing training in Raipur

    ReplyDelete
  46. Highly appreciable regarding the uniqueness of the content. This perhaps makes the readers feels excited to get stick to the subject. Certainly, the learners would thank the blogger to come up with the innovative content which keeps the readers to be up to date to stand by the competition. Once again nice blog keep it up and keep sharing the content as always.

    Data Science Course in Bhilai

    ReplyDelete
  47. Wonderful blog found to be very impressive to come across such an awesome blog. I should really appreciate the blogger for the efforts they have put in to develop such an amazing content for all the curious readers who are very keen of being updated across every corner. Ultimately, this is an awesome experience for the readers. Anyways, thanks a lot and keep sharing the content in future too.

    Digital Marketing training

    ReplyDelete
  48. Actually I read it yesterday I looked at most of your posts but I had some ideas about it . This article is probably where I got the most useful information for my research and today I wanted to read it again because it is so well written.
    Data Science Course in Bangalore

    ReplyDelete
  49. I got a web site from where I be capable of really obtain valuable information regarding my study and knowledge.
    Great Article… Good Job… Thanks For Sharing…

    Website:오피사이트

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates