July 24, 2009 at 7:50 pm
Comments posted to this topic are about the item GetDateTimeString Function
July 31, 2009 at 4:29 am
I 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)
AS
BEGIN
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 @DateString
END
July 31, 2009 at 4:47 am
An Alternative:
ALTER FUNCTION [dbo].[GetDateTimeString]
(
@inDateTime datetime
)
RETURNS varchar(12)
AS
BEGIN
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 @DateString
END
July 31, 2009 at 5:47 am
Good 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)
July 31, 2009 at 9:26 am
DCDBA (7/31/2009)
How about this:CREATE FUNCTION dbo.fGetDateTimeString
(
@dt datetime
)
RETURNS varchar(12)
AS
BEGIN
RETURN Convert(varchar(8), @dt, 112) + Replace(Convert(varchar(5), @dt, 114), ':', '')
END
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.
--Paul Hunter
July 31, 2009 at 11:10 am
Tks to all, it's realy better performance with your suggestions
May 24, 2016 at 7:07 am
Thanks for the script.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply