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