During my assignment I have to generate Integer value from datetime filed for Date Dimension . I found excellent id this way.
CAST(CONVERT(varchar(8),StartDATE,112) AS int) DateDateKey,
The whole Query is as follow.
CAST(CONVERT(varchar(8),BEGDATE,112) AS int) DateDateKey,
FROM dbo.from Student
🙂 its works for me.