Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Working with Datetime Expand / Collapse
Author
Message
Posted Sunday, September 23, 2007 11:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:33 AM
Points: 138, Visits: 280
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.


Post #401678
Posted Tuesday, September 25, 2007 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 7, 2012 10:05 AM
Points: 165, 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
Post #402634
Posted Tuesday, September 25, 2007 10:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 5, 2011 12:56 PM
Points: 13, 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
Post #402658
Posted Saturday, September 29, 2007 9:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:33 AM
Points: 138, Visits: 280
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.



Post #404573
Posted Saturday, September 29, 2007 9:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:33 AM
Points: 138, Visits: 280
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.



Post #404575
Posted Saturday, September 29, 2007 9:41 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 7:33 AM
Points: 138, Visits: 280
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



Post #404577
Posted Tuesday, December 2, 2008 11:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 35,549, Visits: 32,145
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #612293
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse