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 Monday, November 29, 2004 1:13 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/lPeysa


Post #148436
Posted Wednesday, December 22, 2004 5:12 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 14, 2011 12:03 PM
Points: 613, 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
Post #152141
Posted Wednesday, December 22, 2004 5:54 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281

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




Post #152144
Posted Wednesday, December 22, 2004 6:30 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, December 14, 2011 12:03 PM
Points: 613, 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
Post #152148
Posted Wednesday, December 22, 2004 7:50 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281

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.

 




Post #152168
Posted Friday, January 7, 2005 1:21 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

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?




Post #154411
Posted Friday, January 7, 2005 5:21 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281

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.

 

 




Post #154445
Posted Monday, January 10, 2005 5:56 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281

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




Post #154545
Posted Monday, January 10, 2005 6:00 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Friday, December 12, 2014 8:51 AM
Points: 138, Visits: 281

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




Post #154546
Posted Tuesday, January 11, 2005 3:38 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

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




Post #154905
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse