Tuesday, September 30, 2014

83 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 .

83 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
  50. First of all, thank you for your post. Your posts are neatly organized with the information I want, so there are plenty of resources to reference. I bookmark this site and will find your posts frequently in the future. Thanks again ^^ 토토커뮤니티

    ReplyDelete
  51. I learned a lot from you.토토사이트 I have a hobby similar to yours.

    ReplyDelete
  52. Took me time to read all the comments, 토토사이트 but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained!

    ReplyDelete
  53. Your writing is perfect and complete. 온라인바둑이 However, I think it will be more wonderful if your post includes additional topics that I am thinking of. I have a lot of posts on my site similar to your topic. Would you like to visit once?

    ReplyDelete
  54. It was an awesome post to be sure. I completely delighted in understanding it in my noon. Will without a doubt come and visit this blog all the more frequently. A debt of gratitude is in order for sharing. 라이브바카라

    ReplyDelete
  55. ou need to be a part of a contest for one of the highest quality sites on the 토토사이트. I most certainly will recommend this site!

    ReplyDelete
  56. Hello, I'm happy to see some great articles on your site. Would you like to come to my site later? My site also has posts, comments and communities similar to yours. Please visit and take a look 토토사이트

    ReplyDelete
  57. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article :D 안전놀이터순위

    ReplyDelete
  58. I'm extremely impressed with your writing skills as well as with the layout on your blog. Is this a paid theme or did you customize it yourself? Anyway keep up the nice quality writing, it is rare to see a great blog like this one these days ..

    my web page; 오피

    ReplyDelete
  59. Nice work!I’ve read most of your articles and I love to read them at your site as you got best stuff, I have ever seen Admin We Like Dubai junk You always post amazing things like that.
    바카라사이트

    ReplyDelete
  60. The Gaming Club Casino also offers auction services to members, a sign-up bonus and cash
    gratuities available to players and club members alike.
    After the player receiving this bonus, these casinos
    offer other bonuses for future deposits. These
    players will be provided incentives for upcoming deposits that happen to be their casino accounts.
    Here is my web site 풀싸롱

    ReplyDelete
  61. I’m very pleased to discover this site. I want to to thank you for ones time for this particularly wonderful read!! I definitely savored every part of it and i also have you saved as a favorite to see new information on your blog. 사설놀이터

    ReplyDelete
  62. Awesome article! I want people to know just how good this information is in your article. It’s interesting, compelling content. Your views are much like my own concerning this subject 안전한놀이터 It touched me a lot. I would love to hear your opinion on my site. Please come to the site I run once and leave a comment. Thank you.

    ReplyDelete
  63. Positive site, where did u come up with the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work. 메이저놀이터추천

    ReplyDelete
  64. I have been looking for articles on these topics for a long time. Kèobóngđá I don't know how grateful you are for posting on this topic. Thank you for the numerous articles on this site, I will subscribe to those links in my bookmarks and visit them often. Have a nice day.

    ReplyDelete
  65. I figure this article can be enhanced a tad. There are a couple of things that are dangerous here, and if you somehow managed to change these things, this article could wind up a standout amongst your best ones. I have a few thoughts with respect to how you can change these things. 메이저사설놀이터

    ReplyDelete
  66. Amazing article..!! Thank you so much for this informative post. I found some interesting points and lots of information from your blog. Thanks 토토커뮤니티

    ReplyDelete
  67. Thank you for some other informative blog. Where else could I get that type of information written in such an ideal means? I have a mission that I’m just now working on, and I have been at the look out for such information. 안전놀이터 It helped me a lot. If you have time, I hope you come to my site and share your opinions. Have a nice day.

    ReplyDelete
  68. I wanted to thank you for this excellent read!! I definitely loved every little bit of it. I have you bookmarked your site to check out the new stuff you post. 먹튀검증 I am writing on this topic, so I think it will help a lot. I leave my blog address below. Please visit once.!

    ReplyDelete
  69. Of course, your article is good enough, but I thought it would be much better to see professional photos and videos together. There are articles and photos on these topics on my homepage, so please visit and share your opinions. keonhacai

    ReplyDelete
  70. Searching for a great site. I was so impressed to yours. This will probably give me ideas for my work. Thank you 메이저사이트

    ReplyDelete
  71. I am glad to read this post, it’s an impressive piece. I am always searching for quality posts and articles and this is what I found here, I hope you will be adding more in future. 경마

    ReplyDelete
  72. You were great and everyone received so much from your experience and knowledge
    Absolutely amazing, thank you for sharing your knowledge with me. 바카라
    온라인경마

    ReplyDelete
  73. That’s a great article! The neatly organized content is good to see. Can I quote a blog and write it on my blog? My blog has a variety of communities including these articles. Would you like to visit me later? 사설토토

    ReplyDelete
  74. Hi there, I simply hopped over in your website by way of StumbleUpon. Now not one thing I’d typically learn, but I favored your emotions none the less. Thank you for making something worth reading. 메이저사이트순위

    ReplyDelete
  75. The Gaming Club Casino also offers auction services to members, a sign-up bonus and cash
    gratuities available to players and club members alike.
    After the player receiving this bonus, these casinos
    offer other bonuses for future deposits. These
    players will be provided incentives for upcoming deposits that happen to be their casino accounts.
    click me here 샌즈카지노
    LG

    ReplyDelete
  76. This site seems to inspire me a lot. Thank you so much for organizing and providing this quality information in an easy to understand way. I think that a healthy era of big data can be maintained only when such high-quality information is continuously produced. And I, too, are working hard to organize and provide such high-quality information. It would be nice to come in once and get information.

    Also visit my site:파워볼


    ReplyDelete

 

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