September 14, 2018 at 10:59 am
sgmunson - Friday, September 14, 2018 10:53 AMDoesn't anyone realize that DATEFROMPARTS(YEAR(SomeDateValue), MONTH(SameDateValue), 1) is the functional equivalent ? 😀😀😀
But it takes more typing... :laugh:
September 14, 2018 at 11:18 am
Lynn Pettis - Friday, September 14, 2018 10:59 AMBut it takes more typing... :laugh:
And more code reviewing. Particularly because intellisense in smss doesn't label the parameters in a helpful way.
September 14, 2018 at 9:11 pm
drew.allen - Friday, September 14, 2018 10:03 AMandycadley - Friday, September 14, 2018 9:03 AM+1 billion on FOMONTH. How on earth was that not so obviously needed for exactly the same reason as EOMONTH? Grrrr.The day for the end of the month varies depending on what month it is and secondarily on whether it is a leap year. The first of the month is always 1. That difference in variability is exactly the reason that one has a function and the other does not.
Drew
Which was a stupid move on the part of MS because you still need to use multiple parts to calculate the first of the month. EOMonth is also stupid because it only calculates the beginning of the last day of the month, not the end.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 14, 2018 at 9:13 pm
Jonathan AC Roberts - Friday, September 14, 2018 9:10 AMandycadley - Friday, September 14, 2018 9:03 AM+1 billion on FOMONTH. How on earth was that not so obviously needed for exactly the same reason as EOMONTH? Grrrr.Surely a FOMONTH function would return the same date for every month i.e. the 1st?
That's the same day... not the same date.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2018 at 5:40 pm
Jeff Moden - Friday, September 14, 2018 9:11 PMWhich was a stupid move on the part of MS because you still need to use multiple parts to calculate the first of the month. EOMonth is also stupid because it only calculates the beginning of the last day of the month, not the end.
I so wish this actually returned the end of month + 11:59:59.999999
September 16, 2018 at 6:06 am
Steve Jones - SSC Editor - Saturday, September 15, 2018 5:40 PMJeff Moden - Friday, September 14, 2018 9:11 PMWhich was a stupid move on the part of MS because you still need to use multiple parts to calculate the first of the month. EOMonth is also stupid because it only calculates the beginning of the last day of the month, not the end.
I so wish this actually returned the end of month + 11:59:59.999999
In which data type?
_____________
Code for TallyGenerator
September 16, 2018 at 9:01 am
Steve Jones - SSC Editor - Saturday, September 15, 2018 5:40 PMJeff Moden - Friday, September 14, 2018 9:11 PMWhich was a stupid move on the part of MS because you still need to use multiple parts to calculate the first of the month. EOMonth is also stupid because it only calculates the beginning of the last day of the month, not the end.
I so wish this actually returned the end of month + 11:59:59.999999
I believe you mean 23:59:59.999999 but I'm glad it doesn't because it's not right. For date ranges, the best way is to use a Closed/Open check similar to the following...
WHERE @SomeTemporalColumn >= @StartDate (with no time) and @SomeTemporalColumn < @EndDate + 1 day (also with no time).
To Sergiy's point, it makes things "datatype agnostic".
--Jeff Moden
Change is inevitable... Change for the better is not.
September 16, 2018 at 10:44 am
I'd rather an EODAY function to solve that. And I guess an SODAY, which perhaps means FOMONTH should be SOMONTH. And let's have an SOWEEK, EOWEEK whilst we're at it....
September 16, 2018 at 6:21 pm
andycadley - Sunday, September 16, 2018 10:44 AMI'd rather an EODAY function to solve that. And I guess an SODAY, which perhaps means FOMONTH should be SOMONTH. And let's have an SOWEEK, EOWEEK whilst we're at it....
There is no such thing as End Of a Day.
End of the day is a moment in time preceeding the beginning of the next day by an infinitely small period of time.
Computer math can only handle finite depth ofprecision, so it does not have means to correctly present an infinitly small period of time preceeding the zero time moment of the next day.
Whatever value you choose it will be only an approximation of the correct value with the precision allowed by any particular data type been chosen.
Comparing it to points int time measured using other data types will cause either overflow or data loss, depending on the direction of the mismatch.
So - forget about EODAY, it's a faulty concept altogether.
Always use "before start of next day", or " < SONDAY"
_____________
Code for TallyGenerator
September 17, 2018 at 3:13 am
andycadley - Friday, September 14, 2018 10:25 AMdrew.allen - Friday, September 14, 2018 10:03 AMThe day for the end of the month varies depending on what month it is and secondarily on whether it is a leap year. The first of the month is always 1. That difference in variability is exactly the reason that one has a function and the other does not.Drew
But writing DATEFROMPARTS(DatePart(Year, @d), DatePart(Month, @d), 1) to do what should just be FOMONTH(@d) is laborious and error prone for no good reason. Yes the first of the month is easier to get to, but if you're adding one function adding the other seems like common sense.
The quickest I've found (so far) is to subtract the day of month from the date.
😎
CONVERT(DATE,DATEADD(DAY,1-DATEPART(DAY,@DATETIME),@DATETIME))
The code in a table valued function performs equally fast
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE OR ALTER FUNCTION dbo.ITVFN_FOMONTH
(
@INDATETIME DATETIME
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT
CONVERT(DATE,DATEADD(DAY,1-DATEPART(DAY,@INDATETIME),@INDATETIME)) AS FOMONTH
;
GO
September 17, 2018 at 11:57 am
Eirikur Eiriksson - Monday, September 17, 2018 3:13 AMThe quickest I've found (so far) is to subtract the day of month from the date.
😎
CONVERT(DATE,DATEADD(DAY,1-DATEPART(DAY,@DATETIME),@DATETIME))The code in a table valued function performs equally fast
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE OR ALTER FUNCTION dbo.ITVFN_FOMONTH
(
@INDATETIME DATETIME
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT
CONVERT(DATE,DATEADD(DAY,1-DATEPART(DAY,@INDATETIME),@INDATETIME)) AS FOMONTH
;
GO
What about:
SELECT DATEADD(DAY, 1, EOMONTH(@INDATETIME), -1))
Not sure if this performs any better or worse - but is quite a bit shorter
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2018 at 12:20 pm
Jeffrey Williams 3188 - Monday, September 17, 2018 11:57 AMWhat about:SELECT DATEADD(DAY, 1, EOMONTH(@INDATETIME), -1))
Not sure if this performs any better or worse - but is quite a bit shorter
It's about 20% slower than the other
:cool:.
September 17, 2018 at 12:36 pm
Eirikur Eiriksson - Monday, September 17, 2018 12:20 PMIt's about 20% slower than the other
:cool:.
Good to know - thanks.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 17, 2018 at 12:36 pm
Eirikur Eiriksson - Monday, September 17, 2018 3:13 AMThe quickest I've found (so far) is to subtract the day of month from the date.
😎
CONVERT(DATE,DATEADD(DAY,1-DATEPART(DAY,@DATETIME),@DATETIME))The code in a table valued function performs equally fast
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE OR ALTER FUNCTION dbo.ITVFN_FOMONTH
(
@INDATETIME DATETIME
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
SELECT
CONVERT(DATE,DATEADD(DAY,1-DATEPART(DAY,@INDATETIME),@INDATETIME)) AS FOMONTH
;
GO
But that doesn't really "subtract the day of the month from the date" (which would yield the last of the previous month). It subtracts "the day of the month minus 1 from the date". Personally I'd reformat the code slightly because I think it makes it a (very tiny) bit clearer what is being done:
CONVERT(DATE,DATEADD(DAY,-DATEPART(DAY,@INDATETIME) + 1,@INDATETIME))
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 16, 2021 at 7:22 pm
Thank you for the help....
Here is what I finally came up with
DECLARE @startDate DATETIME;
DECLARE @endDate DATETIME;
--DECLARE @dateDiff INT;
SET @startDate = '2018-08-12';
SET @endDate = '2018-08-14';
SELECT TOP 10 [Last Name] AS Name,
@startDate AS StartDate,
@endDate AS EndDate
INTO #temp
FROM [dbo].[pupils]
-- could put a where clause here.....
;
DECLARE @maxdate DATETIME=
(
SELECT MAX([EndDate])
FROM #temp
);
WITH cte
AS (
SELECT Name,
StartDate
-- EndDate
FROM #temp
UNION ALL
SELECT Name,
DATEADD(day, 1, StartDate)
FROM cte
WHERE StartDate < @maxdate)
SELECT *
FROM cte
ORDER BY Name,
StartDate;
DROP TABLE #temp;
Old post, I know but it does use RBAR. Please see the following article for why you must not use such Recursive CTEs to "count" or create sequences.
https://www.sqlservercentral.com/articles/hidden-rbar-counting-with-recursive-ctes
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 46 through 59 (of 59 total)
You must be logged in to reply to this topic. Login to reply