GetDateTimeString Function

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Luis Atondo » GetDateTimeString Function

GetDateTimeString Function

Rate Topic

Display Mode

Topic Options

Author

Message

Atd

Posted Friday, July 24, 2009 7:50 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 10:59 AM
Points: 22, Visits: 75

Comments posted to this topic are about the item GetDateTimeString Function

Post #759564

simon.duckett

Posted Friday, July 31, 2009 4:29 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 21, 2012 2:09 AM
Points: 4, Visits: 56

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

Post #762982

simon.duckett

Posted Friday, July 31, 2009 4:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, December 21, 2012 2:09 AM
Points: 4, Visits: 56

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

Post #762988

Ol'SureHand

Posted Friday, July 31, 2009 5:47 AM

Old Hand

Group: General Forum Members
Last Login: Monday, June 10, 2013 1:35 AM
Points: 357, Visits: 706

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)

Post #763015

DCDBA

Posted Friday, July 31, 2009 8:31 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, February 22, 2011 1:34 PM
Points: 20, Visits: 85

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

Post #763141

SQLNightOwl

Posted Friday, July 31, 2009 9:26 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, June 16, 2013 3:33 PM
Points: 186, Visits: 394

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

Post #763194

Atd

Posted Friday, July 31, 2009 11:10 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 21, 2013 10:59 AM
Points: 22, Visits: 75

Tks to all, it's realy better performance with your suggestions

Post #763279

« Prev Topic | Next Topic »

Permissions