convert int values to datetime in sql server

问题:

I need to convert the int values of a year, month and day in a datetime using sql server.

actually i' am using this

DECLARE @MONTH INT
DECLARE @YEAR INT
DECLARE @DAY  INT
DECLARE @MAXDATE DATETIME
DECLARE @MINDATE DATETIME

SET @MONTH=12
SET @YEAR=2010
SET @DAY=1


SET @MINDATE=CONVERT(DATE,CAST (@YEAR AS VARCHAR)+ RIGHT ('0'+ CAST (@MONTH AS VARCHAR),2) + RIGHT ('0'+ CAST (@DAY AS VARCHAR),2))

SELECT @MINDATE

and works ok, but i'm wondering if exist a better way to convert these values.

回答1:

This would be a little simpler:

select dateadd(month,(@YEAR-1900)* 12 + @MONTH - 1,0) + (@DAY-1)

回答2:

I think you definitely have the right way to do this. You're already padding the zeros for single digit months and dates; your solution will work well and is not untidy.

The only other way would be to being with some arbitrary date, and 'add' years, months and days to that starting point. However this is not a great plan, because you would need to start at 31 December, 0 AD by my reckoning. That makes less sense than simply parsing the components, as you are currently doing.

回答3:

Depending on what you mean by 'better way', you may find different answers acceptable. For example, converting from a string could be simpler (and thus better) than how it is done in your script. Like this:

SET @MINDATE = CAST(@YEAR AS varchar) + '-' +
               CAST(@MONTH AS varchar) + '-' +
               CAST(@DAY AS varchar)

That is, if your variable is datetime, the conversion will be implicit, so no need to use CONVERT. The format chosen here is YYYY-MM-DD, or rather YYYY-M-D, which is, as far as I know, acceptable for implicit conversion regardless of locale settings.

EDIT: The format you've chosen, YYYYMMDD, is locale independent too, if I'm not mistaken. My point was only that it required a more complex expression to build the proper string from integers, which seemed to inconvenience you.

回答4:

Untill i know there´s no way con convert it with a built in function. If it´s possible to declare the year, month anda day as varchar, it can be easier:

SET DATEFORMAT YMD;
SET @MAXDATE = @YEAR+'/'+@MONTH+'/'+@DAY
SELECT @MAXDATE