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 (http://blog.SQLAuthority.com)

About these ads

3 thoughts on “SQL SERVER – Validating If Date is Last Day of the Year, Month or Day

  1. 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

  2. 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’

  3. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s