SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working with Datetime


Working with Datetime

Author
Message
Leo Peysakhovich
Leo Peysakhovich
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1322 Visits: 371
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysa



rschaeferhig
rschaeferhig
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2995 Visits: 119

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.





I reject your reality and substitute one of my own. - Adam Savage-Mythbuster
Leo Peysakhovich
Leo Peysakhovich
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1322 Visits: 371

#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
rschaeferhig
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2995 Visits: 119

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.





I reject your reality and substitute one of my own. - Adam Savage-Mythbuster
Leo Peysakhovich
Leo Peysakhovich
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1322 Visits: 371

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
williamhoos@yahoo.com
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 1

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
Leo Peysakhovich
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1322 Visits: 371

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
Leo Peysakhovich
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1322 Visits: 371

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
Leo Peysakhovich
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1322 Visits: 371

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
williamhoos@yahoo.com
SSC Veteran
SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)SSC Veteran (241 reputation)

Group: General Forum Members
Points: 241 Visits: 1

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





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