«
»

The other day I had a need to be creating dates in a SQL Server SQL statement, made from a few different text fields. I could not find a function to create a date from these, so I wrote one (with the help of our in house SQL guru).

CREATE FUNCTION [dbo].[MakeDate]
(
	-- Add the parameters for the function here
	@inputYear int, @inputMonth int, @inputDay int
)
RETURNS smalldatetime
AS
BEGIN
	DECLARE @inputMonthStr varchar(2), @inputDayStr varchar(2), @returnDate smalldatetime

	SET @inputMonthStr = Right('0' + cast(@inputMonth as varchar(2)),2)
	SET @inputDayStr = Right('0' + cast(@inputDay as varchar(2)),2)

	SET @returnDate = CAST( cast(@inputYear as varchar(4)) + @inputMonthStr + @inputDayStr as smalldatetime)

	-- Return the result of the function
	RETURN @returnDate

END

If you are using MySQL, you already have a create date function built in: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_makedate.

This entry was posted on 2 April 2009 at 9:14 pm and is filed under SQL, Web Development. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

2 Comments

  1. Roland Collins says:

    This one will run much faster if you're using it in a batch statement since it avoids the extra variable declarations and string conversions.

    CREATE FUNCTION [dbo].[MakeDate]
    (
        — Add the parameters for the function here
        @year INT, @month INT, @day INT
    )
    RETURNS smalldatetime
    AS
    BEGIN

    RETURN CONVERT(SMALLDATETIME, CONVERT(VARCHAR, @month) + '/' + CONVERT(VARCHAR, @day) + '/' + CONVERT(VARCHAR, @year))

    END

    3 April 2009, 3:24 pm

  2. Ryan Stille says:

    Nice, thanks.

    3 April 2009, 3:28 pm