The year 2011 was a year of learning and opportunity for me. My recent book, SQL Server Interview Questions and Answers, have received such overwhelming love and support from all of you. While writing the book, I had two simple goals: (1) Master the Basics and (2) Ignite Learning. There was a constant request from the Community to take the learning of these books to the next level. Here is an article which discusses the Author’s Perspective. Let us see a puzzle on date functions.
Beyond Relational has come up with a very interesting concept – they have converted a few of the questions from my book into the SQL Quiz. The quiz is indeed focused on my two goals. In addition, it’s going to put the learning of the book to a higher stage. Looking at this novel concept, Vinod Kumar (Co-author) and I have decided to help every participant and reader by giving a few hints and suggestions to solve the quiz.
SQL Quiz
Using datetime functions inside SQL Server has always been a struggle even if there are multiple options. There have been interesting usages of GETDATE, but appealing functions like SYSDATETIME(), GETUTCDATE() and SYSYTCDATETIME() functions were also introduced. When would you use these functions and what are their differences?
Notes of Vinod Kumar
GETDATE() from the SQL Server 2000 days has been a common function which developers started to utilize. To answer the question, read the hint here. The changes on usage are fascinating as the precision levels and storage needs of these can be altered drastically. Can you name some of these changes?
Notes of Pinal Dave
I remember an old story told to me when I was just a beginning DBA. I had set up a job which was supposed to run at exact 12 AM (midnight). After a few runs, we realized that the job was running always 30 seconds earlier than the desired time. Every single day, at precisely 30 seconds before 12 AM, it would start to run. After a while, we had to create a complete investigation team on this subject. A senior DBA eventually figured it out – it was because we had used SMALLDATETIME datatype for a variable. This variable was storing the current datetime, and as precision of SMALLDATETIME is in terms of minutes, we had to face that issue. Our IT manager finally made all of us to go through every single datatype available in the SQL Server and get familiarized with all of them. I learned the lesson very well so now I want to share this experience with everybody.
Additional Notes
SQL Server Interview Questions and Answers ISBN:Â 1466405643Â Page#133
Difference Between DATETIME and DATETIME2
Difference Between GETDATE and SYSDATETIME
Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime
Datetime Function TODATETIMEOFFSET Example
Information Related to DATETIME and DATETIME2
Datetime Function SWITCHOFFSET Example
Difference Between DATETIME and DATETIME2 – WITH GETDATE
DATE and TIME in SQL Server 2008
Note: SQL Quiz, winners and prizes are administrated by Beyond Relational. The goal of this blog post is to provide additional learning pointers only.
Reference: Pinal Dave (https://blog.sqlauthority.com)
