Creating date in SQL Server 2008

Question

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 to be done in one line in order to be used in a computed column formula.

For Example (I'm not sure if it works because I do not have SQL Server 2012):

DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 3)

Is there a way to do this in SQL Server 2008?

DATEFROMPARTS Seems only available in SQL Server 2012 (link)

Diverge

possible duplicate of Create a date with T-SQL – Claudeclaudel

I saw that answer, can that be used inside a computed column formula? – Diverge

@Diverge - A persisted or non persisted computed column? – Ragtime

@MartinSmith non persisted – Diverge

Answer 1

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.

Fez

@MartinSmith Thanks, fixed :o Forgot for a moment that I'd be adding the days to a 1 instead of a 0. – Fez

There seems to be a syntax error somewhere, I don't see a syntax error after the edits that have been made. Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'dd'. – Diverge

@Diverge I was also missing a closing parentheses at one point. If I put a select in front of what's there now and paste it directly into a query window in Management Studio, I get 2013-10-03 00:00:00.000 if I run it right now. – Fez

Oh okay! Thanks that was dumb of me. I forgot the SELECT. This is exactly what I am looking for. – Diverge

Answer 2

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

DECLARE @year INT = 2012
DECLARE @month INT = 12
DECLARE @day INT = 25

SELECT CAST(CONVERT(VARCHAR, @year) + '-' + CONVERT(VARCHAR, @month) + '-' + CONVERT(VARCHAR, @day)
 AS DATETIME)

Dysteleology

It would probably be less error-prone to convert the year to CHAR(4) and the month and day to CHAR(2). In your example you have them hardcoded but in normal use those could be coming from somewhere else and it could cause problems if these parameters weren't the expected length. – Egypt

Answer 3

CREATE FUNCTION  DATEFROMPARTS
(
    @year int,
    @month int,
    @day int
)
RETURNS datetime
AS
BEGIN

     declare @d datetime

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

END
GO

Hammad

Ran into this today with a client on an old version of sql and this worked like a champ! – Avner

Answer 4

Based in Joel and Alejandro's answer, I would suggest this user function

CREATE FUNCTION  MyDATEFROMPARTS
(
    @year int,
    @month int,
    @day int
)
RETURNS datetime
AS
BEGIN

    RETURN  select @d = dateadd(dd,@day-1,dateadd(mm,@month-1, dateadd(yyyy,@year-1900, 0)))

END;

Explaning the trick : in SQL Server, date 0 is 1/1/1900, so the first dateadd ( dateadd(yyyy,@year-1900, 0) ) results in the first day of the year you want, after that you add the month quantity : dateadd(mm,@month-1, ... At last, you add the number of days.

To use a user function remember to add the schema :

select dbo.MyDATEFROMPARTS(1854,8,3)

You can also extend the function adding hours, minutes and seconds :-)

Lethargy

Your answer describes the general algorithm to use, which is lacking in the accepted answer. I used your algorithm. To answer the question, though, you should provide an exemple using the current month and current year. May I suggest you first define the general algorithm to use (without the scalar function), then an exemple answer the question and finally the definition and use of the scalar function (even though I wouldn't recommand it, since it can cause a performance drop) – Jordanna

Answer 5

CREATE FUNCTION  DATEFROMPARTS
(
    @year int,
    @month int,
    @day int
)
RETURNS datetime
AS
BEGIN

    declare 
    @sy varchar(max)
    ,@sm varchar(max)
    ,@sd varchar(max)
    ;

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

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

Thickhead

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Harvey

Answer 6

International Coding - YYYY-DD-MM and YYYY-MM-DD is supported

select @d = convert(datetime, CONVERT(VARCHAR, @year) 
           + '-' + CONVERT(VARCHAR, @month) + '-' + CONVERT(VARCHAR, @day),102)

RETURN  @d

Kenwood