Meet the new Google AI Assistant Gemini
-
Comments posted to this topic are about the item Meet the new Google AI Assistant Gemini
-
The two statements in the chapter More details of JOINs are equal.
And both are wrong, since you need to put the SUM(o.Amount) > 1000 into the HAVING and not the WHERE condition
God is real, unless declared integer.
-
And yet it's still absolutely stupid about certain "simple" things... In the example below, this is NOT the right way to solve the given problem. Even the note in the suggested warning won't catch the "Silent Failure" that can change the date not just to the next second but next minute, hour, day, week (inherently), month, quarter (inherently), and even year.
And this is just one of many "simple" but "non-basic" problems all the different flavors of AI has failed to display a correct answer for. When it comes to code, it is simply not trustworthy. People say things like "We'll... you have to know a little about what you're asking", which is absolutely contrary to the reason why newbies and even some supposed "experts" might use it.
Of course, it's not the fault of AI... it's "just" a consensus engine and the general consensus of such solutions out there is also terrible.
Especially with but not limited to AI, always remember that "Half of all that is written is wrong and the other half is usually written in such a fashion that it's not easy to tell".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
And yet it's still absolutely stupid about certain "simple" things... In the example below, this is NOT the right way to solve the given problem. Even the note in the suggested warning won't catch the "Silent Failure" that can change the date not just to the next second but next minute, hour, day, week (inherently), month, quarter (inherently), and even year.
And this is just one of many "simple" but "non-basic" problems all the different flavors of AI has failed to display a correct answer for. When it comes to code, it is simply not trustworthy. People say things like "We'll... you have to know a little about what you're asking", which is absolutely contrary to the reason why newbies and even some supposed "experts" might use it.
Of course, it's not the fault of AI... it's "just" a consensus engine and the general consensus of such solutions out there is also terrible.
Especially with but not limited to AI, always remember that "Half of all that is written is wrong and the other half is usually written in such a fashion that it's not easy to tell".
What is the correct answer?
-
the problem is, that DATETIME is not an exact datatype (similar to float vs. decimal). DATETIME suppots only about 0.003 accuracy, so a DATETIME2 = '20240708 23:59:59.998' would be converted to '20240709 00:00:00' since .997 is the last valid time interval in a second of DATETIME.
To be honest I hadn't to face this problem, the most DATETIME values I face are uncritical as some timestamps. Maybe the most accurate was is to split / recombine the value by using the DATETIMEFROMPARTS() function and using rounding down the milliseconds to new_ms = org_ms - ((org_ms - 1) % 3), so for example 998 - ((998 - 1) % 3) = 998 - (997 %3) = 998 - 1 = 997, but there may be still some gotchas.
I think here will the "and the other half is usually written in such a fashion that it's not easy to tell" fit perfectly 🙂 And we didn't even started to talk about all those nasty time zone stuff.
God is real, unless declared integer.
- Jonathan AC Roberts wrote:Jeff Moden wrote:
And yet it's still absolutely stupid about certain "simple" things... In the example below, this is NOT the right way to solve the given problem. Even the note in the suggested warning won't catch the "Silent Failure" that can change the date not just to the next second but next minute, hour, day, week (inherently), month, quarter (inherently), and even year.
And this is just one of many "simple" but "non-basic" problems all the different flavors of AI has failed to display a correct answer for. When it comes to code, it is simply not trustworthy. People say things like "We'll... you have to know a little about what you're asking", which is absolutely contrary to the reason why newbies and even some supposed "experts" might use it.
Of course, it's not the fault of AI... it's "just" a consensus engine and the general consensus of such solutions out there is also terrible.
Especially with but not limited to AI, always remember that "Half of all that is written is wrong and the other half is usually written in such a fashion that it's not easy to tell".
What is the correct answer?
Here's one way. It short circuits for performance. The number 8333400 is 100 ns less than one half of 1/300th of a second, which is the actual resolution of the DATETIME datatype. That evaluation is important because ANY reduction in resolution in a DATETIME2 can result in rounding up, for any millisecond value that ends with 8.333400 ms or greater... and it's not just at the end of the day. The incorrect method will round up to the next temporal range (10's of ms, 100's of ms, seconds, minutes, hours, days, weeks (inherent), months, quarters (inherent), and even years.
I'm NOT claiming it's the fastest method... I just couldn't think of another method that I knew wouldn't be slower than this method.
I also haven't finished testing to ensure that lower resolutions of DATETIME2 aren't affected. The ranges I have tested worked.
CREATE FUNCTION dbo.ConvertToDATETIME
/************************************************************************************************
Purpose:
Given any combination of DATE and/or TIME that can be implicitly converted to a DATETIME2(7),
return the DATETIME without the improper rounding up when the units position of the milli-
seconds resolves to "9".
-------------------------------------------------------------------------------------------------
Usage:
--===== Usage against a table column
SELECT ctd.ConvertToDATETIME
FROM SomeSchema.SomeTable st
CROSS APPLY dbo.ConvertToDATETIME(st.SomeDateTime2)ctd
;
--===== Usage against a scalar variable
SELECT ConvertToDATETIME
FROM dbo.ConvertToDATETIME(@SomeVariable)
;
-------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 02 Jun 2024 - Jeff Moden
- Unit testing and initial release.
************************************************************************************************/ (@SomeDateTime2 DATETIME2(7))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT ConvertToDATETIME = CONVERT(DATETIME --This works correctly
,IIF(DATEPART(ns,@SomeDateTime2)%10000000 < 8333400
,@SomeDateTime2
,DATEADD(ms,-2,@SomeDateTime2)))
;
GO--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply