Convert Year to Date

I need to take an integer field that is supposed to be a year, and convert
it to be a date field, with the date being 1/1/Year for whatever the year
is. So 1990 would update to be 1/1/1990 -- How can I do that?
Thanks for your help.SELECT CONVERT(SMALLDATETIME,
RTRIM(Column_Year_With_Incorrect_DataTyp
e)+'0101')
FROM table
WHERE Column_Year_With_Incorrect_DataType BETWEEN 1900 AND 2029;
"KatMagic" <SSKatMagic@.yahoo.com> wrote in message
news:e1bAHcZXGHA.3848@.TK2MSFTNGP05.phx.gbl...
>I need to take an integer field that is supposed to be a year, and convert
>it to be a date field, with the date being 1/1/Year for whatever the year
>is. So 1990 would update to be 1/1/1990 -- How can I do that?
> Thanks for your help.
>|||KatMagic,
Check function "convert" in BOL, Style 112.
declare @.i int
set @.i = 2006
select cast(ltrim(@.i) + '0101' as datetime)
go
AMB
"KatMagic" wrote:

> I need to take an integer field that is supposed to be a year, and convert
> it to be a date field, with the date being 1/1/Year for whatever the year
> is. So 1990 would update to be 1/1/1990 -- How can I do that?
> Thanks for your help.
>
>|||Or you could do something like the following:
DROP TABLE #years
CREATE TABLE #years ( year_int INT )
SET NOCOUNT ON
INSERT INTO #years VALUES ( 1990 )
INSERT INTO #years VALUES ( 1991 )
INSERT INTO #years VALUES ( 1992 )
INSERT INTO #years VALUES ( 2000 )
SET NOCOUNT OFF
SELECT year_int, DATEADD( year, year_int-1900, 0 )
FROM #years
DATEADD will always return a DATETIME. Let me know how you get on.
Damien
"KatMagic" wrote:

> I need to take an integer field that is supposed to be a year, and convert
> it to be a date field, with the date being 1/1/Year for whatever the year
> is. So 1990 would update to be 1/1/1990 -- How can I do that?
> Thanks for your help.
>
>