Sql Server 2005 Get First and Last Date for Any Month in Any Year

SQL Server 2005 Get First and Last date for any Month in any Year

DECLARE @Month int
DECLARE @Year int

set @Month = 2
set @Year = 2004

select DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) /*First*/

select DATEADD(day,-1,DATEADD(month,@Month,DATEADD(year,@Year-1900,0))) /*Last*/

But what do you need as time component for last day of the month? If your datetimes have time components other than midnight you may well be better off just doing something like

WHERE COL >= DATEADD(month,@Month-1,DATEADD(year,@Year-1900,0)) 
     AND COL < DATEADD(month,@Month,DATEADD(year,@Year-1900,0)) 

In this way your code will continue to work if you eventually migrate to SQL Server 2008 and the greater precision datetime datatypes.

Get First and Last Day of Any Year

I should refrain from developing in the evenings because I solved it, and it's actually quite simple:

SELECT MIN(Date)
    , MAX(Date)
FROM Table
GROUP BY YEAR(Date)

I can put these values into a CTE and then JOIN on the dates and get what I need:

;WITH CT AS(
    SELECT MIN(Date) Mi
        , MAX(Date) Ma
    FROM Table
    GROUP BY YEAR(Date)
)
SELECT c.Mi
    , m.Points
    , c.Ma
    , f.Points
FROM CT c
    INNER JOIN Table m ON c.Mi = m.Date
    INNER JOIN Table f ON c.Ma = f.Date

Get first & last date for next 12 months in sql in datetime format

Assuming EOMONTH function is supported, use

SELECT dateadd(day,1,dateadd(month,number-1,EOMONTH(getdate()))) as mth_start,
dateadd(month,number,EOMONTH(getdate())) as mth_end
FROM Master.dbo.spt_values
WHERE NAME IS NULL
AND Number BETWEEN 0 AND 11

Finding the first and last business day for every month sql

If you got already a table with all the weekdays only:

select min(datecol), max(datecol)
from BusinessOnlyCalendar
group by year(datecol), month(datecol)

But you should expand your calendar to include all those calculations you might do on date, like FirstDayOfWeek/Month/Quarter/Year, WeekNumber, etc.

When you got a column in your calendar indicating business day yes/no, it's a simple:

select min(datecol), max(datecol)
from calendar
where businessday = 'y'
group by year(datecol), month(datecol)

Month Name and Last date of each month for previous 3 months in sql

You can use SQL Server EOMONTH() function to compute the last day of a month.

Consider:

WITH R(N) AS (
    SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N < 3
)
SELECT 
    LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month], 
    EOMONTH(GETDATE(), -N) AS [EOM_DATE],
    DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS [year]
FROM R;

SQL Query to find the last day of the month

Try this one -

CREATE FUNCTION [dbo].[udf_GetLastDayOfMonth] 
(
    @Date DATETIME
)
RETURNS DATETIME
AS
BEGIN

    RETURN DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @Date) + 1, 0))

END

Query:

DECLARE @date DATETIME
SELECT @date = '2013-05-31 15:04:10.027'

SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) + 1, 0))

Output:

-----------------------
2013-05-31 00:00:00.000

---

Related Topics