Working with Datetime
-
I agree with Rob and Alex... I'd also like to add that using something that you know "could" fail but hasn't so far (UDF_ID function) is a bit like sitting under the sword of Damocles.
--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) -
A much simpler way to always display 2-byte days and months is: select convert(char(10),getdate(),101) The 101 style will always give you mm/dd/yyyy
-
First of all, what is wrong with using LEFT(DATENAME(mm, date), 3) to get the month abbreviation. It is a WHOLE lot easier that several CASE statements with 12 WHEN/THEN pairs.
I agree with AlexP about using CONVERT when possible. For example CONVERT(CHAR(10), GETDATE(), 101) will return mm/dd/yyyy and strip off the time.
In an earlier post you stated, "I am not pretending to get the best solutions - the article just shows some WAYS of datatime data type usage. It is like to say that the person who shows how engine is working is not presenting the best engine design. This is not the purpose of the article."
Very few people can guarentee that their way of doing something is the best solution, unless thereis only one way of doing it. In programming, that is almost never the case. If I wanted to write a function to add two integers that was passed to it and return the sum, I could write:
CREATE FUNCTION SumNumbers(@N1 INT, @N2 INT) RETURNS INT AS
BEGIN
DECLARE @FirstNumber INT, @SecondNumber INT, @Result INT
SET @FirstNumber = @N1
SET @SeocndNumber = @N2
@Result = @FirstNumber + @SecondNumber
RETURN @Result
END
and say, "I am not pretending to get the best solutions." NO KIDDING! There is a LOT of unecessary overhead here. The preceding can be reduced to:
CREATE FUNCTION SumNumbers(@N1 INT, @N2 INT) RETURNS INT AS
BEGIN
RETURN @N1 + @N2
END
In day to day programming, with time-crunches and multi-tasking and deadlines, we may not always see the most eficient way of doing something--one of the reasons for peer reviews and code walktroughs. But for sitting down and writing an article for publication, that dozens, hundreds or even thousands of people may put into use, affecting dozens, hundreds or thousnads more people, I would think you would take some time to make sure the solutions you have provided are as efficient as they can be made, at least with the knowledge you have at the time.
-
I think sometime, thorough reading is required to see the ideas.
"I am not pretending to get the best solutions - the article just shows some WAYS of datatime data type usage. It is like to say that the person who shows how engine is working is not presenting the best engine design. This is not the purpose of the article."
“Here are some examples of using datetime data type. A transaction/modification very often requires some unique identifier. In light-load transactional systems (1-2 transactions per second) you can produce a unique id based on the timing characteristics of the transaction itself. I use this method for years and have no problems.”
And this method used by company for 4 years and has no issues in our environment.
“I agree with AlexP about using CONVERT when possible. For example CONVERT(CHAR(10), GETDATE(), 101) will return mm/dd/yyyy and strip off the time.”
Please check the variety of formats and let me know if CONVERT function can provide me with '23FEB04' , '20040223154524', or only seconds and milliseconds ‘23005’. And this is the set of formats our company has to deal with. Our apps/users required 10-12 strange formats that are not the formats of CONVERT or any other Microsoft functions. And this function provides universal set of formats for all applications. I set only 6 as example. Some of them are presented with CONVERT function.
-- @dateformat = 1 Format: '05-SEP-2003'-- @dateformat = 2 Format: '05SEP2003'-- @dateformat = 3 Format: '15-Sep-2003 15:23:23'- dd-mon-yyyy hours:minutes:sec-- @dateformat = 4 Format: '23FEB04'-- @dateformat = 5 Format: '20040223154524' -- yyyymmddhhmiss
-- @dateformat =6 Format: '03-Mar-2004 10:11 PM' dd-mmm-yyyy hh:nn am/pm
--***********************************************************
The next function has been developed to determine previous, closest, or next date from the given date.
Please let me know about Microsoft created function with above functionalities. If we are talking about implementation then this is different case. Implementation can be done many ways.
-
I am unable to see the code posted on this article. What is the problem with the web page? I clicked on the link for the txt file and all I get is some error messgae about tags.;)
-
eletuw (23/09/2007)
I am unable to see the code posted on this article. What is the problem with the web page? I clicked on the link for the txt file and all I get is some error messgae about tags.;)Hmm, I am unable as well. It is the question to the site editors.
-
One other minor cautionary note... For those of us that deal with Daylight Saving Time (or other arbitrary time shifts), the UDF_UTCDATE function will only work if the @dt parameter submitted is in the same time zone (i.e. Standard or Daylight Saving).
In other words, if executed today in the Eastern (USA) time zone:
dbo.UDF_UTCDATE('03/11/2006 01:00', getdate(), getutcdate()) will be incorrect. It should return 06:00
dbo.UDF_UTCDATE('03/11/2006 03:00', getdate(), getutcdate()) will be correct.
However, executing the same on November 4th will yield the opposite validity.
FWIW,
Art
-
Leo Peysakhovich:
"In light-load transactional systems (1-2 transactions per second) you can produce a unique id based on the timing characteristics of the transaction itself. I use this method for years and have no problems.
"And this method used by company for 4 years and has no issues in our environment."
Jeff Moden:
"I agree with Rob and Alex... I'd also like to add that using something that you know "could" fail but hasn't so far (UDF_ID function) is a bit like sitting under the sword of Damocles."
I have to agree with Jeff on this one. What is light load today is 50-users banging away 20 transactions/second tomorrow, and lord knows what next month. I've been a programmer for over 25-years. I've worked on PC platforms with VB and other languages for over 10 of that. I have a class I wrote very early on in my client-server days. It has been revised once, when we went from RDO to ADO. It has been redesigned once for ADO.NET. Other than that, it is in place in almost a dozen locations I have worked at (I'm a contracted resource). It does ALL data handling. It opens connections, returns datasets, sets parameters, executes stored procedures, disconnects datasets, adds, updates, deletes rows from tables...
Good designers not only take into account what is happening today, but what might happen next week or next month or next year. I don't want to be out there redesigning my code every 4-months because the user made a process change that was within my ability to have anticipated. Look at the fiasco the Y2K problem caused.
I wouldn't code a program to determine if a year was a leap year just by determining if the year was evenly divisible by 4. Because 2100 is not a leep year. You might think, "So what, I'm sure not going to be alive in 2100, and besides, my program probably won't even be running in 2100."
As a teacher you do not want to be presenting bad examples. I don't want to provide someone code, a methodology, a concept, and say, "this will work as long as... and it is not an even numbered day of an odd month." It should work for all scenarios. You could use a BIT field as a primary key as long as you never have more than two records in the table 🙂
-
Your reaction is true and is not true at the same time. Leap year should have two deviders - 100 and 4 (known fact)
We talking about balance between practice and pure theory and if there are some differences on case by case bases. I am not advocating for every system to do it this way. But for some cases it is very good and practically resonable.
Based on your answer, all systems must be overingeneered to accomodate all conditions regardless what is reality. And based on your ideas Microsoft should never release Windows 3.1 because 2 -3 years later new conditions will require release Windows 95, 98, 2000.
If the company has 50 users you should not design enterprise level system. Design should allow easily adjust changes. If, in my case, assignment of PK the way I am done will start failing, I can change the mechanism inside build-in function. And no other changes will be required. This is called flexible design. And If for 30 years company has 1-2 transactions per minute I could not imagine 10000% grows for another 20-30 years. If it will be the case, most likely company will redesign every existing system.
-
Your reaction is true and is not true at the same time. Leap year should have two dividers - 100 and 4 (known fact)
We talking about balance between practice and pure theory and if there are some differences on case by case bases. I am not advocating for every system to do it this way. But for some cases it is very good and practically reasonable.
Based on your answer, all systems must be overengineered to accommodate all conditions regardless what is reality. And based on your ideas Microsoft should never release Windows 3.1 because 2 -3 years later new conditions will require release Windows 95, 98, 2000.
If the company has 50 users you should not design enterprize level system. Design should allow easily adjust changes. If, in my case, assignment of PK the way I am done will start failing, I can change the mechanism inside build-in function. And no other changes will be required. This is called flexible design. And If for 30 years company has 1-2 transactions per minute I could not imagine 10000% grows for another 20-30 years. If it will be the case, most likely company will redesign every existing system.
-
Your reaction is true and is not true at the same time. Leap year should have two dividers - 100 and 4 (known fact)
We talking about balance between practice and pure theory and if there are some differences on case by case bases. I am not advocating for every system to do it this way. But for some cases it is very good and practically reasonable.
Based on your answer, all systems must be overengineered to accommodate all conditions regardless what is reality. And based on your ideas Microsoft should never release Windows 3.1 because 2 -3 years later new conditions will require release Windows 95, 98, 2000.
If the company has 50 users you should not design enterprize level system. Design should allow easily adjust changes. If, in my case, assignment of PK the way I am done will start failing, I can change the mechanism inside build-in function. And no other changes will be required. This is called flexible design. And If for 30 years company has 1-2 transactions per minute I could not imagine 10000% grows for another 20-30 years. If it will be the case, most likely company will redesign every existing system
-
Heh... sorry... just over a year late on this one...
Leap year calculations don't need to be complex in SQL Server...
ISDATE(STR(@Year,4)+'0229')
--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 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply