Why sending e-mail with sp_send_dbmail is a bad idea

Sending e-mail with sp_send_dbmail. Should you or should you not. Well as with everything, it will depend on your requirements on your circumstances. Recently, I have been wondering the exact same thing. In my case it was more in the line of application specific emails.

sp_send_dbmail usage scenario:

Our developers are sending e-mail with sp_send_dbmail for various in-application tasks and events. You know, password reset emails, event notification emails, that kind of thing. To me, as a DBA, this always felt like a dirty solution. I took it upon myself to investigate as to whether or not sending e-mail with sp_send_dbmail is a good idea. To my surprise, I could not find any discussions or debates regarding the best practices for sending e-mail with sp_send_dbmail. I spoke to a couple of fellow SQL experts and I finally figured it out. Everyone has there own opinion. This does not really help. Eventually I turned to the good folks of stack exchange. Here, I got some interesting responses as well a some key point which is simply not debatable, and not in the favor of sp_send_dbmail either.

Problems with sending e-mail with sp_send_dbmail

  • Sending SMTP email with sp_send_dbmail will cause your msdb to grow. This may or may not be a problem depending on how many e-mails you are planning to send out. Plan for your volumes in the future. If you only plan for your current volume requirement you will soon find yourself back at the drawing board.
  • You run the risk of damaging the deliver ability and reputation of your IP/domain in regards to spam or blacklisting.
  • SQL server core licenses are expensive. When half of your expensive server cores are used to send email, this can be a potential waste of expensive resources.
  • If you couple the application features too closely with the database, it makes scaling difficult and becomes a nightmare if anything needs to be moved. Different services should run on different servers.
  • The server will require internet access. ALWAYS.
  • SMTP is a common security vulnerability often targeted by hackers and will have to be enabled on the SQL server.
  • Garbage data could be sent to the SQL Server as it will be an SMTP server as well. SQL server may not be able to handle this load. Especially not 1 TB of garbage data.

What now?

I hear you shouting from behind your screen. “I use sp_send_dbmail to send administration and notification messages from my SQL server!”. This is fine. These types of emails do not take up a lot of space and they will not consume large amounts of resources to send out”. I, however, have never liked sending administration emails using sp_send_dbmail for the following reasons:

  • You will have to enable SMTP on the server which is a potential security risk.
  • You will have to connect the server to the internet and keep it that way. This is also a potential security risk.

What I do to prevent sending email with sp_send_dbmail

Let’s be honest, it’s not always avoidable and I have sp_send_dbmail enabled on some of my servers due to the problem mentioned above. However, when it comes to maintenance, I like to keep my system on a central server which sends instructions to my other servers like backup databases, rebuild indexes, etc. I will collect the data and store it on the central server and send out notifications based on the data that I collected i.e. disk space usage and so forth. Third party applications monitor the performance of the SQL Servers and will send out notifications based on the data it collects. In the end, there is no sending email with sp_send_dbmail and no need to enable SMTP, or sp_send_dbmail for that matter, on a production server.

Conclusion

In the end it will be for you to decide whether it is necessary to enable sp_send_dbmail on your SQL server as it is not always avoidable, even though there are risks involved. Whether there is a right or wrong answer is debatable and as mentioned, every SQL expert has his or her own opinions regarding the matter. When deciding though, keep the above points as to why it could be a bad idea in mind and make an informed decision based on the information at hand.

,

Search Engine Optimization: Sorting out the meta data

A few days ago I started applying the techniques which I learned in the Digital Marketing course from Lowercase Academy. This week I will be sorting out my websites meta data on the search engine optimization side of thins. First, here is an overview of what I did last week in the Search Engine Optimization: Getting my website ready post:

  • Sorting out the links and navigation following the best practices taught in the course.
  • Working on the URL’s.
  • Working on the sitemaps and images.

After applying the changes, I gave it a couple of days and checked the rankings of my website. The rankings by keyword as they currently stand are as follows:

Current Page Rank


0 / 10

Current position on Google


KeywordRank on Google
JJ Prinsloo17
SQL Expert132

 

Next I will be continuing the optimization of my website by performing the following changes:

  • I will be installing an SEO WordPress plugin which the course recommends.
  • I will be optimizing my website by adding some additional information on my pages and posts which can be used by search engines to better index the website.
  • I will be signing up for a 30 day trial to an online SEO platform which should help me to further analyze and optimize my website. Unfortunately I will not be signing up to the one that the course recommends as it asks for credit card details which I am not willing to supply when signing up for a trial. I have only heard good ratings about the one I am signing up to though.

On a closer analysis it does not really seem like my websites rankings have changed all that much after the first round of changes which is somewhat expected. The big changes are only being made in the next couple of days although I have no doubt that the changes I already did make will certainly contribute to the effort.

The damage you do when shrinking SQL Server database files

I have heard of, seen, investigated, and debated about so many problems relating to database sizes, disk space, and shrinking SQL Server database files. It always puts a question mark on my face when listening to so many people offering bad advice and/or solutions to these problems.

“Just shrink the file”. Seems to be the general consensus here. DON’T!! I understand the urge. You might be a new DBA and want to show you can solve the problem as soon as possible. The problem is that you may be creating additional problems in the background that you are not aware of.

The problem with shrinking your files is that you are only solving the immediate problem on the surface whilst creating additional problems deeper down. Shrinking your files causes SQL server to move data around within the physical files. This in turn causes extreme index fragmentation. Alright, so we can just rebuild the indexes right? Well in theory yes, and this will sort out the fragmentation issue. The problem now is that your database size has grown again and you are back to square one. This turns into a vicious cycle.

So then what should I do??

As with everything SQL this will depend on your requirement and SLA. A good practice in any event will be to consider the following options:

  1. Determine where the size issue is on your database. Is it within your data files or the transaction log?
  2. If the space issue is within your data files, the first thing to consider is leaving it. The purpose of your drive is to store files. There is no point in carrying around an empty backpack. Whether the free space is on the drive itself or within the data file it does not matter. You have ample free space available for the database to do what it needs to.
  3. If the transaction log is growing uncontrollably and not shrinking, it is most likely that you are using a full recovery model on your database. If this is the case and your transaction log is not shrinking, then you are not taking proper log backups of your transaction log. Review your backup policy and SLA agreements of the business which should point out one of the following options:
    1. You might find that it is not necessary to perform in point restores on a database. If this is the case, consider switching the database recovery model to simple instead of full. Just keep in mind that you will not be able to recover to a specific point in time but only to the time of the last full backup.
    2. If it is necessary to restore to a specific point in time you will have to design a backup solution that fits these needs. Part of your solution should include taking backups of the transaction log which will truncate the log automatically and free up unused space. Unfortunately, full backups won’t do this because of the backup chain.
  4. Review your auto growth settings. Are you using the default 10%? If so, consider changing this to a decent amount of bytes rather than percentage. 10% of a 1TB file = 100GB. Wowza!
  5. Get more disk space. This is not something to be considered lightly as by working through the previous few steps you have more than likely came across one or two things that need to be optimized or changed. More disk space should be the last item on this list as there will be a direct cost to the business which might have been avoided. In any event though, this is sometimes necessary.

How do I prevent this in the first place?

A good practice is to plan for your data usage requirements as it will be in the future and not for what your requirement is right now. This will save you a lot of headache. If you size your databases to cater for the amount of data you will have in a years’ time, you’ll be grand.

Work on a proper backup solution from day one. Review the SLA and business requirements and set your database recover models to match the SLA’s. Design your backup solutions around this and if you go with full recovery, take regular transaction log backups which in turn will keep your transaction log file sizes at bay.

Once again, make sure your auto growth setting is set to a reasonable amount of fixed bytes rather than a percentage. Percentage settings on these can quickly become a handful to deal with.

Great, I still need to shrink my files for other reasons.

I admit that sometimes you cannot avoid the shrinking of a data file. You may have deleted a huge chunk of data after archiving it and the database is not expected to grow which makes it necessary to shrink the files. After all, that space can be used for the data files of other databases.

If this is the requirement, consider following the following alternative approach:

  1. Backup your database (ALWAYS BACKUP your database)
  2. Create a new filegroup
  3. Move all the affected tables and indexes to the newly created filegroup. This process will also remove any fragmentation automatically
  4. Delete the previous file group where you moved the tables and indexes from. In the case where this is the PRIMARY filegroup, shrink it way down.

