September 24, 2020 at 6:03 pm
Hi,
I have a fn with below code I want to understand the highlighted line. Not sure what is FN doing apart from return the value in DATETIME format.
CREATE FUNCTION DBO.TFNGETDATEONLY ( @InDate DATETIME)
RETURNS DATETIME
BEGIN
RETURN CAST(CONVERT(VARCHAR(10), @InDate, 101) AS DATETIME) -- can you please explain what this line is doing and what is 101?
END
So the above FN is used in SP to SET a variable like SET @CurrentDate=DBO.TFNGETDATEONLY(GETDATE())
September 24, 2020 at 6:34 pm
CONVERT is one of the quintessential functions in SQL Server. Please see the following documentation where you'll also find out what the "101" means... You should bookmark the link if you're going to work with T-SQL. Then try to figure out the answer to your question because it'll teach you a lot.
Functions are the workhorses of any computer language and T-SQL is no exception. With that in mind, take a look at the following and start exploring. You should bookmark this one, as well.
https://docs.microsoft.com/en-us/sql/t-sql/functions/functions?view=sql-server-ver15
Then, find the person that made that function and permanently remove their privs from all of your SQL Servers. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2020 at 6:36 pm
Thank you will look at the links for sure. Thanks again!
September 24, 2020 at 6:38 pm
I did not understand the below in your reply...could you please elaborate. Was something wrong with the function I shared? I did not write it but please let me know.
Then, find the person that made that function and permanently remove their privs from all of your SQL Servers.
September 24, 2020 at 6:56 pm
Haha, that's Jeff firing frozen porkchops in their general direction. His mother smelt of elderberries.
They wrote a function, which just used two other functions (and obfuscated what they did) in order to return just the date. He's not a fan.
There are other ways to get that too, like just stuffing the current date/time into a date variable, using dateadd/datediff to set to midnight, etc. Depends what they're doing with that value once it's returned.
If it's just doing that to get to a midnight stamp so that they can perform comparisons to datetime fields, then there is no need to convert to varchar at all. That's a formatting/prettifying step, and they could accomplish what they need by doing:
DECLARE @getDate date;
SET @getDate = getdate();
*Edit: grammar
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
September 24, 2020 at 7:32 pm
I did not understand the below in your reply...could you please elaborate. Was something wrong with the function I shared? I did not write it but please let me know.
Then, find the person that made that function and permanently remove their privs from all of your SQL Servers.
That's Jeff's way of saying that whoever wrote this code probably doesn't know what they are doing, or didn't when they wrote it. It's a pretty convoluted way of stripping the time off of a datetime to get to midnight.
If this is an indicator of the code that exists in your system, especially when it comes to dates and times, I'm betting that there are many other issues in your systems.
Quick story. The folks in the accounting department casually mentioned to me that when they run the accounts payable or receivable reports for the whole year, the values never match the sum of the values from the monthly reports. It was always higher. In about 4 seconds of looking at the procedures that produced the report, I saw that the original developers messed up the calculations for the ending date. Essentially, the last day of every month was left off the reports. The nature of the business was that 80% of each months activity occurred in the last 5-10 days of every month.
They had been under-reporting revenue for 12 years.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 24, 2020 at 7:32 pm
Jeff - I was reading your article(https://www.red-gate.com/simple-talk/opinion/opinion-pieces/jeff-moden-dba-of-the-day/) and wanted to ask, what are some of the books or resources you felt like are great resouces to master Tsql. I am learning Tsql and please advise me here.
September 24, 2020 at 7:44 pm
I like this one....https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
September 24, 2020 at 8:04 pm
Jeff - I was reading your article(https://www.red-gate.com/simple-talk/opinion/opinion-pieces/jeff-moden-dba-of-the-day/) and wanted to ask, what are some of the books or resources you felt like are great resouces to master Tsql. I am learning Tsql and please advise me here.
You actually have hit 2 of the better resources available - this site and the simple-talk site.
The next best resource - in my opinion - is SQL Server help (BOL) itself. And yes, I understand that is isn't the easiest to navigate but hitting F1 on any part of a SQL statement should bring you to the documentation for that command/function which is generally a very good starting point.
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 24, 2020 at 11:07 pm
Jeff - I was reading your article(https://www.red-gate.com/simple-talk/opinion/opinion-pieces/jeff-moden-dba-of-the-day/) and wanted to ask, what are some of the books or resources you felt like are great resouces to master Tsql. I am learning Tsql and please advise me here.
I taught myself the basics from a very old and now out of print book (and I still have the book). It's very well written with some pretty good basic exercises. The guy that introduced me to the book selected the chapters that I needed to read and none of those chapters included using Cursors or While loops except one and he told me to skip that section.
Here's the Google search for the book...
https://www.google.com/search?client=firefox-b-1-d&q=isbn+1-56205-830-4
Most everything else was gleaned from SQLServerCentral.com and Simple-Talk.com. It's ironic that Red-Gate now owns both.
That's not to say that I didn't visit other sites. A lot of people provided links to other sites and I've read literally hundreds of other articles, not including the ones that I've read here. The biggest learning is frequently found in the discussions on the discussion threads for articles regardless of of which site you visit. I also use to haunt SQLTeams.com quite a bit because there were some good people there, as well.
Like I said in the article you read about me, I've found that reading and answering questions on forums was one of the best teachers for me. No where else will you get such a dizzying array of real world problems nor such a cornucopia of different solutions. Yes, there are some great people with incredible answers but it's equally (and perhaps more) important to read and play with the not-so-good solutions to understand why the good solutions are actually so good.
Above all else, the secret to my personal success has been the learned and self-enhanced ability to make shedloads (frequently, millions of rows) of Random Constrained Data that simulates real world problems instead of working with the proverbial 10 rows that a lot of people use. My take is that if it doesn't work properly and very quickly on a million rows, I need to try again until I can do it.
With the understanding that they provide an introduction rather than a complete set of possibilities (like the million row hierarchical table I included the code for in my "Hierarcchies on Steroids" articles or the CSV generator I built for the Tally-Oh! article on an enhanced splitting technique that has become popular), here are a couple of articles on that subject...
Being able to generate a lot of test data can really help you when you practice and help you learn that milliseconds do really matter.
Of course, not all that glitters is gold. For example, there are a whole lot of "Holy Grail" articles out there that come complete with a test data generator, a test harness, etc, etc, and strong words like claiming something is a "Best Practice" that can do a lot of damage to people's learning. Some are really good but some are really bad. For example, the ones that claim and supposedly prove that various forms of an "XML-based String Splitter" are a "good and fast" method are woefully incorrect because they used really crap methods for making test data that favors (super low cardinality) the XML method.
It's a pain and difficult to do but you have to learn that no matter how many people rave about an article in the discussion that follows an article, the article can still be totally wrong and will bonk you pretty good if you use the techniques from the article. If you see someone that claims to have written a good performance article that uses a Recursive CTE, a While loop, or GO xx to build test data, there's a high chance that you could do a lot better by finding a different article on the subject.
As with all else both in life and T-SQL/SQL Server, "It Depends" prevails and you have to learn what to look for and then practice like a madman (again, forums are a good source of problems to learn how to solve even if you don't want to post your solutions but should so you can get feedback on your solutions).
T-SQL is an "art" and really good T-SQL requires many "Black Arts" because the MS Documentation is going to have all the really good stuff in it. For example, no where in MS Documentation on T-SQL do they teach the importance of use of a Tally or Numbers table. Nowhere in the MS Documentation will you find the word "Pseudo Cursor". And nowhere in the MS Documentation will you find (as examples of what I'm talking about), a performance test on horribly slow FORMAT or a performance test on incremental Recursive CTEs, etc, etc. That's all up to you and the other people, books, and articles.
There's no way to learn it all nor learn a lot quickly. It's going to take patience and a lot of Googling and reading and building test data and writing code to try to solve solutions, etc. If you like T-SQL and databases, be patient... it's definitely worth it.
Sorry for the long winded reply but I'm "seeing a first glimmer" and want to help push you in the right direction if you like this business.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 24, 2020 at 11:47 pm
Take what Jeff is saying to heart. I consider him one of my mentors here on sqlservercentral.com and have known him for over 15 years and finally met him in person in 2018. He can bring to life many interesting and sometimes unique solutions. You should also take the time to find and read his numerous articles on this site.
September 25, 2020 at 5:57 am
Thank you Jeff for showing the right direction. I always had this issue of generating test data and now I have a plan and your teaching is very helpful. I read your reply twice to ensure I do not miss on any points. Thank you so much!
September 25, 2020 at 8:55 am
Thank you Jeff for showing the right direction. I always had this issue of generating test data and now I have a plan and your teaching is very helpful. I read your reply twice to ensure I do not miss on any points. Thank you so much!
It sounds like you might be in a similar position that I was not so very long ago.
<brown nose> Jeff's answers on here have probably contributed more to my skillset and personal development in SQL than any single source. It's worth following him, any thread he comments on will be a school day, even if it's just to tuck an idea in a corner of your brain for the future when you see that "weird" problem, and make you stop and think WWJMD. </brown nose>
There are plenty of others here, where the same applies Grant, Eirikur to name just two. I consider them surrogate SQL mentors in the absence of any real-life ones.
Lurk around these forums and you will start to develop an arsenal of useful techniques.
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?
September 25, 2020 at 2:04 pm
Thank you Jeff for showing the right direction. I always had this issue of generating test data and now I have a plan and your teaching is very helpful. I read your reply twice to ensure I do not miss on any points. Thank you so much!
Thank YOU for taking what I said the right way! Like I said, I see a very real "first glimmer" in your efforts and my hat is off to you for trying. It's not a bad wheel to put your shoulder to and any effort you put in will be seriously magnified in returns that you'll never expect.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 25, 2020 at 2:07 pm
Lynn and David,
Thank you both for the very kind words. I never expect such replies and I'm seriously humbled by them.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply