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 Tuesday, January 11, 2005 4:55 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

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

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:19 AM
Points: 126, Visits: 104

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-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 37,056, Visits: 31,619
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."

(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 #401153
Posted Friday, September 21, 2007 8:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 5, 2011 12:56 PM
Points: 13, Visits: 67

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.

 

Post #401204
Posted Friday, September 21, 2007 6:46 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 7:25 AM
Points: 138, Visits: 272

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

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

Group: General Forum Members
Last Login: Saturday, June 8, 2013 9:13 AM
Points: 144, Visits: 275
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
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse