question

Bhupendra99 avatar image
Bhupendra99 asked

Time datatype Alternative in Sql Server 2005

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')
datatypes
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Also take this opportunity to make sure your dev environment matches the live
5 Likes 5 ·
This web site runs off of votes. Can you please indicate all helpful answers below by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that one answer.
0 Likes 0 ·
JohnM avatar image
JohnM answered
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?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered
I believe you could do this with a CLR user-defined type. If I have time later today, I might give it a try.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.