SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working with Datetime


Working with Datetime

Author
Message
Leo Peysakhovich
Leo Peysakhovich
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 350
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.Wink

Hmm, I am unable as well. It is the question to the site editors.



R2ro
R2ro
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 134
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
Doug Bishop
Doug Bishop
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 67
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 :-)
Leo Peysakhovich
Leo Peysakhovich
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 350
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.



Leo Peysakhovich
Leo Peysakhovich
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 350
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.



Leo Peysakhovich
Leo Peysakhovich
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 350
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



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85124 Visits: 41077
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search