Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working with Datetime


Working with Datetime

Author
Message
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 339

0 was there because I was thinking about 2 digit hours. Something like '01:36PM'. 





williamhoos@yahoo.com
williamhoos@yahoo.com
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
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





Frédéric van der Plancke
Frédéric van der Plancke
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 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 ?
Rob Hawkins
Rob Hawkins
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.


AlexP-220494
AlexP-220494
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 163

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!


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44871 Visits: 39850
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Calvin E. Wrightsman
Calvin E. Wrightsman
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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


Doug Bishop
Doug Bishop
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.

 


Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)SSC-Enthusiastic (161 reputation)

Group: General Forum Members
Points: 161 Visits: 339

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.

 





eletuw
eletuw
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
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.Wink



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