I had created a table with 5 columns 3 of them are having Time as there Datatype In Our Dev server we had all Db with Sql server 2008 version but when I created the same Table in Production it started giving error as Sql server 2005 does not support the Time Data type DoT net developers of My Team have used Nhibernate (Entity Framewrok) in ther page which keeps the same mapping to table as there in DB that is same replica of the Table that I created will be made in Pages along with primary foreign key etc Now if i change the Data Type to Varchar it will create errors on pages we are planning to move the changes bu tomorrow so I have to come up with solution of creating a new Custom Type as Time and bind a rule to it to store only the data in HH:mm:ss but still I am getting values as 1900-01-01 09:10:12.000 Can any one help with the below script ot tell me some other way without changing the datatype Please note thet I can change it to Time or is there any other way i can achieve it without changing the datatype to Varchar create type Time2 from dateTime GO create rule TimeOnlyRule as @DateTime = Convert(VARCHAR(20),@DateTime,108) GO EXEC sp_bindrule 'TimeOnlyRule', 'Time2' GO create table Trans ( TranTime Time2 not null ) GO insert into Trans (TranTime) values ('09:10:12')
If I understand the custom types correctly, you won't be able to do this in this manner. The custom type is based off of a known and native data type, the datetime. This means that when the value is stored it'll ultimately have the same construct as a 'datetime'. Thus why your seeing the '1900-01-01'. From BOL: "*The implementation of an alias data type is based on a SQL Server native system type*" Source:
https://msdn.microsoft.com/en-us/library/ms175007(v=sql.90).aspx Can you not just use a datetime and have the developers adjust accordingly?