Feeds:
Posts
Comments

Posts Tagged ‘EOMONTH’

SQL SERVER – How to get first Sunday of next month using EOMONTH

Posted in Solutions, SQL SERVER, tagged , , , , , on January 7, 2014| Leave a Comment »

I came across this query when I was automating one of my services for a customer and it had to run on first Sunday of every month. I already had this solution earlier and utilized it in many places, but as my customer is using SQL Server 2012, I would like to write this script in a different way with less no of codes. So I started scripting and finally developed it using EOMONTH function. (A new function shipped with SQL Server 2012).

Let me create a sample to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-05')
INSERT INTO tbl_Sample VALUES (2,'2013-02-01')
INSERT INTO tbl_Sample VALUES (3,'2013-05-05')
INSERT INTO tbl_Sample VALUES (4,'2013-07-20')
INSERT INTO tbl_Sample VALUES (5,'2013-08-28')

GO

SOLUTION 1 : Using EOMONTH
Given below is the script.

USE tempdb
GO
SELECT DATEADD(DAY,8-
DATEPART(WEEKDAY,DATEADD(DAY,1,EOMONTH([Date])))
,EOMONTH([Date])) AS [First Sunday of the next month]
FROM tbl_Sample
GO
--OUTPUT

firstsundayofthemonth.1.1

SOLUTION 2 : Using Traditional Method
Given below is the script.

USE tempdb
GO
SELECT CONVERT(DATE,DATEADD(DAY,8-
DATEPART(WEEKDAY,DATEADD(MONTH,DATEDIFF(MONTH, 0, [Date])+1,0))
,DATEADD(MONTH,DATEDIFF(MONTH, 0, [Date])+1, -1)))
AS [First Sunday of the next month]
FROM tbl_Sample
GO
--OUTPUT

firstsundayofthemonth.1.1

Read Full Post »

SQL SERVER 2012 – How to check date is End of Month date or not?

Posted in Solutions, SQL SERVER, tagged , , , , , on September 30, 2013| 3 Comments »

Today is month end (30 September 2013) and I was checking my general checklist for month end closing procedure. I saw one of my scripts that I had written earlier regarding how to check whether a date is month end date or not. So, I thought of re-writing this script even much shorter & efficient than my earlier script using EOMONTH .

Let me create a sample table to demonstrate.

USE tempdb
GO
--DROP TABLE tbl_Sample
--GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-03-31')
INSERT INTO tbl_Sample VALUES (3,'2013-10-10')
INSERT INTO tbl_Sample VALUES (4,'2013-12-31')
GO

SOLUTION 1 :
Given below is the script that we usually use in SQL Server 2005/2008 to check whether the date is End of month date or not.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT [ID],[Date],
(CASE WHEN
CONVERT(varchar(11)
,DATEADD(MONTH, DATEDIFF(MONTH, 0, [Date])+1, -1),120)=[Date]
THEN 'Month End' ELSE 'Not a Month End' END) As [Result]
FROM tbl_Sample
GO

isdate is a mont end date.1.1

SOLUTION 2 :
Given below is the script, that I developed using new functions (EOMONTH & IIF) shipped in SQL Server 2012 to check whether the date is End of month date or not. Due to these functions, we can reduce the script size and improve the performance.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT [ID],[Date],
IIF(EOMONTH([Date])=[Date],'Month End','Not a Month End')
As [Result]
FROM tbl_Sample
GO
--OUTPUT

isdate is a mont end date.1.2

CONCLUSION :
As you can see, both solutions have the same result set but Solution 2 is recommended because it is using only one function to check whether the date is month end date or not, instead of a lot of date functions.

Read Full Post »

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 – EOMONTH

Posted in Functions, SQL SERVER, tagged , , , , , on September 10, 2012| 3 Comments »

“How to calculate the last date of the current, previous & next month” ? I have seen this question many times in different blogs and there are multiple approaches that we have been following to find it.
For Example :

