November 29, 2004 at 1:13 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysa
December 22, 2004 at 5:12 am
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.
December 22, 2004 at 5:54 am
#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.
December 22, 2004 at 6:30 am
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.
December 22, 2004 at 7:50 am
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.
January 7, 2005 at 1:21 pm
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?
January 7, 2005 at 5:21 pm
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.
January 10, 2005 at 5:56 am
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
January 10, 2005 at 6:00 am
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
January 11, 2005 at 3:38 pm
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
January 11, 2005 at 4:55 pm
0 was there because I was thinking about 2 digit hours. Something like '01:36PM'.
January 11, 2005 at 7:41 pm
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
September 21, 2007 at 2:11 am
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 ?
September 21, 2007 at 2:25 am
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.
September 21, 2007 at 5:14 am
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 26 total)
You must be logged in to reply to this topic. Login to reply