How can i convert a date to an integer format?

Question

I have a column with a date and time. I need to convert that column into an integer format as shown in below. I tried with

SELECT 
    CAST(SUBSTRING(CAST(CAST(date  AS DATETIME) AS BINARY(8)),1,4) AS INT) as Rowdate 
FROM Spend

but I am not getting the below format. Can you please help me guys.

Date:

2012-09-11 11:40:51.000
2012-09-11 11:49:59.000
2012-09-11 11:37:08.000
2012-09-11 11:58:44.000
2012-09-11 12:02:35.000
2012-09-11 11:47:08.000
2012-09-11 12:05:34.000
2012-09-11 11:53:42.000
2012-09-11 12:02:51.000
2012-09-11 12:06:09.000
2012-09-11 11:58:56.000
2012-09-11 11:58:14.000
2012-09-11 12:03:28.000
2012-09-11 11:53:24.000
2012-09-11 11:59:15.000
2012-09-11 11:57:08.000

Rowdate:

2012091116
2012091116
2012091116
2012091116
2012091116
2012091116
2012091116
2012091116
2012091116
2012091116
2012091116
2012091116
2012091116
2012091116
2012091116
2012091116

Answer 1

DECLARE @TABLE TABLE(Col DATETIME)
INSERT INTO @TABLE VALUES
(GETDATE()),(GETDATE()-1),(GETDATE()-3)


SELECT CONVERT(VARCHAR(8),Col,112) + '16' --<-- Dont know why but here is your 16
FROM @TABLE

RESULT

2014030416
2014030316
2014030116

I have added this 16 because it is shown in your desire output but if you only want the part with 'YYYYMMDD' then the following will do the job.

SELECT CONVERT(VARCHAR(8), ColumnName ,112)
FROM Table_Name

RESULT

20140304
20140303
20140301