What is new in Copilot, the new features available
-
Comments posted to this topic are about the item What is new in Copilot, the new features available
-
Heh... ask it how to convert a DATETIME2 value to a DATETIME value in SQL Server. 😉
--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:
Heh... ask it how to convert a DATETIME2 value to a DATETIME value in SQL Server. 😉
I haven't got Copilot, but I thought I'd try it on ChatGPT
https://chatgpt.com/share/1cdef498-9b6d-4e4c-be44-da24b942c921
-
Deleted duplicate posting
- Jonathan AC Roberts wrote:Jeff Moden wrote:
Heh... ask it how to convert a DATETIME2 value to a DATETIME value in SQL Server. 😉
I haven't got Copilot, but I thought I'd try it on ChatGPT
https://chatgpt.com/share/1cdef498-9b6d-4e4c-be44-da24b942c921
So... not only does it provide an incorrect answer (it can silently fail by rounding all the way up to even the next year), it also says that's the way I do it (those that know will tell you that I'd never make such a text based mistake).
Here's just one of the many proofs... Both the direct conversion from DATETIME2 to DATETIME and the faulty answer above produce the "silent failures" that I'm talking about". Here are the results my quick little test.
DATETIME2 DATETIME_Bad CONVERT (Also Bad)
--------------------------- ----------------------- -----------------------
2022-12-31 23:59:59.9989999 2023-01-01 00:00:00.000 2022-12-31 23:59:59.997
2022-12-31 23:59:59.9990000 2023-01-01 00:00:00.000 2023-01-01 00:00:00.000It does have the very human traits of being confidently but seriously incorrect and then lying about it down pat.
Perhaps I should sue for slander. 😉
--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:Jonathan AC Roberts wrote:Jeff Moden wrote:
Heh... ask it how to convert a DATETIME2 value to a DATETIME value in SQL Server. 😉
I haven't got Copilot, but I thought I'd try it on ChatGPT
https://chatgpt.com/share/1cdef498-9b6d-4e4c-be44-da24b942c921
So... not only does it provide an incorrect answer (it can silently fail by rounding all the way up to even the next year), it also says that's the way I do it (those that know will tell you that I'd never make such a text based mistake).
Here's just one of the many proofs... Both the direct conversion from DATETIME2 to DATETIME and the faulty answer above produce the "silent failures" that I'm talking about". Here are the results my quick little test.
DATETIME2 DATETIME_Bad CONVERT (Also Bad)
--------------------------- ----------------------- -----------------------
2022-12-31 23:59:59.9989999 2023-01-01 00:00:00.000 2022-12-31 23:59:59.997
2022-12-31 23:59:59.9990000 2023-01-01 00:00:00.000 2023-01-01 00:00:00.000Perhaps I should sue for slander.
Yes, well ChatGPT generates its responses one word at a time, using each preceding word and the context of the conversation, including its own partially completed sentences, to predict the next word. This process involves selecting one of the most probable word to continue the text, based on extensive training data.
I'm amazed it can produce anything meaningful at all.
- Jonathan AC Roberts wrote:
Yes, well ChatGPT generates its responses one word at a time, using each preceding word and the context of the conversation, including its own partially completed sentences, to predict the next word. This process involves selecting one of the most probable word to continue the text, based on extensive training data.
I'm amazed it can produce anything meaningful at all.
I agree with being amazed that it can produce anything at all but my goal here is that, just like listening to human "experts" that are frequently "Confidently Incorrect" (especially on this subject), people MUST understand that AI is just as incorrect. The really big issue is that it does work in many cases but the person asking it such a question doesn't have enough knowledge to even suspect in may be incorrect for things likes this or they wouldn't have needed to ask the question to begin with.
Again, when it comes to code, especially SQL code (or so it seems), all of these AI sites seem to be expensive bullshit grinders operating from "general consensus" and we all know how wrong the "general consensus" frequently is.
So... how is this code "dangerous"? Think "late payments and other deadlines", just for starters.
--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:Jonathan AC Roberts wrote:
Yes, well ChatGPT generates its responses one word at a time, using each preceding word and the context of the conversation, including its own partially completed sentences, to predict the next word. This process involves selecting one of the most probable word to continue the text, based on extensive training data.
I'm amazed it can produce anything meaningful at all.
I agree with being amazed that it can produce anything at all but my goal here is that, just like listening to human "experts" that are frequently "Confidently Incorrect" (especially on this subject), people MUST understand that AI is just as incorrect. The really big issue is that it does work in many cases but the person asking it such a question doesn't have enough knowledge to even suspect in may be incorrect for things likes this or they wouldn't have needed to ask the question to begin with.
Again, when it comes to code, especially SQL code (or so it seems), all of these AI sites seem to be expensive bullshit grinders operating from "general consensus" and we all know how wrong the "general consensus" frequently is.
So... how is this code "dangerous"? Think "late payments and other deadlines", just for starters.
Just out of interest, what is the method you use for converting a DATETIME2 value to a DATETIME?
- Jonathan AC Roberts wrote:
Just out of interest, what is the method you use for converting a DATETIME2 value to a DATETIME?
I thought I had a good one but then found a "silent failure" so I'm trying something else and I'm not yet done with it. I actually owe Steve Jones an article on the subject.
--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) - Jonathan AC Roberts wrote:Jeff Moden wrote:
Here's just one of the many proofs... Both the direct conversion from DATETIME2 to DATETIME and the faulty answer above produce the "silent failures" that I'm talking about". Here are the results my quick little test.
DATETIME2 DATETIME_Bad CONVERT (Also Bad)
--------------------------- ----------------------- -----------------------
2022-12-31 23:59:59.9989999 2023-01-01 00:00:00.000 2022-12-31 23:59:59.997
2022-12-31 23:59:59.9990000 2023-01-01 00:00:00.000 2023-01-01 00:00:00.000What are you expecting it to convert '2022-12-31 23:59:59.9989999' and '2022-12-31 23:59:59.9990000' to?
I think Datetime has a minimum granularity of about 3 ms.
- Jonathan AC Roberts wrote:Jonathan AC Roberts wrote:Jeff Moden wrote:
Here's just one of the many proofs... Both the direct conversion from DATETIME2 to DATETIME and the faulty answer above produce the "silent failures" that I'm talking about". Here are the results my quick little test.
DATETIME2 DATETIME_Bad CONVERT (Also Bad)
--------------------------- ----------------------- -----------------------
2022-12-31 23:59:59.9989999 2023-01-01 00:00:00.000 2022-12-31 23:59:59.997
2022-12-31 23:59:59.9990000 2023-01-01 00:00:00.000 2023-01-01 00:00:00.000What are you expecting it to convert '2022-12-31 23:59:59.9989999' and '2022-12-31 23:59:59.9990000' to?
I think Datetime has a minimum granularity of about 3 ms.
Heh... dagnabbit. Although the dates I posted above are posted correctly, I didn't post the numbers I actually wanted to show. Oh well... gotta have something left to explain in the article.
To answer your question the, here are the dates from above that I'm expecting.
DATETIME2 DATETIME_Bad CORRECT DATETIME
--------------------------- ----------------------- -----------------------
2022-12-31 23:59:59.9989999 2023-01-01 00:00:00.000 2022-12-31 23:59:59.997
2022-12-31 23:59:59.9990000 2023-01-01 00:00:00.000 2022-12-31 23:59:59.997Also, DATETIME has a resolution of 1/300th of a second. That's roughly 3.3 milliseconds but not the same, especially when it comes to DATETIME2 and other conversions. Even after that, they round those values to the nearest millisecond, which really puts the screws to conversions from DATETIME2 to DATETIME and even in working with just DATETIME itself. In the end, although the milliseconds will always end with 0, 3, or 7, 0 occurs only 2 out of 10 times, 3 occurs 3 out of 10 times, 7 occurs 4 out of 10 times, and (IMHO) bad rounding occurs the remainder of the times. The last 2 categories cover half of all the values that can be converted.
I'll have all of that in the article and this thread was a really good reminder that I needed to work on it.
8333400 nano-seconds plays an import roll in the simple conversions. It also turns out that MS apparently made a change to the DATETIME datatype in 2016 and has been causing it to have "silent failures" ever since. Almost now one is aware of the problem because the failures are "silent". It's probably not a big problem because it occurs only on certain edge cases but just one failure over any span of time is still a failure that could occur again.
Anyway, here's the formula. Like I said, I'm still testing it to be absolutely sure so (although it's looking sure especially mathematically), if you use it, use it with a bit of caution. "DATETIME2_Orig" can be a variable or a column of the DATETIME2 datatype. It works perfectly with a resolution of (7). Mathematically, I don't see issues with other resolutions but I'm compelled to test. It's just my nature to help prevent finding "oolies" and "crud bursts" the hard way. 😀
Convert2DateTime = CONVERT(DATETIME
,IIF(DATEPART(ns,DATETIME2_Orig)%10000000 < 8333400
,ca1.DT2
,DATEADD(ms,-2,DATETIME2_Orig)))--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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply