September 30, 2014 at 11:11 pm
DECLARE @currMonthNum INT,
@lastDayMonth INT
SET @currMonthNum = DATEPART(MM, GETDATE())
IF @currMonthNum = (1 | 3 | 5 | 7 | 8 | 10 | 12)
SET @lastDayMonth = 31
IF @currMonthNum = 2
SET @lastDayMonth = 28
IF @currMonthNum = (4 | 6 | 9 | 11)
SET @lastDayMonth = 30
SELECT
@currMonthNum
, @lastDayMonth
By the way, no one has answered why the original query failed.
What it is (the statement below) really doing is BITWISE OR rather than LOGICAL OR , between which you are getting confused, and which of course is giving you wrong results.
Try seeing the result:
select (1 | 3 | 5 | 7 | 8 | 10 | 12)
October 1, 2014 at 12:06 am
david.dartnell (9/30/2014)
Hi ChrisM@Work,I am trying to better understand your solution, towards this end I have started with the inner most brackets -
SELECT DATEDIFF(MONTH, 0, GETDATE())
Upon first inspection I expected this part of the query to return the numbers of months between 0 and the current month; it is October (where I am) as I write this so I was expecting a result of 10.
However after plugging this query into SSMS I was surprised to see a result of 1377!!!
Can you please explain what this query is actually doing?
Kind Regards,
David
The select statement above returns the number of months between the zero (0) day, which 1900-01-01, and today. If you add that difference back to the zero day you will get the first day of the current month.
October 1, 2014 at 1:05 am
You can use this function
CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATE) RETURNS INT
AS
BEGIN
DECLARE @isLeap INT = 0
IF (YEAR(@myDate) % 400 = 0 OR (YEAR(@myDate) % 4 = 0 AND YEAR(@myDate) % 100 !=0))
SET @isLeap=1
DECLARE @month INT = MONTH(@myDate)
DECLARE @days INT
SELECT @days =
CASE
WHEN @month=1 THEN 31
WHEN @month=2 THEN 28 + @isLeap
WHEN @month=3 THEN 31
WHEN @month=4 THEN 30
WHEN @month=5 THEN 31
WHEN @month=6 THEN 30
WHEN @month=7 THEN 31
WHEN @month=8 THEN 31
WHEN @month=9 THEN 30
WHEN @month=10 THEN 31
WHEN @month=11 THEN 30
WHEN @month=12 THEN 31
END
RETURN @days
END
SELECT dbo.fnGetMonthDays(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays(CONVERT(DATE,'2016-02-01'))
--29
Igor Micev,
My blog: www.igormicev.com
October 1, 2014 at 4:12 am
Mohit Dhiman (9/30/2014)
SELECT DATEADD(m,DATEDIFF(m,'19001231',GETDATE()),'19001231')This will give you the month end date for the current month (which can be changed by replacing GETDATE() with whichever other month-date you want)..
And you don't have to worry about leap year here..
SELECT DATEPART(d,DATEADD(m,DATEDIFF(m,'19001231',GETDATE()),'19001231'))This of course if you want the day number instead of the whole date. (Just extract whichever part of the date you want.
I always use this method whenever i have to work with dates to find start/end of year/month/week.
Most folks on ssc would use integer 0 rather than '19001231' as the base date for this type of arithmetic. If you use 0 as the base date, you will see no implicit conversion in the execution plan. Using '19001231' as the base date (or more commonly '19000101') results in an implicit conversion of GETDATE() to datetimeoffset(3) - where you might expect the string base date to be converted to a datetime type.
Try it with this little test harness:
DROP TABLE #Dates
SELECT *
INTO #Dates
FROM (
SELECT MyDate = GETDATE() UNION ALL
SELECT GETDATE()-1 UNION ALL
SELECT GETDATE()-10 UNION ALL
SELECT GETDATE()-100
) d
SELECT MonthEndDate = DATEADD(m,DATEDIFF(m,'19001231',MyDate),'19001231')
FROM #Dates
-- Scalar Operator(dateadd(month,datediff(month,'1900-12-31 00:00:00.0000000 +00:00',CONVERT_IMPLICIT(datetimeoffset(3),[tempdb].[dbo].[#Dates].[MyDate],0)),'1900-12-31 00:00:00.000'))
SELECT MonthEndDate = DATEADD(m,DATEDIFF(m,0,MyDate),0)
FROM #Dates
-- Scalar Operator(dateadd(month,datediff(month,'1900-01-01 00:00:00.000',[tempdb].[dbo].[#Dates].[MyDate]),'1900-01-01 00:00:00.000'))
GO
DROP TABLE #Dates
SELECT *
INTO #Dates
FROM (
SELECT MyDate = CAST(GETDATE() AS DATETIMEOFFSET(3)) UNION ALL
SELECT GETDATE()-1 UNION ALL
SELECT GETDATE()-10 UNION ALL
SELECT GETDATE()-100
) d
SELECT MonthEndDate = DATEADD(m,DATEDIFF(m,'19001231',MyDate),'19001231')
FROM #Dates
-- Scalar Operator(dateadd(month,datediff(month,'1900-12-31 00:00:00.0000000 +00:00',[tempdb].[dbo].[#Dates].[MyDate]),'1900-12-31 00:00:00.000'))
SELECT MonthEndDate = DATEADD(m,DATEDIFF(m,0,MyDate),0)
FROM #Dates
-- Scalar Operator(dateadd(month,datediff(month,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[tempdb].[dbo].[#Dates].[MyDate],0)),'1900-01-01 00:00:00.000'))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 1, 2014 at 4:19 am
Igor Micev (10/1/2014)
You can use this function
CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATE) RETURNS INT
AS
BEGIN
DECLARE @isLeap INT = 0
IF (YEAR(@myDate) % 400 = 0 OR (YEAR(@myDate) % 4 = 0 AND YEAR(@myDate) % 100 !=0))
SET @isLeap=1
DECLARE @month INT = MONTH(@myDate)
DECLARE @days INT
SELECT @days =
CASE
WHEN @month=1 THEN 31
WHEN @month=2 THEN 28 + @isLeap
WHEN @month=3 THEN 31
WHEN @month=4 THEN 30
WHEN @month=5 THEN 31
WHEN @month=6 THEN 30
WHEN @month=7 THEN 31
WHEN @month=8 THEN 31
WHEN @month=9 THEN 30
WHEN @month=10 THEN 31
WHEN @month=11 THEN 30
WHEN @month=12 THEN 31
END
RETURN @days
END
SELECT dbo.fnGetMonthDays(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays(CONVERT(DATE,'2016-02-01'))
--29
Why? You can make an inline table-valued function from
SELECT MonthEndDate = DATEPART(DAY,DATEADD(DAY,-1,DATEADD(m,1+DATEDIFF(m,0,MyDate),0)))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 1, 2014 at 4:36 am
ChrisM@Work (10/1/2014)
Mohit Dhiman (9/30/2014)
SELECT DATEADD(m,DATEDIFF(m,'19001231',GETDATE()),'19001231')This will give you the month end date for the current month (which can be changed by replacing GETDATE() with whichever other month-date you want)..
And you don't have to worry about leap year here..
SELECT DATEPART(d,DATEADD(m,DATEDIFF(m,'19001231',GETDATE()),'19001231'))This of course if you want the day number instead of the whole date. (Just extract whichever part of the date you want.
I always use this method whenever i have to work with dates to find start/end of year/month/week.
Most folks on ssc would use integer 0 rather than '19001231' as the base date for this type of arithmetic. If you use 0 as the base date, you will see no implicit conversion in the execution plan. Using '19001231' as the base date (or more commonly '19000101') results in an implicit conversion of GETDATE() to datetimeoffset(3) - where you might expect the string base date to be converted to a datetime type.
I agree largely with that..
But the reason i wrote the dates explicitly was to make it more understandable as to what's happening behind the scenes..
With 0/1/-1 there might be confusion for few people (specially when they have no idea that 0 = 19000101).
Also, i wanted to highlight the point that the base date, does not necessarily have to be from 0/1/-1...
It could be any date (20141001 for that matter :cool:) as long as you are using the correct base date for the correct purpose..
So :
to find month end the base date has to be a month ending date like - 18991231 , 19991231, 20140131,20140930 ......
to find year start the base date has to be a year starting date like - 18990101 , 19990101, 20140101 ...... and so on.
From a performance perspective we may benefit slightly by using integers but for the sake of clarity i'd rather use explicit dates.
Or one could use a datetime variable like:
DECLARE @BaseDate DATETIME
SET @BaseDate = CAST('19000101' AS DATETIME)
October 1, 2014 at 4:40 am
ChrisM@Work (10/1/2014)
Igor Micev (10/1/2014)
You can use this function
CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATE) RETURNS INT
AS
BEGIN
DECLARE @isLeap INT = 0
IF (YEAR(@myDate) % 400 = 0 OR (YEAR(@myDate) % 4 = 0 AND YEAR(@myDate) % 100 !=0))
SET @isLeap=1
DECLARE @month INT = MONTH(@myDate)
DECLARE @days INT
SELECT @days =
CASE
WHEN @month=1 THEN 31
WHEN @month=2 THEN 28 + @isLeap
WHEN @month=3 THEN 31
WHEN @month=4 THEN 30
WHEN @month=5 THEN 31
WHEN @month=6 THEN 30
WHEN @month=7 THEN 31
WHEN @month=8 THEN 31
WHEN @month=9 THEN 30
WHEN @month=10 THEN 31
WHEN @month=11 THEN 30
WHEN @month=12 THEN 31
END
RETURN @days
END
SELECT dbo.fnGetMonthDays(CONVERT(DATE,'2014-02-01'))
--28
SELECT dbo.fnGetMonthDays(CONVERT(DATE,'2016-02-01'))
--29
Why? You can make an inline table-valued function from
SELECT MonthEndDate = DATEPART(DAY,DATEADD(DAY,-1,DATEADD(m,1+DATEDIFF(m,0,MyDate),0)))
I know, I've already proposed your solution above.
I just reminded myself on a specific language I used to use for determining leap year and decided to just add this as well.
Igor Micev,
My blog: www.igormicev.com
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply