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

GetDateTimeString Function Expand / Collapse
Author
Message
Posted Friday, July 24, 2009 7:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, July 31, 2009 4:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 13, 2014 2:39 PM
Points: 10, Visits: 60
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
Posted Friday, July 31, 2009 4:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, November 13, 2014 2:39 PM
Points: 10, Visits: 60
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
Posted Friday, July 31, 2009 5:47 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, November 2, 2014 7:52 PM
Points: 371, Visits: 717
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
Posted Friday, July 31, 2009 8:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Friday, July 31, 2009 9:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:14 PM
Points: 201, Visits: 406
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
Posted Friday, July 31, 2009 11:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse