Convert float (YYYY) to datetime

Question

I'm relatively new to SQL, so please forgive me if this is a dumb question. I've been trying for too long now to get this to work.

I have a column in table A that is a float column called ConstructionYear. It is populated with a simple 4 digit year (i.e. 2010, 2005, 1972, etc.). I need to populate table B's YearBuilt datetime field using these years. I've searched and searched and tried all sorts of different combinations of convert() and cast() that I've found online, but it's not working.

What I would like to happen is this:

'2008' -> '2008-01-01 00:00:00.000'

'2005' -> '2005-01-01 00:00:00.000'

'1986' -> '1986-01-01 00:00:00.000'

Instead of what is currently happening (using CAST(ConstructionYear AS DATETIME)):

'2008' -> '1905-07-02 00:00:00.000'

'2010' -> '1905-07-04 00:00:00.000'

'1984' -> '1905-06-08 00:00:00.000'

EDIT: Solution: cast(convert(varchar,@ConstructionYear) AS DATETIME)

So my problem had 2 main causes (other than me being new to sql).

1. I didn't know about the 1900 epoch that SQL Server uses for datetime. I could tell something was going on because of all teh 1905 datetimes i saw, but i didn't know that it was taking my 2005 year and counting it as days from 1900.

2. The year 1753. Why is 1753 the earliest year we can use? I probably had the right syntax at some point before i posted my question here, but it didn't run because my data had some years predating 1753. I assumed the error was with my code.

Answer 1

Check this example:

DECLARE @ConstructionYearas FLOAT
SET @ConstructionYear = 2012
SELECT FloatToDatetime = CAST(convert(varchar(4),@ConstructionYear) as datetime)

It will output:

2012-01-01 00:00:00.000

---

Basically use:

CAST(convert(varchar(4),@ConstructionYear) as datetime)

where @ConstructionYear is your Float variable

Hope it helps!

Answer 2

Besides @Ghost's answer, if you are using SQL SERVER 2012

You can use

DATEFROMPARTS(ConstructionYear, 1, 1)

reference: http://msdn.microsoft.com/en-us/library/hh213228.aspx