Feeds:
Posts
Comments

Posts Tagged ‘DATEFROMPARTS()’

SQL SERVER 2012 – Detecting Leap Year in T-SQL using SQL SERVER 2012 Functions

Posted in Solutions, SQL SERVER, tagged , , , , , , on January 23, 2013| Leave a Comment »

I was working on statistical reports and I had to display whether the given year was leap year or not in a field. So, I developed few ways using SQL SERVER 2012 functions to detect whether leap year or not.

Given below are the multiple ways to detect leap year:

Method 1 :
In method 1, I made a date (2012-02-28) using DATEFROMPARTS function then used EOMONTH function to find the last date (2012-02-29) of February. Then to find the last day (29), used DAY function and after that used IIF to check whether it is 29 or not, to detect if its a leap year or not.

DECLARE @Year INT =2012
SELECT IIF(DAY(EOMONTH(DATEFROMPARTS(@Year,2,28)))=29 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)
Method 2 :
In method 2, I simply made 2 dates using DATEFROMPARTS function. First is the 1st date of the year and 2nd date is the last date of the year and then used DATEDIFF function to find total no of days in a year and finally used IIF function to check if it is 366 or 365 days. If 366, then leap year and if 365 then it is not.

DECLARE @Year INT =2012
SELECT IIF(DATEDIFF(DAY,DATEFROMPARTS(@Year,1,1), DATEFROMPARTS(@year,12,31))+1=366 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)
Method 3 :
In method 3, I first made a date(2012-02-28) using DATEFROMPARTS function then added one day to it. After addition I took the day and checked it using IIF function. If it is 29, then leap year, else, it is not.

DECLARE @Year INT =2012
SELECT IIF(DAY(DATEADD(DAY,1,DATEFROMPARTS(@Year,2,28)))=29 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)

Method 4 :
In method 4, I made 2 dates (2012-02-28, 2012-03-01) using DATEFROMPARTS function and then found the date difference using DATEDIFF function, and finally used IIF function to check the difference. If it is 2 days, means the year is leap, else not.

DECLARE @Year INT =2012
SELECT IIF(DATEDIFF(DAY,DATEFROMPARTS(@Year,2,28), DATEFROMPARTS(@year,3,01))=2 ,'YES','NO') AS [LEAP YEAR]
--RESULT

LEAP YEAR
———
YES

(1 row(s) affected)

Note : Try @Year as 2013 and all the result set will return that 2013 is not a leap year using any above methods.

Let me know if you know a better method to detect leap year.

Read Full Post »

SQL SERVER 2012 -Date and Time Function – DATEFROMPARTS()

Posted in Functions, SQL SERVER, tagged , , on August 31, 2012| 3 Comments »

Before SQL SERVER 2012, If we have a scenario that Year, Month & day are given to us and we need to convert it into date and return it. In this case we need to do a lot of conversion and then we can get our desired results.

For Example :

--SQL Server 2008
Declare @Year as int=2012
Declare @Month as int=12
Declare @Day as int=31

Select Convert(Date,Convert(varchar(4), @Year)
+
Convert(varchar(2), @Month)
+
Convert(varchar(2), @Day))
--Result
2012-12-31

But In SQL Server 2012, the solution came as a new built in function namely DATEFROMPARTS().
Lets discuss this function syntax, parameters, purpose and examples in detail.

Syntax :

DATEFROMPARTS ( year, month, day )

Parameters :
@Year : A valid integer for year.
@Month : A valid integer for month range from 1-12.
@Day : A valid integer for day range from 1-31 (depends upon the total number of days in a month)

Purpose :
This function requires a valid Year, Month & day as a parameter and returns  a valid date. If we pass any invalid date part to this function it will return an error. Also if we pass NULL to any of its parameter, it returns NULL value.

Let me explain this with simple examples.

Example-1 : DATEFROMPARTS – With valid date parts

Declare @Year as int=2012
Declare @Month as int = 12
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month,@Day) as [Result]
--Result

Example-2 : DATEFROMPARTS – With Invalid date parts
If we provide an invalid date part to this function, it will generate an error because only valid date parts are allowed as a parameter.

Declare @Year as int=2012
Declare @Month as int = 13 -- 13 is not a Valid month
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month,@Day) as [Result]
--Result

Msg 289, Level 16, State 1, Line 5
Cannot construct data type date, some of the arguments have values which are not valid.

Example-3 : DATEFROMPARTS – With NULL Date Parts
If we pass NULL value to any parameter of this function, It will return NULL value.

Declare @Year as int=2012
Declare @Month as int = NULL -- Passed NULL as a Month
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month,@Day) as [Result]
--Result

Example-4 : DATEFROMPARTS – With missing parameters
As I have mentioned above that all parameters(3) are mandatory for DATEFROMPARTS function. Lets try to pass less than 3 parameters.

Declare @Year as int=2012
Declare @Month as int = 12
Declare @Day as int =31

Select DATEFROMPARTS(@Year,@Month) as [Result]
--Result

Reference : MSDN

Read Full Post »