InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 10:34:18 GMT20http://www.sqlservercentral.com/Forums/Topic759564-1350-1.aspxTks to all, it's realy better performance with your suggestionsFri, 31 Jul 2009 11:10:43 GMTAtdhttp://www.sqlservercentral.com/Forums/Topic759564-1350-1.aspx[quote][b]DCDBA (7/31/2009)[/b][hr]How about this:CREATE FUNCTION dbo.fGetDateTimeString( @dt datetime)RETURNS varchar(12)ASBEGIN RETURN Convert(varchar(8), @dt, 112) + Replace(Convert(varchar(5), @dt, 114), ':', '')END[/quote]DCDBA is the winner.I ran the original code vs the modified code. There are a total of 12 steps and when looking at the execution plan each step takes 8.333% of the execution time (100 / 12).So 8.333 * 11 = 91.663% for the original vs. 8.334% for the modified.It appears the author needs to press F1 and look up the CONVERT funciton.Fri, 31 Jul 2009 09:26:09 GMTSQLNightOwlhttp://www.sqlservercentral.com/Forums/Topic759564-1350-1.aspxHow about this:CREATE FUNCTION dbo.fGetDateTimeString( @dt datetime)RETURNS varchar(12)ASBEGIN RETURN Convert(varchar(8), @dt, 112) + Replace(Convert(varchar(5), @dt, 114), ':', '')ENDFri, 31 Jul 2009 08:31:30 GMTDCDBAhttp://www.sqlservercentral.com/Forums/Topic759564-1350-1.aspxGood work. Most certainly there are cases when a string - or bigint - needs to be constructed this way.Otherwise, of course, just the 112 format mask for CONVERT will produce a string that can be further cast as int.A word of advice about UDF usage (this one being a tempting candidate!): - indiscriminate use of the function will slow down queries and may end up in a limbo during multiple selects in views. The native CONVERT function is preferable (where a YYYYMMDD value is sufficient). - use in stored procs is preferable, as a CONST before processing (avoid using it in-line)Fri, 31 Jul 2009 05:47:12 GMTOl'SureHandhttp://www.sqlservercentral.com/Forums/Topic759564-1350-1.aspxAn Alternative:[code]ALTER FUNCTION [dbo].[GetDateTimeString] ( @inDateTime datetime)RETURNS varchar(12)ASBEGIN DECLARE @DateString varchar(12), @Hours varchar(2), @Minutes varchar(2) SELECT @Hours = CONVERT(varchar(2), DATEPART(hour, @inDateTime)), @Minutes = CONVERT(varchar(2), DATEPART(minute, @inDateTime)), @DateString = CONVERT(varchar(8), @inDateTime, 112) + STUFF('00', 3 - LEN(@Hours), 2, @Hours) + STUFF('00', 3 - LEN(@Minutes), 2, @Minutes) RETURN @DateStringEND[/code]Fri, 31 Jul 2009 04:47:27 GMTsimon.ducketthttp://www.sqlservercentral.com/Forums/Topic759564-1350-1.aspxI would construct a function like this for the same purpose.Not so easy to read but I think it is probably a little more efficient./*DECLARE @DTest NVARCHAR(14)SET @DTest=dbo.GetDateTimeString(getdate())SELECT @DTest as Test,getdate()*/ALTER FUNCTION [dbo].[GetDateTimeString] ( @inDateTime datetime)RETURNS varchar(12)ASBEGIN DECLARE @DateString varchar(12) SELECT @DateString = CONVERT(varchar(8), @inDateTime, 112) + STUFF('00', 3 - LEN(CONVERT(varchar(2), DATEPART(hour, @inDateTime))), 2, CONVERT(varchar(2), DATEPART(hour, @inDateTime))) + STUFF('00', 3 - LEN(CONVERT(varchar(2), DATEPART(minute, @inDateTime))), 2, CONVERT(varchar(2), DATEPART(minute, @inDateTime))) RETURN @DateStringENDFri, 31 Jul 2009 04:29:36 GMTsimon.ducketthttp://www.sqlservercentral.com/Forums/Topic759564-1350-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Date+Time/67549/">GetDateTimeString Function</A>[/B]Fri, 24 Jul 2009 19:50:07 GMTAtd