SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function

Note: Tomorrow is February 29th. This blog post is dedicated to coming tomorrow – a special day :)

Subu: “How can I find leap year in using SQL Server 2012?

Pinal: “Are you asking me how to year 2012 is leap year using T-SQL – search online and you will find many example of the same.”

Subu: “No. I am asking – How can I find leap year in using SQL Server 2012?

Pinal: “Oh so you are asking – How can I find leap year in using SQL Server 2012?

Subu: “Yeah - How can I find leap year in using SQL Server 2012?

Pinal: “Let me do that for you – How can you find leap year in using SQL Server 2012?

Indeed a fun conversation. Honestly, only reason I pasted our conversation here is – it was fun. What he was asking is that how to do it using new functions introduced in SQL Server 2012. Here is the article I have written which introduces all the new functions in SQL Server 2012 Summary of All the Analytic Functions – MSDN and SQLAuthority and 14 New Functions – A Quick Guide.

There are many functions written to figure out to figure out if any year is Leap Year or not. The same I have written using T-SQL function over here.

CREATE FUNCTION dbo.IsLeapYear (@year INT)
RETURNS INT
AS
BEGIN
RETURN
(IIF(DATEPART(dd,(EOMONTH(CONCAT(@year,'0201')))) = 29,1,0))
END
GO

What I really like is that I was able to use three newly introduced function in SQL Server 2012 in above script. You can read more about them here. IIF, EOMONTH and CONCAT.

You can validate above query by running following script.

SELECT dbo.IsLeapYear('2011') 'IsLeapYear';
SELECT dbo.IsLeapYear('2012') 'IsLeapYear';
GO

You will get result 1 if the year is leap year and 0 if year is not leap year.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – DATEDIFF – Accuracy of Various Dateparts

I recently received the following question through email and I found it very interesting so I want to share it with you.

“Hi Pinal,

In SQL statement below the time difference between two given dates is 3 sec, but when checked in terms of Min it says 1 Min (whereas the actual min is 0.05Min)

SELECT DATEDIFF(MI,'2011-10-14 02:18:58' , '2011-10-14 02:19:01') AS MIN_DIFF


Is this is a BUG in SQL Server ?”

Answer is NO.

It is not a bug; it is a feature that works like that. Let us understand that in a bit more detail. When you instruct SQL Server to find the time difference in minutes, it just looks at the minute section only and completely ignores hour, second, millisecond, etc. So in terms of difference in minutes, it is indeed 1.

The following will also clear how DATEDIFF works:

SELECT DATEDIFF(YEAR,'2011-12-31 23:59:59' , '2012-01-01 00:00:00') AS YEAR_DIFF

The difference between the above dates is just 1 second, but in terms of year difference it shows 1.

If you want to have accuracy in seconds, you need to use a different approach. In the first example, the accurate method is to find the number of seconds first and then divide it by 60 to convert it to minutes.

SELECT DATEDIFF(second,'2011-10-14 02:18:58' , '2011-10-14 02:19:01')/60.0 AS MIN_DIFF

Even though the concept is very simple it is always a good idea to refresh it. Please share your related experience with me through your comments.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Information Related to DATETIME and DATETIME2

I recently received interesting comment on the blog regarding workaround to overcome the precision issue while dealing with DATETIME and DATETIME2.

I have written over this subject earlier over here.

SQL SERVER – Difference Between GETDATE and SYSDATETIME

SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE

SQL SERVER – Difference Between DATETIME and DATETIME2

SQL Expert Jing Sheng Zhong has left following comment:

The issue you found in SQL server new datetime type is related time source function precision. Folks have found the root reason of the problem – when data time values are converted (implicit or explicit) between different data type, which would lose some precision, so the result cannot match each other as thought. Here I would like to gave a work around solution to solve the problem which the developers met.

-- Declare and loop
DECLARE @Intveral INT, @CurDate DATETIMEOFFSET;
CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2, GlobalDate DATETIMEOFFSET)
SET @Intveral = 10000
WHILE (@Intveral > 0)
BEGIN
----SET @CurDate = SYSDATETIMEOFFSET(); -- higher precision for future use only
SET @CurDate = TODATETIMEOFFSET(GETDATE(),DATEDIFF(N,GETUTCDATE(),GETDATE())); -- lower precision to match exited date process
INSERT #TimeTable (FirstDate, LastDate, GlobalDate)
VALUES (@CurDate, @CurDate, @CurDate)
SET @Intveral = @Intveral - 1
END
GO
-- Distinct Values
SELECT COUNT(DISTINCT FirstDate) D_DATETIME,
COUNT(DISTINCT LastDate) D_DATETIME2,
COUNT(DISTINCT GlobalDate) D_SYSGETDATE
FROM #TimeTable
GO
-- Join
SELECT DISTINCT a.FirstDate,b.LastDate, b.GlobalDate, CAST(b.GlobalDate AS DATETIME) GlobalDateASDateTime
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate = CAST(b.GlobalDate AS DATETIME)
GO
-- Select
SELECT *
FROM #TimeTable
GO
-- Clean up
DROP TABLE #TimeTable
GO

If you read my blog SQL SERVER – Difference Between DATETIME and DATETIME2 you will notice that I have achieved the same using GETDATE().

Are you using DATETIME2 in your production environment? If yes, I am interested to know the use case.

