Working with Datetime

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysa

  • rschaeferhig

    SSCrazy Eights

    Points: 8776

    A very nice utilitarian date formatting function that mimics VB's date format strings is available at http://www.sqlservercentral.com/scripts/contributions/1007.asp

    I highly recommend it for low transaction volume systems that have odd date reformatting requirements rather than reinventing the wheel for each new format.

     

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    #1. This function has a bug (or issue)

    select dbo.fn_FormatDate (getdate(), 'xx:ss') will result 'xx:17'

    It is not a date format. Function required format verification code.

    #2. If every programmer will start writing format every time - it will end up with many errors. We have 5000 - 6000 reports and 200-300 applications. Each Developer developping 1 application every 3-5 weeks. It is more safe to make the predefined formats. Espesially, when number of formats are limited.

    #3. 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.

    Thanks for the responce.

  • rschaeferhig

    SSCrazy Eights

    Points: 8776

    If my post was taken as critical I apologize. I was just trying to offer the script from the library as an example of a flexible way to provide date formatting functionality. The author acknowedges there are issues, but corrections have been posted in the past and are welcome by the author.

    It seems to me (IMHO) that providing a comprehensive date formatting solution that is implemented such that it is available to all developers for all databases is a good way to ensure uniformity of both development techniques and output formatting.

    Again, this was not intended as criticism, just offering an opinion. I rated your article as "good" so that should speak for my opinion.

     

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Thanks for email. In all my articles I am trying to show some ideas and provide with the example of the code (not necessary the best one but working). And always I am glad when a person takes an advice and use it based on the situation. Each situation may post a challenge and may need to be resolved its own way.

    Again, thanks.

     

  • williamhoos@yahoo.com

    Right there with Babe

    Points: 789

    Both are good examples of some scripts.  Does anyone have an addition to the UDF_CONVERTDATE that would add option 7 that would allow returning just the time?  Maybe 7 would return "1:30 PM" and 8 would return "13:30".  Just thought I'd ask before trying to add it myself.

    Does SQL 2005 have some of this built in?

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    In reality, I have about 15 different formats. The one you asking looks:

     

    IF (@dateformat = 8)

     begin

     set @converteddt =

      RIGHT('0' + CAST(DATEPART ( hh , @datevar ) AS VARCHAR(2)), 2) +  ':' +

      RIGHT('0' + CAST(DATEPART ( mi , @datevar ) AS VARCHAR(2)), 2)

      

      set @formatexists = 1

     end

    I don't have #7 but it can be done similar way with CASE statement or conversion of datetime variable to varchar first based on the formatting parameter.

     

    I can make it Monday. I don't have SQL Server installed on my computer now.

     

     

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Formats you need based on your posted message:

    IF (@dateformat = 7)

     begin

     set @converteddt =

      RIGHT('0' + right( convert(varchar, getdate(),100 ) ,7), 7)

      set @formatexists = 1

      end

     

    IF (@dateformat = 8)

     begin

     set @converteddt =

      RIGHT('0' + CAST(DATEPART ( hh , @datevar ) AS VARCHAR(2)), 2) +  ':' +

      RIGHT('0' + CAST(DATEPART ( mi , @datevar ) AS VARCHAR(2)), 2)

      

      set @formatexists = 1

     end

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Oops, need change getdate to @datevar

    IF (@dateformat = 7)

     begin

     set @converteddt =

      RIGHT('0' + right( convert(varchar, @datevar,100 ) ,7), 7)

      set @formatexists = 1

      end

     

    IF (@dateformat = 8)

     begin

     set @converteddt =

      RIGHT('0' + CAST(DATEPART ( hh , @datevar ) AS VARCHAR(2)), 2) +  ':' +

      RIGHT('0' + CAST(DATEPART ( mi , @datevar ) AS VARCHAR(2)), 2)

      

      set @formatexists = 1

     end

  • williamhoos@yahoo.com

    Right there with Babe

    Points: 789

    Thanks for the replies.  That is helpful.  The AM/PM doesn't use a space after the time, so I revised as below.  Not sure why the '0' + was added since it was always removed by the outer function.

     

    IF (@dateformat = 7)

     begin

     set @converteddt =

      STUFF(right( convert(varchar,@datevar,100 ) ,7), 6, 0, ' ')

      set @formatexists = 1

      end

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

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

  • williamhoos@yahoo.com

    Right there with Babe

    Points: 789

    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

    SSC Enthusiast

    Points: 198

    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

    Newbie

    Points: 5

    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

    SSC Enthusiast

    Points: 175

    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!

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply