SQL SERVER – Validating If Date is Last Day of the Year, Month or Day

Here is one more question I recently received in an email-

“Pinal, is there any ready made function which will display if the given date is the last day or the year, month or day?

For example, if a date is the last day of the Year and last day of the month, I want to display Last Day of the Year and if a date is the last date of the month and last day of the week, I want to display the last day of the week. “

Well, very interesting question and there is no such function available for the same.

However, here is the function I have written earlier for my personal use where I almost accomplish same task.

-- Example of Year
DECLARE @Day DATETIME
SET
@Day = '2014-12-31'
SELECT
CASE
WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@Day)+1,0))) AS DATE) THEN 'LastDayofYear'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Day)+1,0)) AS DATE) THEN 'LastDayofMonth'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@Day),0)) AS DATE) THEN 'LastDayofWeek'
ELSE 'Day'
END
GO
-- Example of Month
DECLARE @Day DATETIME
SET
@Day = '2014-06-30'
SELECT
CASE
WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@Day)+1,0))) AS DATE) THEN 'LastDayofYear'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Day)+1,0)) AS DATE) THEN 'LastDayofMonth'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@Day),0)) AS DATE) THEN 'LastDayofWeek'
ELSE 'Day'
END
GO
-- Example of Month
DECLARE @Day DATETIME
SET
@Day = '2014-05-04'
SELECT
CASE
WHEN CAST(@Day AS DATE) = CAST(DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,@Day)+1,0))) AS DATE) THEN 'LastDayofYear'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Day)+1,0)) AS DATE) THEN 'LastDayofMonth'
WHEN CAST(@Day AS DATE) = CAST(DATEADD(s,-1,DATEADD(wk, DATEDIFF(wk,0,@Day),0)) AS DATE) THEN 'LastDayofWeek'
ELSE 'Day'
END
GO

Let me know if you know any other smarter trick and we can post it here with due credit.

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

SQL DateTime
Previous Post
SQL SERVER – Script to Find First Day of Current Month
Next Post
SQL SERVER – Convert Old Syntax of RAISEERROR to THROW

Related Posts

5 Comments. Leave new

  • Another solution is :

    SELECT CASE WHEN YEAR(@Day) YEAR(DATEADD(DAY,1,@Day)) THEN ‘LastDayofYear’
    WHEN MONTH(@Day) MONTH(DATEADD(DAY,1,@Day)) THEN ‘LastDayofMonth’
    WHEN DATEPART(WEEKDAY,@Day)>DATEPART(WEEKDAY,DATEADD(DAY,1,@Day))THEN ‘LastDayofWeek’ –Week Starting from Sunday
    WHEN DATEPART(WEEKDAY,@Day)<DATEPART(WEEKDAY,DATEADD(DAY,-1,@Day))THEN 'LastDayofWeek' –Week Staring from Monday
    ELSE 'DAY' END

    Reply
  • Leonard Rutkowski
    August 22, 2014 2:37 am

    Here are some SQL, that I use, and I have had for several years. I do not remember where I got them, so please do not credit me with authorship.

    DECLARE @mydate DATETIME
    SELECT @mydate = CURRENT_TIMESTAMP

    SELECT CONVERT(VARCHAR(25),DATEADD(s,0,DATEADD(wk, DATEDIFF(wk,0,@mydate)-1,0)),101) ,
    ‘First Day of Previous Week’
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(s,0,DATEADD(wk, DATEDIFF(wk,0,@mydate)-1,0) + 6),101) ,
    ‘Last Day of Previous Week’
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,@mydate)-1,0)),101) ,
    ‘First Day of Previous Month’
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
    ‘Last Day of Previous Month’
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
    ‘First Day of Current Month’ AS Date_Type
    UNION
    SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, ‘Today’ AS Date_Type
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
    ‘Last Day of Current Month’
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
    ‘First Day of Next Month’
    GO

    –Last Day of Any Month and Year
    DECLARE @dtDate DATETIME
    SET @dtDate = ‘8/18/2007’
    SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0)) ,
    ‘Last Day of Any Month and Year’

    Reply
  • Hey,

    Below is a way which could also be used to figure out last day of any year.

    Please provide your comments if this can be optimized further.

    — Last day of any year w.r.t. given date
    declare @Date date = ‘2017-01-31’
    select DATEADD(m, (12 – MONTH(@Date) + 1), CONVERT(DATE, DATEADD(d, -DAY(@Date), @Date))) as ‘lastDayofYear’

    Thanks!
    Niraj

    Reply
  • how to calculate last day of the year and start day of the year

    Reply
    • Why do you need to calculate that? Just get the year part of the date and use below logic.

      Last day of the year is last day of December and First day of the year is first day on Jan. Dec has always 31 days. First day of year is always first Jan.

      Reply

Leave a Reply