To recap

Make sure your settings on your database is correct, that your backup policies and procedures are in place, and that these agree with your SLA.

Make sure you plan ahead regarding the required storage space for your data.

NEVER shrink your data files, especially not as part of your automatic maintenance procedures (It sounds harsh and extreme but as a DBA, this is how you should think about it)

If it’s not a problem, leave it! As mentioned before, your disk is there to be used and there is no difference between free space on the disk and free space in your data file.

Take the time to understand the road ahead and plan for what is coming. It could become a slippery slope.

,

Raspberry Pi Christmas

‘Tis December 20th, 2016. The season of joy and all things magical are upon us. On this day I didn’t have much to do for the first time in a long time. I ended up on my sofa, mobile in hand, mindlessly scrolling through Facebook as we all do. I decided to have a look at what was happening in the land of Pi. I went over to The Raspberry Pi Foundations official Facebook page. Let’s be honest, it would be a Christmas miracle if I came across an announcement of a new Raspberry Pi. In the end though, no such luck. Pretty soon however, this year changed to a Raspberry Pi Christmas.

While I was browsing the Facebook page I noticed there were quite a lot of Raspberry Pi related questions and nobody was answering. I took it upon myself to help some of the people out with my experiences relating to their questions. The next day, Alex J’rassic hit me up on Twitter thanking me for my help on their Facebook page and that I should drop her an email. This is what I did. Alex informed the that she and the Foundation would like to send me some goodies to say thank you for my help. “Tis the season of giving after all” she said.

I’m not going to bore you with all the things that happened in between regarding the delivery but, secretly I was really hoping there would be a Raspberry Pi Zero in the package as I have been struggling for more than a year to get one in South Africa. Just have a look at what was delivered to me today:

The goodie bag included:

  • Raspberry Pi 3
  • Sense HAT
  • Raspberry Pi Zero
  • Camera
  • IR Camera
  • Case
  • Various connectors for the Raspberry Pi Zero
  • Camera Ribbon Cable
  • A t-shirt which I will wear with pride
  • Carry bag
  • Stickers
  • A hand written note from Alex Bate herself

I would really like to thank Alex Bate and the Raspberry Pi Foundation for this huge gift. You guys are awesome! Really. I will definitely be using these in my projects and I will let you know what I make.

I hope you all had a Merry Christmas! I know I did.

,

Featured: Material Two Steps Login Android library

Developing mobile apps which are aesthetically pleasing to the eye can sometimes be difficult. It becomes even more so when you have a case of the infamous creative block syndrome which is never fun to diagnose yourself with. Luckily, we have guys like Unipiazza, the developer of the Material Two Steps Login Android library which makes it a little bit easier with the great work they do.

Today’s featured library is the Material Two Steps Login library developed by Unipiazza. Material Two Steps Login is a beautiful Android library which you can import into your project and wire up to handle the login functionality of your application. The library can easily be styled to fit in with your design and works great. The only downside to the library in its current version is that the styling capabilities are very basic. If you would like to implement more advanced styling such as custom fonts, you will be better off to clone the library and implement it in your project rather than just including the bas URL in your build.gradle.

 

jj prinsloo unipiazzi two steps login preview 1
jj prinsloo unipiazzi two steps login preview 2

 

To use the library, all you need to do is add the dependency to your build.gradle file:

compile 'com.unipiazza.materialtwostepslogin:materialtwostepslogin:0.1.4'

After adding the dependency, create a new activity and add the Material Two Steps Login form in the activity with the following XML:

 <com.unipiazza.material2stepslogin.view.MaterialTwoStepsLogin
    android:id="@+id/login_view"
    android:layout_width="match_parent"
    android:layout_height="wrap_content" >

From here you will be able to customize the activity in the activity code as you see fit:

MaterialTwoStepsLogin login_view = (MaterialTwoStepsLogin) findViewById(R.id.login_view);

   //REQUIRED
    login_view.setListener(this);
    login_view.setActivity(this);
    login_view.setFirst_step_background_color(getResources().getColor(R.color.colorPrimary));
    login_view.setSecond_step_background_color(Color.WHITE);
    login_view.setLogo(R.mipmap.ic_launcher);
    login_view.setDescription(R.string.insert_email_login);

    //OPTIONAL
    //TEXTS
    login_view.setRegister_description(R.string.not_registered_login);
    login_view.setRegister_text(R.string.registrati);
    //login_view.setPassforget_description_text();
    //login_view.setButton_passforget_text(Color.WHITE);
    //login_view.setButton_login_text();

    //REGISTER BUTTON background
    //login_view.setRegister_background(R.drawable.rounded_white_stroke_button);
    login_view.setButton_register_text_color(Color.WHITE);

    //EDITTEXT BACKGROUNDS AND COLOR
    //login_view.setEdittext_password_background();
    //login_view.setEdittext_email_background();
    //login_view.setEmail_text_color(Color.BLACK);
    //login_view.setEdittext_password_text_color(Color.BLACK);

    //BUTTON BACKGROUNDS
    //login_view.setButton_login_background();
    //login_view.setButton_next_background();

    //BUTTON TEXT COLOR
    login_view.setButton_next_text_color(Color.WHITE);
    login_view.setButton_login_text_color(getResources().getColor(R.color.colorPrimary));
    //login_view.setButton_passforget_text_color(Color.WHITE);
    //login_view.setPassforget_description_text_color(Color.WHITE);

    //TEXTVIEW TEXT COLOR
    //login_view.setDescription_text_color(Color.BLACK);
    //login_view.setRegister_description_text_color(Color.WHITE);
    //login_view.setName_text_color(Color.BLACK);
    //login_view.setEmail_secontstep_text_color(Color.BLACK);
    login_view.setPassforget_description_text_color(Color.BLACK);

Thanks Unipiazza, You really outshine on this one!

Be sure to check out Unipiazza’s Github page for more awesome libraries.

,

Search Engine Optimization: Getting my website ready

Recently, I completed the Digital Marketing course by Lowercase Academy which focused around digital marketing and search engine optimization. I don’t want to get into detail about how I felt about the course yet as I will be doing an in depth review at a later stage. What I can say, though, is that I definitely learned a few things from this course which definitely sparked my interest in one way or another.

The time has now come where I will start to apply the techniques that were taught in the course to my own websites, test them out and see the results.

In today’s article I will be discussing the first part of what the course teaches which I dubbed “Getting your house in order” meaning to sort out the basics on your website which might affect your websites rankings.

Note

Please note that this course is a paid service and I will not be going into any details regarding what it is that I have learned from this course or explicitly how to apply the techniques to your own website. Doing so will be highly immoral and unfair to the creator of the content and plainly I will just be a jerk if I do so. This series of articles which is based on this course, is simply for you to follow along. What we are trying to accomplish is to apply the techniques and see what the results are and how they affect our sites rankings. In short, apart from a high level scope of what we are doing, there won’t be any details.

As mentioned before, today’s scope is to get our websites basics right before we continue with any funky SEO implementations.

Firstly, I would like to mention that this website was built in WordPress and I will be applying all the techniques which I learned from the course to this website.

Some of the tasks I will be doing on my site:

  • Sorting out the links and navigation following the best practices taught in the course
  • Working on the URL’s
  • Working on the sitemaps and images

I will be working on these changes in the next day to get them sorted out. Next week we will check the page ranking again. Sign up for my newsletter to follow along with this series and see how these changes affect my website.

In future articles I will be adding the current website status on the search engines at the top of the articles in the following way:

Current Page Rank


0 / 10

Current position on Google


KeywordRank on Google
JJ Prinsloo26
SQL Expert132

 

 

Digital Marketing Course

Some of the most frequently asked questions by companies and/or website owners and entrepreneurs revolve around digital marketing.

  • How do I get my website visible on the internet?
  • How do I get on that first page of Google?
  • How do I reach my audience to build up a network who I can eventually sell my product to?

These question are all too common in the online business and entrepreneur space and most of the time we don’t have a huge marketing budget to pay for Google and Facebook ads or run massive online marketing campaigns etc. The question that this raises is; do you need a huge budget like those enterprise companies that run marketing campaigns online or can you get a piece of the action without it? It comes as no surprise that the internet is a deep and dark space where we have to fight for individuality and to stand out from the crowd. With the millions of websites out there, how will you get noticed?

I have had this conversation many a times with business owners, entrepreneurs and individuals alike. It always baffles me that the general understanding that these people have regarding the subject is that “If I get a website, I will get more business” and that is the end of it. This couldn’t be further from the truth and if you think otherwise, you might as well give up now and not waste your time or money. Merely popping a website on the internet will not make the masses run towards your business. Your website will be consumed into the masses faster than Google Chrome consumes memory. A website takes hard work, motivation and dedication.

I have been lucky enough to be invited as a study buddy to a digital marketing course offered by Lowercase Academy. You can download the brochure here to see what it is all about.

The course is led by Barry le Grange, a South African digital marketer with more than 10 years experience in the field. According to the Lowercase Academy website, Barry has worked with some of the largest corporate companies in South Africa doing digital marketing and building online businesses using the techniques that he teaches in the course.

In the next few weeks I will be doing the online course which entails the following modules:

LevelModuleSession
Level 1IntroductionThe Lowercase Academey Digital Marketing Training Program (10 Mins)
Level 1Building a professional website and blogHow to build a website and blog in WordPress (6 Mins)
Level 1Building a professional website and blogThe WordPress Dashboard (5 Mins)
Level 1Building a professional website and blogCreating web pages, blog posts, and menus in WordPress (12 Mins)
Level 1Building a professional website and blogWorking with WordPress Themes (16 Mins)
Level 1Building a professional website and blogWordPress Settings, Widgets, and Plugins (14 Mins)
Level 1Search Engine OptimizationHow Search Engines Work (8 Mins)
Level 1Search Engine OptimizationSearch Engine Optimization – Part 1 (6 Mins)
Level 1Search Engine OptimizationSearch Engine Optimization – Part 2 (14 Mins)
Level 1Search Engine OptimizationSearch Engine Optimization – Part 3 (8 Mins)
Level 1Search Engine OptimizationWhat Google Search will look like in 5 years (9 Mins)
Level 1Google AnalyticsGoogle Analytics – Part 1 (4:30 Mins)
Level 1Google AnalyticsGoogle Analytics Terminologies – Part 1 (7:30 Mins)
Level 1Google AnalyticsGoogle Analytics Terminologies – Part 2 (8 Mins)
Level 1BloggingContent Creation and Blogging (12 Mins)
Level 1BloggingHow to build an audience (11 Mins)
Level 2Branding, Design & User ExperienceBranding and Design (8 Mins)
Level 2Branding, Design & User ExperienceUser Experience Design (UX) (10 Mins)
Level 2Social Media for BusinessSocial Media for Business – Introduction (6 Mins)
Level 2Social Media for BusinessInsights: Social Media Platforms (18 Mins)
Level 2Online AdvertisingIntroduction to Online Advertising (6 Mins)
Level 2Online AdvertisingFacebook Advertising (10 Mins)
Level 2Online AdvertisingFacebook Advertising – Measuring and Optimizing (11 Mins)
Level 2Landing Page OptimizationLanding Page Optimization (11 Mins)
Level 2Email MarketingModern Email Marketing (15 Mins)
Level 2Payment GatewaysPayment Gateways and Membership Sites (8 Mins)
Level 2Overview and ConclusionOverview and Conclusion (6 Mins)

I will also be starting a project to see how the techniques and skills I learn in this course can affect my own website in terms of design, marketing and SEO. I will be applying every technique I learn to the site and at the end, see where I end up on Googles rankings, how many followers I have gained and the results in my google analytics. I will be writing follow up articles every step of the way about what I did and what the effects were, so register for my newsletter and follow along.

When all is done I will be writing a review on Lowercase Academy’s Digital Marketing Course and what I learnt and what the benefits were to me as a website and online business owner.

Let’s market some digital!

SQL Server Date Formats

One of the most frequently searched terms regarding SQL server is SQL Server Date Formats and codes as well as how to format a date for a specific requirement or standard. Although it is one of the first things you learn when working with SQL server, it it is not always as easy to remember all the format codes which is to be used as part of the CONVERT function to accomplish the desired result.

Here is a complete reference to the date formats that come standard with SQL Server. Following the standard format list is a list of extended formats that are often required. Note that these extended formats are not returned as a DATETIME data type but rather a VARCHAR data type so you will have to perform all your date calculations and modifications before converting it to an extended format. Happy dating!

Standard SQL Date Formats

Date FormatStandardSQL StatementSample Output
Mon DD YYYY HH:MIAM (or PM)DefaultSELECT CONVERT(VARCHAR(20), GETDATE(), 100)Jan 1 2005 1:29PM
MM/DD/YYUSASELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]11/23/98
MM/DD/YYYYUSASELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]11/23/1998
YY.MM.DDANSISELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]72.01.01
YYYY.MM.DDANSISELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]1972.01.01
DD/MM/YYBritish/FrenchSELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]19/02/72
DD/MM/YYYYBritish/FrenchSELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]19/02/1972
DD.MM.YYGermanSELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]25.12.05
DD.MM.YYYYGermanSELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]25.12.2005
DD-MM-YYItalianSELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]24-01-98
DD-MM-YYYYItalianSELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]24-01-1998
DD Mon YYSELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]04 Jul 06
DD Mon YYYYSELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]04 Jul 2006
Mon DD, YYSELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]Jan 24, 98
Mon DD, YYYYSELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]Jan 24, 1998
HH:MM:SSSELECT CONVERT(VARCHAR(8), GETDATE(), 108)03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM)Default + millisecondsSELECT CONVERT(VARCHAR(26), GETDATE(), 109)Apr 28 2006 12:32:29:253PM
MM-DD-YYUSASELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]01-01-06
MM-DD-YYYYUSASELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]01-01-2006
YY/MM/DDSELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]98/11/23
YYYY/MM/DDSELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]1998/11/23
YYMMDDISOSELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]980124
YYYYMMDDISOSELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]19980124
DD Mon YYYY HH:MM:SS:MMM(24h)Europe default + millisecondsSELECT CONVERT(VARCHAR(24), GETDATE(), 113)28 Apr 2006 00:34:55:190
HH:MI:SS:MMM(24H)SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)ODBC CanonicalSELECT CONVERT(VARCHAR(19), GETDATE(), 120)1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)ODBC Canonical (with milliseconds)SELECT CONVERT(VARCHAR(23), GETDATE(), 121)1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMMISO8601SELECT CONVERT(VARCHAR(23), GETDATE(), 126)1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAMKuwaitiSELECT CONVERT(VARCHAR(26), GETDATE(), 130)28 Apr 2006 12:39:32:429AM
DD/MM/YYYY HH:MI:SS:MMMAMKuwaitiSELECT CONVERT(VARCHAR(25), GETDATE(), 131)28/04/2006 12:39:32:429AM

 

Extended Date Formats

Date FormatSQL StatementSample Output
YY-MM-DDSELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), ‘/’, ‘-‘) AS [YY-MM-DD]99-01-24
YYYY-MM-DDSELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), ‘/’, ‘-‘) AS [YYYY-MM-DD]1999-01-24
MM/YYSELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]08/99
MM/YYYYSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]12/2005
YY/MMSELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]99/08
YYYY/MMSELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]2005/12
Month DD, YYYYSELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]July 04, 2006
Mon YYYYSELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]Apr 2006
Month YYYYSELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]February 2006
DD MonthSELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) AS [DD Month]11 September
Month DDSELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]September 11
DD Month YYSELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]19 February 72
DD Month YYYYSELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]11 September 2002
MM-YYSELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]12/92
MM-YYYYSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]05-2006
YY-MMSELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]92/12
YYYY-MMSELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]2006-05
MMDDYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), ‘/’, ”) AS [MMDDYY]122506
MMDDYYYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), ‘/’, ”) AS [MMDDYYYY]12252006
DDMMYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), ‘/’, ”) AS [DDMMYY]240702
DDMMYYYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), ‘/’, ”) AS [DDMMYYYY]24072002
Mon-YYSELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) AS [Mon-YY]Sep-02
Mon-YYYYSELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) AS [Mon-YYYY]Sep-2002
DD-Mon-YY SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) AS [DD-Mon-YY]25-Dec-05
DD-Mon-YYYYSELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) AS [DD-Mon-YYYY]25-Dec-2005