Reference: Pinal Dave (http://www.SQLAuthority.com)

SQL SERVER – Datetime Function TODATETIMEOFFSET Example

Earlier I wrote about SQL SERVER – Datetime Function SWITCHOFFSET Example. After reading this blog post, I got another quick reply that if I can explain the usage of TODATETIMEOFFSET as well.

Let us go over the definition of the TODATETIMEOFFSET  from BOL: Returns a datetimeoffset value that is translated from a datetime2 expression.

What essentially it does is that changes the current offset only  offset which we defined. Let us see the example of the same.

SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-04:00') 'GetCurrentOffSet-4';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '+04:00') 'GetCurrentOffSet+4';

Let us see the resultset below.

It is quite clear from the example that only offset changes in the time but unlike SWITCHOFFSET the datetime remains the same.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Datetime Function SWITCHOFFSET Example

I was recently asked if I know how SWITCHOFFSET works. This feature only works in SQL Server 2008.

Here is quick definition of the same from BOL: Returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

What essentially it does is that changes the current offset of the time to any other offset which we defined. Let us see the example of the same.

SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-04:00') 'GetCurrentOffSet-4';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-02:00') 'GetCurrentOffSet-2';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+00:00') 'GetCurrentOffSet+0';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:00') 'GetCurrentOffSet+2';
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+04:00') 'GetCurrentOffSet+4';

Now let us check the resultset of the same.

The example above clearly shows that Switch offset does not only change the offset; it also alters the current time. If you look at GetcurrentOffset, it is +5.30; but, you will notice that GetCurrentOffset-2 does not only change the offset to -2. It also changes the time with the appropriate time at Timezone -2.

I suggest that you run the code in SSMS Query Window and observe the code behavior.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE

Earlier I wrote blog post SQL SERVER – Difference Between GETDATE and SYSDATETIME which inspired me to write SQL SERVER – Difference Between DATETIME and DATETIME2. Now earlier two blog post inspired me to write this blog post (and 4 emails and 3 reads from readers).

I previously populated DATETIME and DATETIME2 field with SYSDATETIME, which gave me very different behavior as SYSDATETIME was rounded up/down for the DATETIME datatype. I just ran the same experiment but instead of populating SYSDATETIME in this script I will be using GETDATE function.
DECLARE @Intveral INT
SET
@Intveral = 10000
CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2)
WHILE (@Intveral > 0)
BEGIN
INSERT
#TimeTable (FirstDate, LastDate)
VALUES (GETDATE(), GETDATE())
SET @Intveral = @Intveral - 1
END
GO
SELECT COUNT(DISTINCT FirstDate) D_FirstDate, COUNT(DISTINCT LastDate) D_LastDate
FROM #TimeTable
GO
SELECT DISTINCT a.FirstDate, b.LastDate
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate = b.LastDate
GO
SELECT *
FROM #TimeTable
GO
DROP TABLE #TimeTable
GO

Let us run above script and observe the results.

You will find that the values of GETDATE which is populated in both the columns FirstDate and LastDate are very much same. This is because GETDATE is of datatype DATETIME and the precision of the GETDATE is smaller than DATETIME2 there is no rounding happening.

In other word, this experiment is pointless. I have included this as I got 4 emails and 3 twitter questions on this subject. If your datatype of variable is smaller than column datatype there is no manipulation of data, if data type of variable is larger than column datatype the data is rounded.

Reference: Pinal Dave (http://www.SQLAuthority.com)

SQL SERVER – Difference Between DATETIME and DATETIME2

Yesterday I have written a very quick blog post on SQL SERVER – Difference Between GETDATE and SYSDATETIME and I got tremendous response for the same. I suggest you read that blog post before continuing with this blog post today. I had asked people to honestly take part and share their view about the above two system functions.

There are few emails as well as few comments on the blog post asking a question on how did I come to know the difference between the same. The answer is from real world issues. I was called in for performance tuning consultancy, where I was asked a very strange question by one developer. Here is the situation faced by him.

System had a single table with two different columns of datetime. One column was datelastmodified and the second column was datefirstmodified. One of the columns was DATETIME and  the other was DATETIME2. Developer was populating each of them with SYSDATETIME. He assumed that the value inserted in the table will be the same. This table was only accessed by INSERT statement, and there were no updates done over it in application. One fine day, he ran distinct on both these columns and was surprised by the result. He always thought that both the tables will have the same data, but in fact, they had very different data.

He presented this scenario to me. I said this is not possible, but I had to agree with him when I looked at the resultset. Here is the simple script to demonstrate the problem he was facing. This is just a sample of the original table.

DECLARE @Intveral INT
SET
@Intveral = 10000
CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2)
WHILE (@Intveral > 0)
BEGIN
INSERT
#TimeTable (FirstDate, LastDate)
VALUES (SYSDATETIME(), SYSDATETIME())
SET @Intveral = @Intveral - 1
END
GO
SELECT COUNT(DISTINCT FirstDate) D_GETDATE, COUNT(DISTINCT LastDate) D_SYSGETDATE
FROM #TimeTable
GO
SELECT DISTINCT a.FirstDate, b.LastDate
FROM #TimeTable a
INNER JOIN #TimeTable b ON a.FirstDate = b.LastDate
GO
SELECT *
FROM #TimeTable
GO
DROP TABLE #TimeTable
GO

Let us see the resultset.

You can clearly see from result that SYSDATETIME() does not populate the same value in both the fields. In fact, the value is either rounded down or rounded up in the field which is DATETIME. Eventhough we are populating the same value, the values are totally different in both the columns, thus resulting in the SELF JOIN fail and displaying of different DISTINCT values.

The best way is to use GETDATE() if you are using DATETIME, and SYSDATETIME() if you are using DATETIME2 to populate them with current date and time for accurately addressing the precision. As DATETIME2 is introduced in SQL Server 2008, the above script will only work with SQL Server 2008 and later versions.

I hope I have answered some of the questions asked yesterday.

Reference: Pinal Dave (http://www.SQLAuthority.com)