Creating Date In Sql Server 2008

Is there something similar to DATEFROMPARTS(year, month, day) in SQL Server 2008? I want to create a date using the current year and month, but my own day of the month. This needs

Solution 1:

You could use something like this to make your own datetime:

DECLARE@yearINT=2012DECLARE@monthINT=12DECLARE@dayINT=25SELECTCAST(CONVERT(VARCHAR, @year) +'-'+CONVERT(VARCHAR, @month) +'-'+CONVERT(VARCHAR, @day)
 AS DATETIME)

Solution 2:

Using the 3 from your example, you could do this:

dateadd(dd, 3 -1, dateadd(mm, datediff(mm,0, current_timestamp), 0))

It works by finding the number of months since the epoch date, adding those months back to the epoch date, and then adding the desired number of days to that prior result. It sounds complicated, but it's built on what was the canonical way to truncate dates prior to the Date (not DateTime) type added to Sql Server 2008.

You're probably going to see other answers here suggesting building date strings. I urge you to avoid suggestions to use strings. Using strings is likely to be much slower, and there are some potential pitfalls with alternative date collations/formats.

Solution 3:

CREATEFUNCTION  DATEFROMPARTS
(
    @yearint,
    @monthint,
    @dayint
)
RETURNS datetime
ASBEGINdeclare@d datetime

     select@d=CAST(CONVERT(VARCHAR, @year) +'-'+CONVERT(VARCHAR, @month) +'-'+CONVERT(VARCHAR, @day) AS DATETIME)
    RETURN@dEND
GO

Solution 4:

CREATEFUNCTION  DATEFROMPARTS
(
    @yearint,
    @monthint,
    @dayint
)
RETURNS datetime
ASBEGINdeclare@syvarchar(max)
    ,@smvarchar(max)
    ,@sdvarchar(max)
    ;

    set@sy=convert(varchar(max),@year);
    set@sm= (casewhen@month<10then'0'else''end) +convert(varchar(max),@month);
    set@sd= (casewhen@day<10then'0'else''end) +convert(varchar(max),@day);

    RETURNconvert(datetime, @sy+'-'+@sm+'-'+@sd+'T00:00:00.000');
END