SQL Server Query to Find First Day and Last Day of Month
Introduction
There can be a scenario where you need to load data in batch into warehouse for the current month. During such scenarios identifying the first and last day of the month becomes necessary. This article includes the TSQL script specific to find first and last date of a month in SQL Server 2008 and higher versions.
Find First Day & Last Day of the Month in SQL Server 2008/2008R2
Execute the below TSQL script to find first day & last day of the month in SQL Server 2008/2008R2.
DECLARE @CurrentDate AS DATE = GETDATE()
/* First Day of the Month */
SELECT DATEADD(DD, -DAY(@CurrentDate) + 1, @CurrentDate) AS FirstDayOfTheMonth
/* Last Day of the Month */
SELECT DATEADD(DD, -DAY(DATEADD(MM, 1, @CurrentDate)), DATEADD(MM, 1, @CurrentDate)) AS LastDayOfTheMonth
/* First Day of the Month */
SELECT DATEADD(DD, -DAY(@CurrentDate) + 1, @CurrentDate) AS FirstDayOfTheMonth
/* Last Day of the Month */
SELECT DATEADD(DD, -DAY(DATEADD(MM, 1, @CurrentDate)), DATEADD(MM, 1, @CurrentDate)) AS LastDayOfTheMonth
For more information on New Data and Time Functions in SQL Server 2008/2008 R2 see here.
Retrieve First Day & Last Day of the Month in SQL Server 2012
Execute the below TSQL script to retrieve first day & last day of the month in SQL Server 2012.
DECLARE @CurrentDate AS DATE = GETDATE()
/* First Day of the Month */
SELECT DATEADD(DD, -DAY(@CurrentDate) + 1, @CurrentDate) AS FirstDayOfTheMonth
/* Last Day of the Month */
SELECT EOMONTH(@CurrentDate) AS LastDayOfTheMonth
/* First Day of the Month */
SELECT DATEADD(DD, -DAY(@CurrentDate) + 1, @CurrentDate) AS FirstDayOfTheMonth
/* Last Day of the Month */
SELECT EOMONTH(@CurrentDate) AS LastDayOfTheMonth
Read more: http://www.mytechmantra.com/LearnSQLServer/SQL-Server-Query-to-Find-First-Day-And-Last-Day-of-Month/#ixzz3h4eLOYHV
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook
Introduction
SQL Server 2012 has introduced new Date and Time functions in SQL Server 2012. This article explains each of them with along with an example.
Recommended:- Date and Time Functions in SQL Server 2008 / 2008 R2
New Data and Time Functions Introduced in SQL Server 2012 are:-
- DATEFROMPARTS
- DATETIMEFROMPARTS
- DATETIME2FROMPARTS
- TIMEFROMPARTS
- SMALLDATETIMEFROMPARTS
- DATETIMEOFFSETFROMPARTS
- EOMONTH
Let take a look at each of them with an example.
Using DATEFROMPARTS Function in SQL Server 2012
DATEFROMPARTS function in SQL Server 2012 returns a date value for the specified Year, Month, and Day.
Syntax DATEFROMPARTS
DATEFROMPARTS (year, month, day)
SELECT DATEFROMPARTS (2012, 12, 12) AS DATEFROMPARTS
GO
DATEFROMPARTS
--------------------
2012-12-12
DATEFROMPARTS (year, month, day)
SELECT DATEFROMPARTS (2012, 12, 12) AS DATEFROMPARTS
GO
DATEFROMPARTS
--------------------
2012-12-12
Using DATETIMEFROMPARTS Function in SQL Server 2012
DATETIMEFROMPARTS function in SQL Server 2012 returns a datetime value for the specified date and time.
Syntax DATETIMEFROMPARTS
DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
SELECT DATETIMEFROMPARTS (2012, 12, 12, 12, 12, 12, 0) AS DATETIMEFROMPARTS
GO
DATETIMEFROMPARTS
---------------------------
2012-12-12 12:12:12.000
DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)
SELECT DATETIMEFROMPARTS (2012, 12, 12, 12, 12, 12, 0) AS DATETIMEFROMPARTS
GO
DATETIMEFROMPARTS
---------------------------
2012-12-12 12:12:12.000
Using DATETIME2FROMPARTS Function in SQL Server 2012
DATETIME2FROMPARTS function in SQL Server 2012 returns a datetime2 value for the specified date and time and with the specified precision.
Syntax DATETIME2FROMPARTS
DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
SELECT DATETIME2FROMPARTS (2012, 12, 12, 23, 59, 59, 500, 3) AS DATETIME2FROMPARTS
GO
DATETIME2FROMPARTS
--------------------------
2012-12-12 23:59:59.50
DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)
SELECT DATETIME2FROMPARTS (2012, 12, 12, 23, 59, 59, 500, 3) AS DATETIME2FROMPARTS
GO
DATETIME2FROMPARTS
--------------------------
2012-12-12 23:59:59.50
Using TIMEFROMPARTS Function in SQL Server 2012
TIMEFROMPARTS function in SQL Server 2012 returns a time value for the specified time and with the specified precision.
Syntax TIMEFROMPARTS
TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
SELECT TIMEFROMPARTS (23, 59, 59, 0, 0) AS TIMEFROMPARTS
GO
TIMEFROMPARTS
-------------------
23:59:59
TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
SELECT TIMEFROMPARTS (23, 59, 59, 0, 0) AS TIMEFROMPARTS
GO
TIMEFROMPARTS
-------------------
23:59:59
Using SMALLDATETIMEFROMPARTS Function in SQL Server 2012
SMALLDATETIMEFROMPARTS function in SQL Server 2012 Returns a smalldatetime value for the specified date and time.
Syntax SMALLDATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
SELECT SMALLDATETIMEFROMPARTS (2012, 12, 12, 23, 59) AS SMALLDATETIMEFROMPARTS
GO
SMALLDATETIMEFROMPARTS
---------------------------------
2012-12-12 23:59:00
SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
SELECT SMALLDATETIMEFROMPARTS (2012, 12, 12, 23, 59) AS SMALLDATETIMEFROMPARTS
GO
SMALLDATETIMEFROMPARTS
---------------------------------
2012-12-12 23:59:00
Using DATETIMEOFFSETFROMPARTS Function in SQL Server 2012
DATETIMEOFFSETFROMPARTS function in SQL Server 2012 Returns a smalldatetime value for the specified date and time.
Syntax DATETIMEOFFSETFROMPARTS
DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
SELECT DATETIMEOFFSETFROMPARTS (2012, 12, 12, 12, 12, 12, 0, 8, 00, 3 ) AS DATETIMEOFFSETFROMPARTS
GO
DATETIMEOFFSETFROMPARTS
------------------------------------
2012-12-12 12:12:12.000 +08:00
DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
SELECT DATETIMEOFFSETFROMPARTS (2012, 12, 12, 12, 12, 12, 0, 8, 00, 3 ) AS DATETIMEOFFSETFROMPARTS
GO
DATETIMEOFFSETFROMPARTS
------------------------------------
2012-12-12 12:12:12.000 +08:00
Using EOMONTH Function in SQL Server 2012
DATETIMEOFFSETFROMPARTS function in SQL Server 2012 Returns a smalldatetime value for the specified date and time.
Syntax EOMONTH
EOMONTH ( start_date [, month_to_add ] )
DECLARE @TodaysDate DATETIME = GETDATE()
SELECT EOMONTH (@TodaysDate) AS 'This Month'
SELECT EOMONTH (@TodaysDate, 1) AS 'Next Month'
SELECT EOMONTH (@TodaysDate, -1) AS 'Last Month'
GO
This Month
----------
2012-03-31
Next Month
----------
2012-04-30
Last Month
----------
2012-02-28
EOMONTH ( start_date [, month_to_add ] )
DECLARE @TodaysDate DATETIME = GETDATE()
SELECT EOMONTH (@TodaysDate) AS 'This Month'
SELECT EOMONTH (@TodaysDate, 1) AS 'Next Month'
SELECT EOMONTH (@TodaysDate, -1) AS 'Last Month'
GO
This Month
----------
2012-03-31
Next Month
----------
2012-04-30
Last Month
----------
2012-02-28
Conclusion
This article gives you an overview of new date and time functions in SQL Server 2012. We would recommend you to try them and “Share this Article” with your colleagues and friends.
Read more: http://www.mytechmantra.com/LearnSQLServer/New-Data-And-Time-Functions-in-SQL-Server-2012/#ixzz3h4eVdvwZ
Follow us: @MyTechMantra on Twitter | MyTechMantra on Facebook
