Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Working with Datetime Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, January 11, 2005 4:55 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 16, 2016 6:05 AM Points: 145, Visits: 335
 0 was there because I was thinking about 2 digit hours. Something like '01:36PM'.
Post #154915
 Posted Tuesday, January 11, 2005 7:41 PM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, June 27, 2006 12:41 PM Points: 29, Visits: 1
 I realized that after I posted.  The problem is that for the 1 digit hours, the right(,7) selects the space between the year and the time so you get '0 5:00PM'.There is probably a better solution, but I think the earlier posting works.  Thanks for giving me something to build on. Willy
Post #154928
 Posted Friday, September 21, 2007 2:11 AM
 Valued Member Group: General Forum Members Last Login: Thursday, May 20, 2010 10:04 AM Points: 54, Visits: 38
 Just to point out that datetime values are NOT precise to the millisecond. Even though MS docs state that "The other 4 bytes store the time of day represented as the number of milliseconds after midnight" they also state that a datetime value represents:"Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds" (I guess they mean the number of ms always end with 0, 3 or 7, so precision is exactly 1/300 s up to rounding the last ms.)Personal experience shows that the second statement is true. (I've been bitten by that !) But then, the first statement should be false, otherwise how to explain the precision loss between storage and exploitation ?
Post #401037
 Posted Friday, September 21, 2007 2:25 AM
 Forum Newbie Group: General Forum Members Last Login: Thursday, December 2, 2010 2:55 PM Points: 3, Visits: 19
 At the risk of being flamed(!) I must say that I think trying to handle date time (or any) textual formatting for display purposes within the database level is a bit bonkers!Presentation should be handled at the application level, in a language suited to doing so (.net, java, whatever) that supports localisation and cultural settings, it can then be done transparently and easily - without the need for tricks and tips in the sql.Just my thoughts - no offense intended.
Post #401043
 Posted Friday, September 21, 2007 5:14 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Friday, November 11, 2016 3:33 AM Points: 149, Visits: 162
 What's wrong with the CONVERT-function??? I never needed a date format that was not readily available through this function. It's also much much quicker than this CASTing and concatenating.Albeit that this article gives a nice basic insight on how to convert, concatenate and prefix, formatting dates this way is not something I'd recommend: use the build-in CONVERT-function with three parameters!
Post #401078
 Posted Friday, September 21, 2007 7:41 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:17 AM Points: 42,051, Visits: 39,438
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #401153
 Posted Friday, September 21, 2007 8:47 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, September 13, 2010 1:05 PM Points: 2, Visits: 11
 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
Post #401198
 Posted Friday, September 21, 2007 8:49 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, April 5, 2011 12:56 PM Points: 13, Visits: 67
Post #401204
 Posted Friday, September 21, 2007 6:46 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Wednesday, November 16, 2016 6:05 AM Points: 145, Visits: 335
 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.
Post #401366
 Posted Sunday, September 23, 2007 7:05 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, February 2, 2015 5:31 AM Points: 144, Visits: 282
 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.;)
Post #401568

 Permissions