Setting date/time in SQL using int values?

Question

I have the following integer type values in a SQL script: @year, @month, @day. Now I want to convert those into a datetime value. Should be easy, right?

Well, I just went through the SQL documentation and was very surprised that I couldn't find any way to do this, other than converting to a string and then to a datetime.

declare @dt datetime
set @dt= convert(varchar,@year)+'/'+convert(varchar,@month)+'/'+convert(varchar,@day)

This is horrible! Surely there has to be a way to convert straight from the int values to the datetime?

Why not define the values as varchar as they should be in the first place. These are string data not integer data just like zip is string data or phone numbers are string data. — HLGEM, Jun 23 '10 at 14:26
@HLGEM: I think there's a bit of a difference there. You will pretty much never use an arithmetic operation on a ZIP or phone number, but that's not the case with years/months/days. These represent a number - number of days since the start of the month, number of months since the start of the year, number of years since BC/AD switch. A ZIP or phone number is just a code that happens to be numerical. — Tom H, Jun 23 '10 at 15:48
I would disagree, I do all date math using a datetime data type, the others are only used for display. And month and day usually need leading zeros so they are not best stored as int. At any rate, if you want to use those values to convert to a date datatype, you should define them as such to begin with. — HLGEM, Jun 23 '10 at 17:07

score 1 · Accepted Answer · edited May 23 '17 at 12:03

1

Not out of the box, but you could create a UDF that does it, for example:

create function ints2date (@year int, @month int, @day int) 
returns datetime 
as begin
    declare @foo varchar (10)

    set @foo = convert (varchar, @year) + '-' +
               convert (varchar, @month) + '-' +
               convert (varchar, @day)
    return convert (datetime, @foo)
end
go               

select dbo.ints2date (2000,1,1)

You can also do it in a more convoluted (but probably slightly faster) way using dateadd/datepart. An example of this can be found at Create a date with T-SQL (stackoverflow.com).

edited May 23 '17 at 12:03

Community

1
1

answered Jun 23 '10 at 12:42

ConcernedOfTunbridgeWells

64,444
15
143
197

Seriously?? MS *really* didn't provide a native method for creating a date from the date parts? And you have to turn the integers into strings before you can create a date? I'm absolutely **horrified**! Yes, given the facts, using a UDF would be the way to go - but I am just totally speechless at the stupidity of it all. AFAI am concerned, that is a big BUG in SQL Server. – Shaul Behr Jun 23 '10 at 12:50
No, it doesn't do this - a bit of a minor annoyance with T-SQL. Note that converting dates is easiest if you construct them in the form YYYY-MM-DD. To be fair, I don't think TO_DATE() in Oracle is appreciably more convenient. – ConcernedOfTunbridgeWells Jun 23 '10 at 12:51
OK, it wasn't the answer I was hoping for, but being accurate, and since you got in the first answer, I'll give you the credit. – Shaul Behr Jun 24 '10 at 06:48
Shaul, SQL Server makes this easy from version 2012 SELECT DATETIMEFROMPARTS( @YEAR,@MONTH,@DAY,0,0,0,0) – Madhivanan Jul 06 '12 at 06:36

score 1 · Answer 2 · answered Jun 23 '10 at 13:48

1

You can do it without converting to string like this. It's not a single function, which would be nice, but it works:

DECLARE
    @year   SMALLINT,
    @month  TINYINT,
    @day    TINYINT,
    @d      DATETIME

SET @year = 2010
SET @month = 6
SET @day = 23
SET @d = '1900-01-01'

SELECT
    DATEADD(dy, @day - 1, DATEADD(mm, @month - 1, DATEADD(yy, @year - 1900, @d)))

answered Jun 23 '10 at 13:48

Tom H

46,766
14
87
128

Yes, but it's still relying a string conversion to create the original value of `@d`... – Shaul Behr Jun 23 '10 at 14:11
@d is a datetime. If it bothers you that it has what looks like a string literal on the other side of the assignment operator then you can remove it completely from the script and just put 0 in its place in the final function. – Tom H Jun 23 '10 at 14:15

score 0 · Answer 3 · answered Jul 06 '12 at 06:30

Another shortcut method

DECLARE 
    @year   SMALLINT, 
    @month  TINYINT, 
    @day    TINYINT, 
    @d      DATETIME 

SET @year = 2010 
SET @month = 6 
SET @day = 23 

SELECT cast(cast(@year*10000+@month*100+@day  as char(8)) as datetime)

SELECT cast(ltrim(@year*10000+@month*100+@day)  as datetime)

Setting date/time in SQL using int values?

3 Answers3