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)
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
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’
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
how to calculate last day of the year and start day of the year
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.