--SQL Server 2005/2008
DECLARE @DATE DATETIME
SET @DATE='2012-09-10'

--Last date of the Previous Month
SELECT CONVERT(VARCHAR(10),DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE),0)),120) AS [Previous Month]

--Last date of the Current Month
SELECT CONVERT(VARCHAR(10),DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0)),120) AS [Current Month]

--Last date of the Next Month
SELECT CONVERT(VARCHAR(10),DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+2,0)),120) AS [Next Month]

Previous Month
--------------
2012-08-31

(1 row(s) affected)

Current Month
-------------
2012-09-30

(1 row(s) affected)

Next Month
-----------
2012-10-31

(1 row(s) affected)

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

Syntax :

EOMONTH ( start_date [, month_to_add ] )

Parameters :
@start_date : A valid Date in varchar or date time data type (Mandatory)
@month_to_add : A valid integer for month range from 0-9999 (Optional). By default it is zero.

Purpose :
This function requires a valid date (Either in Datetime or varchar data type) as a parameter and returns last date of that month. There is an optional parameter called “@month_to_add”. By using this optional parameter, we can get the last date of other months also. It could be previous, next or any month.

Let me explain this with simple examples.

Example-1 : EOMONTH – valid start date as Date data type

Declare @Date as Date ='2012-09-01'

--Last date of the Previous Month
Select EOMONTH (@Date,-1) as [Previous Month]

--Last date of the current Month
Select EOMONTH (@Date) as [Current Month]

--Last date of the Next Month
Select EOMONTH (@Date,1) as [Next Month]

Previous Month
--------------
2012-08-31

(1 row(s) affected)

Current Month
-------------
2012-09-30

(1 row(s) affected)

Next Month
----------
2012-10-31

(1 row(s) affected)

Example-2 : EOMONTH – valid start date as varchar data type

Declare @Date as varchar(10) ='2012-09-01'

--Last date of the current Month
Select EOMONTH (@Date) as [Current Month]

Current Month
-------------
2012-09-30

(1 row(s) affected)

Example-3 : EOMONTH – Last Date of each month in the Current Year 

Declare @Date as Date ='2012-01-01'

--Last date of the Jan Month
Select EOMONTH (@Date) as [Jan]

--Last date of the Feb Month
Select EOMONTH (@Date,1) as [Feb]

--Last date of the Mar Month
Select EOMONTH (@Date,2) as [Mar]

--Last date of the Apr Month
Select EOMONTH (@Date,3) as [Apr]

--Last date of the May Month
Select EOMONTH (@Date,4) as [May]

--Last date of the Jun Month
Select EOMONTH (@Date,5) as [Jun]

--Last date of the Jul Month
Select EOMONTH (@Date,6) as [Jul]

--Last date of the Aug Month
Select EOMONTH (@Date,7) as [Aug]

--Last date of the Sep Month
Select EOMONTH (@Date,8) as [Sep]

--Last date of the Oct Month
Select EOMONTH (@Date,9) as [Oct]

--Last date of the Nov Month
Select EOMONTH (@Date,10) as [Nov]

--Last date of the Dec Month
Select EOMONTH (@Date,11) as [Dec]

Jan
----------
2012-01-31

(1 row(s) affected)

Feb
----------
2012-02-29

(1 row(s) affected)

Mar
----------
2012-03-31

(1 row(s) affected)

Apr
----------
2012-04-30

(1 row(s) affected)

May
----------
2012-05-31

(1 row(s) affected)

Jun
----------
2012-06-30

(1 row(s) affected)

Jul
----------
2012-07-31

(1 row(s) affected)

Aug
----------
2012-08-31

(1 row(s) affected)

Sep
----------
2012-09-30

(1 row(s) affected)

Oct
----------
2012-10-31

(1 row(s) affected)

Nov
----------
2012-11-30

(1 row(s) affected)

Dec
----------
2012-12-31

(1 row(s) affected)

Reference : MSDN

Read Full Post »